Access TO Excel

Posted on 2013-09-26
Medium Priority
Last Modified: 2013-09-26
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

Question by:surah79
  • 2
  • 2
  • 2
  • +1
LVL 54

Assisted Solution

Rgonzo1971 earned 1000 total points
ID: 39523836

pls try

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _ 
                               TableName:="myqry1", _ 
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _ 
                               TableName:="myqry2", _ 

Open in new window


Author Comment

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

Open in new window

LVL 29

Accepted Solution

IrogSinta earned 1000 total points
ID: 39524460
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!"

Open in new window

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Closing Comment

ID: 39524493
Thanks a lot
LVL 61

Expert Comment

ID: 39524515
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)
LVL 29

Expert Comment

ID: 39524540
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).

LVL 61

Expert Comment

ID: 39524619
<<  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).

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question