Access TO Excel

Hi
I have 4 different queries with same parameter input and I want to transfer the data in the
same excel workbook but in different sheet.
I am trying to use the
docmd transferspreadsheet

Open in new window

but I am not getting much success.

need help

Thanks
surah79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _ 
                               TableName:="myqry1", _ 
                               FileName:="D:\Test.xls") 
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _ 
                               TableName:="myqry2", _ 
                               FileName:="D:\Test.xls") 

Open in new window

Regards
0
surah79Author Commented:
It is giving me an type mismatch error
Call Docmd .Transferspreadsheet(acExport," qrytest","H:\userSetting\Desktop\ccc\test.xls"

Open in new window

0
IrogSintaCommented:
For different sheets:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryTest1", "H:\userSetting\Desktop\ccc\test.xls", , "Sheet1!"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryTest2", "H:\userSetting\Desktop\ccc\test.xls", , "Sheet2!"
etc...

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

surah79Author Commented:
Thanks a lot
0
mbizupCommented:
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)
0
IrogSintaCommented:
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
0
mbizupCommented:
<<  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).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.