Access TO Excel

It is giving me an type mismatch error

Call Docmd .Transferspreadsheet(acExport," qrytest","H:\userSetting\Desktop\ccc\test.xls"

Select allOpen in new window

Thanks a lot

I'm very surprised that made a difference!  TransferSpreadheet will by default write to separate sheets in the same workbook if you use the same workbook name in successive calls to transferspreadsheet,

Are you sure that the type mismatch error was not in the query itself -- perhaps in the parameters?

<<
different queries with same parameter input
>>

The best way to handle this sort of parameter input is to run the transferspreadsheet command from a command button on a form, and have your queries read the parameter from a textbox on the form.  That way you only have to enter the parameter once (in the textbox on the form)

Hi Miriam,
I just tried doing 3 successive calls without specifying a sheet name and it just created a new sheet with the same name as the table I was exporting.  I assume it exported to this same sheet 3 times.  

I believe his mismatch error is because he left out the spreadsheet type among the parameters (i.e acSpreadsheetTypeExcel12).

Ron

<<  I assume it exported to this same sheet 3 times.  >>
Yeah- if you use tables or queries with different names, it will create separate worksheets in the same file, each with a tab label /name automatically determined by the name of the table or query.

<< I believe his mismatch error is because he left out the spreadsheet type...>>

Ah - Yup... you're right.  He didn't follow the same approach Rgonzo1971 suggested with the named parameters (which should also have worked).