Query results into Excel?

E

elli

Is there a simple way to make the access to move query results into excel
spreadsheet automatically with a commandbutton?
The data should always go to the next free row in excel...
The path to the Excel is C:\Documents and Settings\All
Users\Tiedostot\Recieved.

Any hints where to start....
Macros arent't an option:(

Have a nice Sunday eve U all!
 
J

John Nurick

Hi Elli,

There's no simple way. You need to use Access VBA code to do something
like this:
-launch Excel and open the workbook
-check that the worksheet you want exists
-if so, find the first "free row" on the worksheet
-open a recordset into the query
-compare the number of records in the recordset with the number of rows
left on the worksheet
-if there's enough space on the worksheet, use Range.CopyFromRecordset
to place the data on the worksheet
-save and close the workbook
-close Excel.
 
F

fredg

Is there a simple way to make the access to move query results into excel
spreadsheet automatically with a commandbutton?
The data should always go to the next free row in excel...
The path to the Excel is C:\Documents and Settings\All
Users\Tiedostot\Recieved.

Any hints where to start....
Macros arent't an option:(

Have a nice Sunday eve U all!
Click to expand...

You can code the command button click event:

DoCmd.TransferSpreadsheet acExport, etc....

Look up the TransferSpreadsheet method in VBA Help.

NOTE: You cannot enter a Range of cells to export to when you export
data.
Access will write the data to either a new worksheet named the same as
the query name, or to that worksheet (overwriting existing data) if
that worksheet already exists.

It is then easy enough to write a macro in Excel to then move that
data to wherever you want in another worksheet and then delete to old
sheet at the same time.
 
Top