Export from access to excel worksheet (1 Viewer)

eword · Jul 23, 2014

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 · Jul 23, 2014

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 · Jul 23, 2014

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 · Jul 23, 2014

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

Frothingslosh · Jul 23, 2014

eword said:
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 · Jul 23, 2014

Frothingslosh said:
... 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 · Jul 23, 2014

vbaInet said:
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 · Jul 23, 2014

Frothingslosh said:
like building a chess program in access....

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

Frothingslosh · Jul 23, 2014

vbaInet said:
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 · Jul 23, 2014

I think so. Anything is possible

eword · Jul 24, 2014

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 · Jul 24, 2014

eword said:
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 · Jul 24, 2014

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 · Jul 24, 2014

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 · Jul 24, 2014

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 · Aug 22, 2014

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 · Aug 22, 2014

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

SLCOMEAUXJR · Aug 22, 2014

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 · Aug 22, 2014

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

Export from access to excel worksheet (1 Viewer)

New member

AWF VIP

New member

AWF VIP

Premier Pale Stale Ale

AWF VIP

Premier Pale Stale Ale

AWF VIP

Premier Pale Stale Ale

AWF VIP

New member

Premier Pale Stale Ale

New member

Attachments

Premier Pale Stale Ale

AWF VIP

New member

AWF VIP

New member

AWF VIP

Similar threads

Users who are viewing this thread