Spreadsheet Exporting problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the below code to export a parameter query to a excel spreadsheet.
The problem is the parameter box opens and displays the query result. Then
the export file save opens and then the parameter box open again and if I do
not enter the code again it will not export the data. Why is the parameter
opening twice and how can I have the file “Save as Type†default to Excel
97-2002.
Thanks
CODE:
DoCmd.OpenQuery "z- qmtblTeoco Control"
DoCmd.RunCommand acCmdExport
 
I am using the below code to export a parameter query to a excel spreadsheet.
The problem is the parameter box opens and displays the query result. Then
the export file save opens and then the parameter box open again and if I do
not enter the code again it will not export the data. Why is the parameter
opening twice and how can I have the file ´Save as Type¡ default to Excel
97-2002.
Thanks
CODE:
DoCmd.OpenQuery "z- qmtblTeoco Control"
DoCmd.RunCommand acCmdExport
Click to expand...

Use the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "z-
qmtblTeoco Control", "C:\FolderName\FileName.xls", True

See VBA help for additional arguments and information.
 
Thank you..

fredg said:
Use the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "z-
qmtblTeoco Control", "C:\FolderName\FileName.xls", True

See VBA help for additional arguments and information.
Click to expand...
 
Hi Raj,

I was just doing that this morning. Apparently, if you specify the
same file when exporting more worksheets, it defaults to adding more
tabs. I crammed 8 of them into a single workbook.

Regards,
RD
 
RD,
I was thinking that is would add but for some reason it replaces the tab,
with the new data.
 
RD,
I was thinking that is would add but for some reason it replaces the tab,
with the new data.
Click to expand...

If you use the same query each time you send data to Excel, it will
always go into the existing worksheet of that name and the new data
will replace the old data.
You need to change the name of the query each time, and it will create
a new worksheet of that name each time.
 
Fred,
The code that is entered into the query is a 3 or 4 digit number is there
any way to add this to the file that is being exported so that it will create
a new worksheet?
I an new at doing more advanced features of Access so please forgive so many
questions..
Raj
 
If you use the same query each time you send data to Excel, it will
always go into the existing worksheet of that name and the new data
will replace the old data.
You need to change the name of the query each time, and it will create
a new worksheet of that name each time.
Click to expand...

Oops. Forgot to mention that I changed the name slightly each time I
ran it.
 
Back
Top