Transfer Access query data to existing Excel spreadsheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dom Boyce - 21 Aug 2003 12:17 GMT

I have a query in Access which returns stock performance data in 3
columns. I need to run COUNTIF and SUMIF functions on this data, and
reckon that the easiest way to do this is to export the data to an
Excel spreadsheet which contains the necessary functions, then import
the resulting data back into an Access table.

I investigated the OutputToExcel function, but this creates a new
Excel file, which obviously does not have the required COUNTIF and
SUMIF functions in it. I also noticed that data can be sent using a
DDE link to Excel, but this cannot be used in VBA as part of a module.

Could anybody help with doing this? I need to automate the task with
as little user intervention as possible, but am fairly new to data
exchange between Windows apps, and am unsure where to start.

Thanks a lot,

Dominic Boyce

Reply to this Message

Don Smith - 21 Aug 2003 23:33 GMT

>I have a query in Access which returns stock performance data in 3
>columns. I need to run COUNTIF and SUMIF functions on this data, and
[quoted text clipped - 14 lines]
>
>Dominic Boyce

It should be possible to generate the effects of a CountIf and SumIf
though the use of additional queries, append queries, dup and non-dup
queries and a temp table. (While a little harder to sent up, this path
would allow for easier automation within Access.)

Can you provide additional information - the names of the three
columns and conditions of the CountIf and SumIf you was wishing to
have Excel do for you?

Don Smith

Reply to this Message

newcomer19992000 - 22 Aug 2003 00:48 GMT

>>I need to run COUNTIF and SUMIF functions on this data

I think you can do it without Excel.
Make a query in ACCESS which returns 'count(*) from .. where ..' ,
and 'sum(..) from .. where ..' instead of Excel's CountIf and SumIf Function.

If you still need to export the data to Excel.
You can write macro code in ACCESS as below.
For example , with this code, ACCESS opens 'c:\temp.xls',
and write CurrentTime in cell'C2'.
You can transfer data without DDE.
You should use Excel as an ActiveX by using of Object type.

'-------------------------------------
Sub TestApp()

Dim Ex As Object
Dim Bk As Object
Dim iX As Integer
Dim iY As Integer

Dim sFile As String
sFile = "c:\temp.xls" 'Write the path to an existing file here.

Set Ex = CreateObject("Excel.Application") 'This is important.
Set Bk = Ex.Workbooks.Open(sFile)

'From Access to Excel

iX = 2 'Y of Cell'C2'
iY = 3 'X of Cell'C2'

Bk.Sheets(1).Cells(iY, iX) = Now()
Bk.Save 'Save the book.

Bk.Close 'Close the book.
Ex.Quit 'Quit Excel Apllication.

Set sh = Nothing 'Release object
Set Ex = Nothing 'Release object

End Sub

Reply to this Message

Dom Boyce - 19 Sep 2003 12:27 GMT

Thanks for the help on this - managed to do all necessary calculations
within access, keeping things a lot tidier than they might have been
had I been left to my own devices!

Dom

> I have a query in Access which returns stock performance data in 3
> columns. I need to run COUNTIF and SUMIF functions on this data, and
[quoted text clipped - 14 lines]
>
> Dominic Boyce

Reply to this Message

rlc4488 - 30 Sep 2003 14:54 GMT

Use the Excel "Get External Data" to build a spreadsheet with an
embedded query to your database. Select the fields required from your
database into the spreadsheet and build your formulas in the adjacent
columns to the data that is returned. I use this quite frequently
with excellent results.

rlc4488

> Thanks for the help on this - managed to do all necessary calculations
> within access, keeping things a lot tidier than they might have been
[quoted text clipped - 20 lines]
> >
> > Dominic Boyce

Reply to this Message