By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Access 2010 VBA Code to Export to Excel automatically

P: 2
I am trying to understand how to write VB code in Access 2010 to automatically populate the data into multiple excel spreadsheets. The data in the query encompasses all the data (by manager), but I need to separate the files by manager. The files need to be automatically exported to excel with the name of each manager which should give me 32 exported files. I'm new to VBA and have read posts for the last 2 days and can't figure it out.
Thanks in Advance
Feb 4 '14 #1
Share this Question
Share on Google+
4 Replies

Expert 5K+
P: 8,638
  1. Post the SQL behind the Query.
  2. How is the Data to populate each individual Spreadsheet, by Year, Months, etc.?
  3. Do these Spreadsheets exist, or do they need to be dynamically created?
  4. These and other questions we will need to know the answers to in order to assist you.
Feb 4 '14 #2

P: 2
I'm not sure what you mean post the SQL behind the query?
The data is populate by manager, no time periods. Only one query exists and they need to dynamically created (split by manager).
Feb 5 '14 #3

Expert Mod 5K+
P: 5,397
Deep breath.
Open the file
Open the stored query in design view
Right click on an empty area in the table display pane
In the pop-up menu select SQL View
You will now see the actual text behind the scenes
Select all of this text
Come back here
Click on the [CODE/] button in the tool bar
a pair of tags:
[code] [/code]
Will be inserted
Paste your SQL text between these tags.

-- You should also read thru:--

Check your Bytes Inbox... I've PM'd you a list of helpfull sites.
Feb 5 '14 #4

P: 294
I think this is what you want?

Expand|Select|Wrap|Line Numbers
  1.  outputFileName = CurrentProject.Path & "\Reports\YourReportName.xlsx"
  2.             DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourName", outputFileName, True
This should help get you started.
Feb 6 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.