TransferSpreadsheet, etc

[m stroup]

Is it possible to tramsfer an Access query directly into an Excel spreadsheet
that is already formatted the way you want it? (ie. with conditional
formatting, formulas, Bolded headers, etc.)?

 

[Douglas J. Steele]

No, it's not.

 

[Dale Fye]

Not using the TransferSpreadsheet method. But there are other techniques.

What version of Office are you using?

1. You could open your Excel spreadsheet and link to the Access query or
table. You will not be able to use a query that has computed fields that
use user-defined functions.

2. You could use automation to open the Excel file and paste the data from
Access into Excel one cell at a time. This will be relatively slow. The
way you do this goes something like this.
a. Use the GetObject method to open Excel
b. Open the file you want to put the data in
c. Create a recordset in Access based on your query
d. Loop through the rows of the recordset, loop through the fields in
each record and put them in the appropriate cells of the Excel spreadsheet.

3. Something else you might want to consider is using the
TransferSpreadsheet method to get the data into Excel, then use a saved
macro in Excel to reformat the Excel spreadsheet with the formatting you
want.

HTH
Dale