Exporting to excel

[John]

Hi

I need to export output of a query into an excel sheet with a specified name
and overwriting the excel file if it already exists at the destination. Is
there a way to do all this by press of a button so its easy for the end
user?

Many Thanks

Regards

 

[Rob Parker]

You can use the code here as a starting point:
http://www.freevbcode.com/ShowCode.asp?ID=2794

You can assign the name of a query, rather than a table, to strTable. As
written, it will export a table from another database; to use it to export a
query from the current database, comment out the line which assigns a file
to strDB, and change the line:
Set objDB = OpenDatabase(strDB)
to
SetobjDB = CurrentDb()

HTH,

Rob

 

[JCS]

Hi John,
Look at the Transferspreadsheet option for macros. I've used this on many
database to export Access tables to Excel. You can even export your table
into a range in Excel.

Regards,
John

 

[Rob Parker]

In my first post in reply to the original question, I purposely did not
mention the TransferSpreadsheet method, because the OP asked to write the
data to a specified name sheet; the TransferSpreadsheet method does not
allow the range to be specified for export (although it does for import).
When exporting, the data will be written to a sheet with the name of the
original table/query.

Rob

 

[Ken Snell \(MVP\)]

Actually, one can use the Range argument in an export from
TransferSpreadsheet but it has some qualifications for successful use. See
Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ExpRange

 

[Rob Parker]

Hi Ken,

Thanks for the reference. I notice at the top of that page that you say
"NOTE that the use of the Range argument for exports is an UNDOCUMENTED
feature in ACCESS"; that's not the impression I got from the following,
taken from the VBA Help file (Access 2003):

"Range Optional Variant. A string expression that's a valid range of cells
or the name of a range in the spreadsheet. This argument applies only to
importing. Leave this argument blank to import the entire spreadsheet. When
you export to a spreadsheet, you must leave this argument blank. If you
enter a range, the export will fail."

The last two sentences of that para (and attempting to use a range which
contained both a sheetname and a cell reference ("SheetName!$a$1", or
"'SheetName'!$a$1"), both of which produced an error message) was what
prompted me to reply to JCS as I did, and to omit any mention of the
TransferSpreadsheet method in my reply to the OP.

Rob

 

[Ken Snell \(MVP\)]

Hi Ken,

Thanks for the reference. I notice at the top of that page that you say
"NOTE that the use of the Range argument for exports is an UNDOCUMENTED
feature in ACCESS"; that's not the impression I got from the following,
taken from the VBA Help file (Access 2003):

"Range Optional Variant. A string expression that's a valid range of
cells or the name of a range in the spreadsheet. This argument applies
only to importing. Leave this argument blank to import the entire
spreadsheet. When you export to a spreadsheet, you must leave this
argument blank. If you enter a range, the export will fail."

The last two sentences of that para (and attempting to use a range which
contained both a sheetname and a cell reference ("SheetName!$a$1", or
"'SheetName'!$a$1"), both of which produced an error message) was what
prompted me to reply to JCS as I did, and to omit any mention of the
TransferSpreadsheet method in my reply to the OP.

Rob

Yeah, the Help file says Range is not available for exports, but it can be
used with limited success. But its limitations makes it flaky and often
unreliable when people try to use it for something that it doesn't do.

<g>