Transfer data to excel

[John Southern]

I have a select query in access and I need to transfer the results to Excel using VBA code.

There seem to be two commands that are designed to achieve this


DoCmd Transferdatabase and DoCmnd Transfertoexcel. I have been unable to find what arguments are required and generaly how to code this. Can anyone help please.
John

[pbaldy]

Have you looked in VBA help?

[John Southern]

Thanks for the attachment, it has more details than were available to me. Eg the three types of transfer and more spreadsheet types. ( Am I right in assuming that 9 is Excel 2007?)
I am still very unsure how to give the path and file name to the SELECT query I need to export to excel. I also want to make sure that every time the proceedure is run the data replaces that already in the spread sheet. When I have used the export wizard it generates a new workbook!
John

[pbaldy]

Am I right in assuming that 9 is Excel 2007?

No, that is from Access 2000. Again, have you looked in Help? There should be examples with the syntax.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryEquipListTSA", "\\ServerName\Users\Blah\TSAExport.xls"

[John Southern]

I have entered the following code :-

DoCmd.TransferSpreadSheet acexport, acSpreadSheetTypeExcel10, "Qryalldata", "U:\Everybody\Sales statistics\Sales report.xlsm"

I get the following error,
Could not find installable ISAM (Error 3170)

Using the Help key showed two possible reasons
a) The ISAMs have been moved.
b) A network link is not available.

a) is unlikely I didnt know I had ISAMs!
b) I am working on a networked machine. The application and data are on the file server. Excel is on my workstation.

I have succesfuly exported the data using the wizard, I would suppose that the wizard needs the ISAMs as much as I do.
John

[pbaldy]

Just in case, try it with the normal "xls" extension.