Export specific Data from Access to specific cells in Excel VBA
Access 2007 doesn't export to Excel by Susan Harkins in Microsoft Office, in Software on October 16, 2007, 2:12 AM PST. 2007 Excel spreadsheet won't save - Save options greyed out. Strange thing I have 30+ other users at her location and no one else that access the files has a problem with Excel. This seems isolated to her PC. Repair the 2007 excel from disk if you have one.
Export specific Data from Access to specific cells in Excel VBA
The issue: would like to populate specific data in Excel directly from Access.
Currently, the process is data is transfered from Access to create a new worksheet 'B' in the existing spreadsheet containing worksheet 'A', run VB code that loops through all the data to match Name in worksheet 'A' with Name in worksheet 'B', then populate the new data in Field3 in the existing worksheet 'A' from worksheet 'B' Field26, then deletes new worksheet 'B' and exits the excel spreadheet. This works fine but is there a way to do this directly from Access without copying the worksheet to Excel 1st?
I'm a novice coder so any hints would be greatly appreciated.
Thanks
---
Currently, the process is data is transfered from Access to create a new worksheet 'B' in the existing spreadsheet containing worksheet 'A', run VB code that loops through all the data to match Name in worksheet 'A' with Name in worksheet 'B', then populate the new data in Field3 in the existing worksheet 'A' from worksheet 'B' Field26, then deletes new worksheet 'B' and exits the excel spreadheet. This works fine but is there a way to do this directly from Access without copying the worksheet to Excel 1st?
I'm a novice coder so any hints would be greatly appreciated.
Thanks
---
What You Can Export
- A completetable with its data
- Just the structure of a table
- Results of a query, form, or report as a datasheet.
- Database objects, including forms and reports, to another Access database.
Methods of Exporting
There are a number of ways to get data out of Access into a form that another program can use.
- Make-Table query:
Choose to create the table in another Access database instead of the current one. The database must already exist. - Menu:
Select object to export. File | Export... Select the file type and file name of the destination file- another Access database, another kind of database, a spreadsheet, or a text format. A database must already exist before you can export to it.
Exporting a form or report to another Access database just puts a copy of the design in the database. Exporting a form or report to a text or spreadsheet format will copy the data shown by the form or report.
Using the command Send To | Mail Recipient (as Attachment) will also export your object as an attachment. You must have an email account like Outlook that can handle MAPI mail to use this feature. - Publish It with Word:
Select the object in the Database Window or select part of a datasheet. From the menu select Tools | Office Links | Publish It with Microsoft Word . A Word document is created and opened.
Tables and datasheets are often far too wide to fit onto the paper, so this command is not likely to be useful for a them. Forms and reports often export quite neatly to Word.
- Analyze It with Excel:
Select the object in the Database Window or select part of a datasheet. From the menu select Tools | Office Links | Analyze It with Microsoft Excel . An Excel document is created and opened. - Copy and Paste:
Select data in a datasheet. Copy. Switch to your destination file and paste.
Step-by-Step: Export Table from Access |
![Out Out](/uploads/1/2/6/5/126526199/292171978.jpg)
What you will learn: | to export as text file to export as spreadsheet file to export to another Access database |
Start with: , resource files, worldtravel.mdb
World Travel Inc. needs to share its Staff table with some of its offices that have not yet started using Access. The table needs to exported as a comma-delimited text file (csv) and also as an Excel spreadsheet (xls) since different offices have been using different software. (It is a BAD PLAN for different parts of a company to use different software, but is not unusual in the real world!)
Once you have other kinds of objects in Access, you can export them similarly.
Export Table as Text File
- If necessary, open the database worldtravel.mdb which you created earlier and have been modifying.
- Select the table Staff in the Database Window.
- From the menu select File | Export .
The Export dialog appears.
Problem:The command Export is grayed out (not available)
You did not select an object to export in the Database Window. - Navigate, if necessary, to the folder databases project4 on your Class disk.
The File name box should say 'Staff' and the Title bar should read 'Export Table 'Staff' As'.
Problem: File name is not Staff
You did not select the Staff table in the Database Window.
Solution: Close this dialog and start over. - Change the 'Save as type' box to Text Files.
- Click on the Export button.
The Export Text Wizard appears. You will have to make several choices, like you did in the Import Text Wizard. - In step 1, chooseDelimited, which is the default choice..
- Click on Next.
- In step 2, selectComma as the delimiter and check the box for Field Names on First Row.
- Click on Next.
- In step 3, verify the path to where the wizard is going to save the file. It should be on your Class disk in the folder for databases project4. (Your path may be different from the illustration.)
- Click on Finish.
A message appears when the export is done. - Click on OK. The box closes and you are back in the Database Window.
Export as Spreadsheet
- In the Database Window, again select the table Staff.
The Export dialog appears. - If necessary, navigate to your Class disk to the folder databases project4.
- Change the 'Save as type' box to Microsoft Excel 97-2003 (*.xls).
Know which software version will be used:
Versions of Excel before 97 will not be able to open a file in the format you just chose. Those are really old programs now, but there are many old programs still in use. When in doubt, ASK the users! - Click on the Export button.
This time no wizard is needed. Access quietly creates the file and puts you back in the Database Window.
Verify Files
![Excel Excel](/uploads/1/2/6/5/126526199/656217288.jpg)
It is ALWAYS a good idea to make sure that the file you exported will actually open the way you expect it to do! Of course you won't be able to verify if you do not have the correct software to open the exported data.
- In an Explorer window, navigate to your Class disk and open the folder databases project4 where you just saved the text and spreadsheet files.
Temporary file: This folder will have 3 or 4 files. The file worldtravel.ldb is a temporary file that appears only while the database worldtravel.mdb is open. - If you have Excel installed on this computer, double-click on the file Staff.xls.
The file opens in Excel. Scroll to make sure that all the columns were exported correctly.
Success! - Close Excel.
- In the Explorer window, right click on the file Staff.txt and choose Open With | Notepad from the popup menu.Notepad opens the text file. It's hard to know if this is all correct, but at least we know that data was exported.
- Close Notepad.