Exporting to Excel

[Shane]

When exporting a table directly to Excel from Access, it automatically defaults to file type Excel 95/97. How do I change this default to Excel 2000?

Thanks.

 

[NateO]

Howdy,

I'm trying to overwrite the previous Excel file with a new Excel file created by the Access query.

Try the following:

DoCmd.TransferSpreadsheet acExport, 8, "Your Query Name", "c:\temp\QryExport.xls"

Change the query name and filename/path strings.

This should export and overwrite without any notification. 8 gets you to:

acSpreadsheetTypeExcel9

which is the same as

acSpreadsheetTypeExcel8

Excel 2000 is version 9, whereas '97 is 8. Access 2000 doesn't care and uses the same output version for both.

Hope this helps and have a nice weekend.

 

[Marie Aiken]

This worked beautifully. When I was using "output to" in the Access it would not overwrite the Excel files. Now it overwrites existing Excel files and doesn't prompt me to replace them. Wonderful! This has been a great time saver for me. Thanks for your help!

 

[mdmilner]

Mini-Thread Hijack with similar question

I have recently encountered a problem when exporting into a prepared xls using the TransferSpreadsheet command object within VBA. I've changed the layout of the destination xls such that I have five date format fields together. Usually, they are filled in left to right as events occur and the users are recording them, but frequently, only fields 1/2/5 are filled in as 3/4 are inapplicable to the specific situation.

Sometimes, when exporting, instead of the date value dropping into the 5th field, it "slides" to the left into the first unpopulated field (#3 usually)

The only suggestion I've found is to load the query I'm exporting into a recordset and parse it to the xls. The simple expedient of putting of pulling fields 3/4 out of order and placing them elsewhere on the sheet fixes the problem. Also, this does not happen in all possible cases.

The source data for the export is actually drawn from the same spreadsheet. I currently use the function, for example, to recreate the entire basic spreadsheet when rolling out changes to the base xls (which has some vba within it) or when something went wrong with xls formatting/layout and I want to simply start over.

I am highly suspicious that the issue is an xls field format problem. When I pull the data from the source xls during my import operation (actually an acLink), it's grabbing some property (null/empty/other) and setting the Access2K field properties in some fashion that when exporting (acExport) creates this problem.

While it's not a problem for me to write a function that can change only the properties needed to correct this, I have no idea what precisely is doing this. Can't fix it until I identify it.

Any ideas?

Mike

 

[mdmilner]

oops

Mini clarification. My import is an acImport. Marginally relevant but what I'm actually using.

Just looking for hints.

 

[NateO]

Re: oops

Mini clarification. My import is an acImport. Marginally relevant but what I'm actually using.

Just looking for hints.

This doesn't sound right... In any case, you might want to try something like:

http://www.mrexcel.com/board2/viewtopic.php?t=53716&start=4

This is from Excel, but if you want to work with Excel, try pulling from your spreadsheet instead of pushing from Access.