Exporting to Excel question

[Pwyd]

I was wondering what the syntax in VB would be for using a macro to export to
a specific column, and a specific worksheet (by name, or its position in the
tabs) rather than simply over-writing a worksheet that already exists. Is
this easily done?
If not, where else could i look?

 

[Ken Snell \(MVP\)]

If you have a Range already established in the EXCEL workbook to where you
want to export the data, you may be able to use the Range argument of
TransferSpreadsheet for the export. However, the Range argument is
undocumented for exports, and can lead to interesting/undesired results. See
this blog for more details:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

Otherwise, the "foolproof" way to do the export from ACCESS is to use VBA
code to open a recordset that contains the data to be exported, and use
Automation to open the EXCEL file and then write each field for each record
into the desired cells on EXCEL worksheet.

 

[Pwyd]

Great, thanks Ken.

If you have a Range already established in the EXCEL workbook to where you
want to export the data, you may be able to use the Range argument of
TransferSpreadsheet for the export. However, the Range argument is
undocumented for exports, and can lead to interesting/undesired results. See
this blog for more details:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

Otherwise, the "foolproof" way to do the export from ACCESS is to use VBA
code to open a recordset that contains the data to be exported, and use
Automation to open the EXCEL file and then write each field for each record
into the desired cells on EXCEL worksheet.

 

[Pwyd]

Ken, what if i wish to export a new record into the same spreadsheet each
time?
in a new range of cells...

IE, right under each other, day by day.

 

[Pwyd]

Essentially, i have data on a form in a format i like. The form uses 8
different queries to get that data; i'd like to export the data in the format
shown on the form, into an excel spreadsheet, once per day. Right now its
being manually copied from the form to the spreadsheet. Can i automate it,
easily?

 

[Ken Snell \(MVP\)]

Exporting just one new row and keeping the original data likely cannot be
done using the Range argument in the TransferSpreadsheet action. That's
because the Range argument expects a Range to already exist in the workbook
at the desired starting location.

Can't you just export the entire dataset again, overwriting the rows already
there with the same data, plus the new row?

Otherwise, your scenario likely can be done only by using Automation to
write directly into the worksheet at the desired location.