Export from access to excel worksheet

[eword]

Hi,

How would I create a macro that exports data from an Access 2010 database table to a specific worksheet in an excel file. So far I have tried using ImportExportSpreadsheet with no success.

Ideally what I would like the macro to do is export a table to a worksheet in an excel file and then in a different worksheet complete a vlookup on the automatically exported data.

 

[vbaInet]

There are lots of examples within the following link:

http://www.btabdevelopment.com/ts/default.aspx?PageId=110

The current page should be what you're looking for.

 

[eword]

I want to be able to do this using the macro builder in access, not VB. I know u can do this otherwise my tutor would not have set it as an assignment.

 

[vbaInet]

Well if it's an assignment then you should be doing some research.

 

[Frothingslosh]

I want to be able to do this using the macro builder in access, not VB. I know u can do this otherwise my tutor would not have set it as an assignment.

If this was set as an assignment, then either the information is in your books, or else you're being tested on your ability to determine/find the answer on your own.

One thing to keep in mind is that AWF, like every other programming support forum out there, does not do homework FOR you. If you attempt something (even homework) and get stumped, we're happy to help you; just tell us what you've done so far and what results you're getting, and we'll point you in the right direction.

Is the 'must be by macro' your restriction or your tutor's? Exporting to a specific sheet is something I've only seen done via VBA.

 

[vbaInet]

... Exporting to a specific sheet is something I've only seen done via VBA.

It can be done in a macro too... if that's any reassuring to eword

 

[Frothingslosh]

It can be done in a macro too... if that's any reassuring to eword

lol

I wasn't sure - I'm only at the intermediate/advanced intermediate level here, so just because I've not seen something it doesn't follow it can't be done. Much like building a chess program in access....

 

[vbaInet]

like building a chess program in access....

There's a sample db somewhere in the forum with a chess game.

 

[Frothingslosh]

There's a sample db somewhere in the forum with a chess game.

Yeah, I saw the guys talking about Chris having written one; that's how I know it's possible. I think someone did Monopoly, too?

 

[vbaInet]

I think so. Anything is possible

 

[eword]

Ok can somebody just provide an example of how to use the macro ImportExportSpreadsheet to save a database table from access to a new spreadsheet with a given range.

 

[Frothingslosh]

Ok can somebody just provide an example of how to use the macro ImportExportSpreadsheet to save a database table from access to a new spreadsheet with a given range.

According to the Microsoft Dev Center, ImportExportSpreadsheet cannot export to specific ranges on a target sheet, as selecting a range when exporting will make the process fail. If there's a way to export data to a specific range on a specified spreadsheet via a macro, I don't know what it is and a 10-second Google search didn't have anything pop up. Hopefully vbaInet knows a way!

If he doesn't, you're probably going to need to use VBA to make this work the way you want.

 

[eword]

I worked it out. If you use Access 2010, when you add a new action in design view select ImportExportSpreadsheet. Then fill in the fields like this:

Transfer Type: Export
Spreadsheet Type: Excel Workbook
Table Name: tblYourDatabaseTable
File Name: C:\Users\yourusername\Desktop\Yourfolder\YourExcelFile.xlsx
Has Field Names: Yes
Range: tblYourDatabaseTable!1:1048576

The range refers to the name of your worksheet and the 1:1048576 means the entire worksheet is where you want to export the data to. If you leave this field blank it will just add the data to a new worksheet in the file, not the same one.

P.S. I am not sure why but if the worksheet in the excel file is not the same name as the table you wish to export it to, it will save it into a different worksheet or just wont work.

 

[Frothingslosh]

Huh. When you said range, I was expecting you needed something like "B3:J17", which, AFAIK, can only be done with VBA.

It's interesting that entering a value in the range field did not cause the process to crash the way MS said it would.

 

[vbaInet]

Always test it. That statement may apply to Lotus or much older versions of Excel.

The export should run fine. The name of your sheet must not match the name of your table. And you don't need quotes in the table name part.

 

[SLCOMEAUXJR]

I want the export function in the macro to automatically replace an existing file in that path and name.

Is there a command line switch that can be set?

 

[vbaInet]

Please go through the thread and look at the screenshot in post #13.

 

[SLCOMEAUXJR]

I have the macro working to export the data to excel, I just want to know how to set a switch in the macro to automatically replace the existing file previously exported.
Thanks.

 

[vbaInet]

I see. You'll need to call a function to Kill the file.