Is it possible to export from access into and existing excel document?

I have a query in access that I would like to export into and existing excel document as a new tab, Is that possible?
garyrobbinsAsked:
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.

Andrew VelascoOperational Account Management & Data AnalystCommented:
Are you looking to do this through macro? or just need to import the data into excel?
0
PatHartmanCommented:
Yes.  Use the TransferSpreadsheet Method.  Specify the name of the existing document.  The name of the query will be used as the name for the new tab.
0
garyrobbinsAuthor Commented:
Where is TransferSpreadsheet Method?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Andrew VelascoOperational Account Management & Data AnalystCommented:
Are you trying to write code to do this? or just use the built in features of Excel/Access?

Depending on your version of MS Office:

In Excel, on a new sheet, select Cell A1.
Then select the Data tab.
On the far left is "From Access", select it.
Navigate to your Access file and you will be prompted to select your query.
Hit ok on the next prompt (confirming where you want the data to go)

Now you will be able to right click on the data and select "Refresh" to refresh if anything ever changes.
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
Helen FeddemaCommented:
Do you want to replace the entire contents of the target workbook, add a new sheet, or what?  Here are some simple code samples (the 3rd with a range indicated) to write the contents of an Access query to a workbook:
strPath = Application.CurrentProject.Path
strWorkbook = strPath & "Customers.xls"

DoCmd.TransferSpreadsheet transfertype:=acExport, _
   spreadsheettype:=acSpreadsheetTypeExcel8, _
   tablename:="qryCustomers", _
   FileName:=strWorkbook, _
   hasfieldnames:=True

'For Office 2010, use acSpreadsheetTypeExcel12Xml argument and 
'the .xlsx extension for the filename

DoCmd.TransferSpreadsheet transfertype:=acExport, _
   spreadsheettype:=acSpreadsheetTypeExcel12Xml , _
   tablename:="qryCustomers", _
   FileName:=strWorkbook, _
   hasfieldnames:=True


DoCmd.TransferSpreadsheet transfertype:=acImport, _
   spreadsheettype:=acSpreadsheetTypeExcel9, _
   tablename:=strTable, _
   FileName:=strWorkbook, _ 
   hasfieldnames:=True, _
   Range:=strRange

Open in new window

0
garyrobbinsAuthor Commented:
Nice, I was trying to export from access to excel, rather that import form excel to access.  thanks.
0
PatHartmanCommented:
Looks like Helen has provided code samples for you.  TransferSpreadsheet is also the name of a macro if you are more comfortable building a macro than one line of code.  Helen's code shows named arguments but intellisense will help you build the code all in one statement.

DoCmd.TransferSpreadsheet  -- as soon as you enter the space following the command, intellisense will pop up and help you complete the statement.
0
PatHartmanCommented:
gary,
To help us in the future, please specify that your need is one-time only and you want a GUI solution if one is available.  Typically the expert answers will be biased toward a code/macro solution since we rarely see questions regarding how to do something one time via the GUI.
0
garyrobbinsAuthor Commented:
Is it possible to pivot table off of the data in access without bringing everything over?

Preferable not in a Macro.
0
garyrobbinsAuthor Commented:
Never mind I figured it out using the GUI suggestions from above, thanks again.
0
garyrobbinsAuthor Commented:
Pat in reference to your GUI vs Code Macro comment.  I don't have much experience with Macros outside of duplicating simple repetitive task, can you recommend a good source where i can get some starter information to dive into it and learn the basics?
0
PatHartmanCommented:
I don't use macros at all so I can't recommend any sources.  I write VBA.  The Access Cookbook is a big help when you are learning VBA because it shows you snippets of code that do specific things.  The format makes it very accessible.  So, rather than starting with the syntax of a language, you start by implementing something useful.  There are several versions but I think the newest is for A2002 (http://www.amazon.com/Access-Cookbook-OReilly-Windows-Getz/dp/0596000847/ref=sr_1_1?ie=UTF8&qid=1407436009&sr=8-1&keywords=access+cookbook).  Don't worry.  VBA hasn't changed since then although there are a few more functions.  Then second thing to look for is a book on VBA functions.  There are also websites but I haven't found one to recommend since they offer too high level a description.  The actual documentation is better.  The problem with Access Help is that if you don't know the name of the function you need help with, there is no way to search.  Sometimes you get lucky and come across the Functions by category list.  That is much more useful than an alphabetic list.  You can simply go to the section on Date functions and see what's there.  The list is short enough so you will take the time to read through it.

The point I was trying to make is that we need as much information as you can provide when you ask questions so since you actually wanted a 1-time solution using the GUI, Helen and I wasted our time offering code solutions.
0
garyrobbinsAuthor Commented:
Ok, Thanks. I will be more specific in the future.

Used Copy for only $0.96 can't hurt to have on hand right.
0
PatHartmanCommented:
Hope you enjoy it.  It's a classic.
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 Excel

From novice to tech pro — start learning today.

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.