How do I script to export two different queries to same excel file.

[chetna kalra via AccessMonster.com]

I would like to know how to write a procedure to export two Access queries
like for example qry1 and qry2 to two sheets of same Excel file.

I mean in Excel sheet prepared I shoul have two sheets naming qry1 and qry2.

Thanks in advance.

 

[Nikos Yannacopoulos]

Karla,

Try something like:

vWkbk = "c:\My Documents\MyWorkbook.xls"
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9,"qry1",vWkbk,,"qry1"
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9,"qry2",vWkbk,,"qry2"

Each TransferSpreadsheet exports one query; the third argument (qryX) is
the query (or table) to be exported, the last one is the name of the
sheet it will be exported to; they just happen to be the same here.
Watch out for wrapping in your newsreader, each
DoCmd.TransferSpreadsheet should be in a single line with all its arguments.

HTH,
Nikos

 

[chetna kalra via AccessMonster.com]

Hi Nikos

Thanks for your efforts.

However still I am facing the same problem.
This time the error was "Runtime error:2498 An expession you entered is
the wrong datatype for one of the argument"

what I wrote was
vWkbk = "c:\My Documents\MyWorkbook.xls"
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9,"qry1",vWkbk,,"sheet1"

as a new excel sheet always has sheet1, sheet2 and sheet3 by default. and
error is in the same line.

the second I wrote as

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9,"qry2",vWkbk,,"sheet2"

however I tried with qry1 and qry2 also but the same error is coming and
one more thing I need to export this into excel 97 so I am using that only.

Can you help in this regard?

Thanks in advance.

 

[Nikos Yannacopoulos]

Karla,

To begin with, I suppose your Transfer Spreadsheet expression is all in
one line in the VBA window, regardless of the wrapping that occurs in
the newsreader, right?
Also, since you're still on Excel 97, it may be that the spreadsheet
type constant needs to be changed to acSpreadsheetTypeExcel8 (or even
acSpreadsheetTypeExcel7).
Furthermore, my assumed query name qry1 was more of a placeholder
really; if your actual query name is different, then you should be using
the actual name instead of qry1. Likewise, c:\My Documents\
MyWorkbook.xls is also a placeholder, use the actual target workbook
path and filename; there may not even be a folder C:\My Documents on
your PC, for all I know!

HTH,
Nikos

 

[chetna kalra via AccessMonster.com]

Nikos

That I wrote was just a example copy of what I am scripting not the actual
code.

I did at my end with my assumptions like the exact path and stuff. and even
for excel version I am putting acSpreadsheetTypeExcel97, so no issues
regarding that.

But I really like to thank you coz I did a very stupid mistake because of
which the error was coming.

Thank you very much for your help.

It really worked.