• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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?
0
garyrobbins
Asked:
garyrobbins
  • 6
  • 5
  • 2
  • +1
1 Solution
 
Andrew VelascoCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Andrew VelascoCommented:
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now