Export Access Query to Existing Excel Workbook (which has Macros)

Question

First question on Stackoverflow, have been using the site as a viewer for a long time.

I need to export a query from Access 2010 into an existing Excel 2010 Workbook which can be either .xlsm or .xlsb (since macros work in both). I set it up the following way:

User clicks a button in the Excel workbook to refresh a tab
--> this runs VBA in Excel that calls a Macro in Access to run ---> the macro in Access merely calls an existing query in Access to run and export the results back to the initial Excel Workbook.

Access Macro Code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Data_SlotView", "M:\Merch. Strategy\Merch Forecasting\Automotive Division\File Sharing\Automotive Deal-Page-Slot View\Deal-Page-Slot-View.xlsb"

My code works if my Excel file was .xlsx but apparently Access 2010 does not allow exporting to Excel files with macros so I have to use some sort of workaround method.

I just tested and it worked fine for me. I don't think you need an alternate method; it's better to figure out what's wrong with the one you're trying. Are you getting an error? — ashleedawg
– ashleedawg, Commented Mar 14, 2018 at 17:01
...also, on what are you basing "apparently Access 2010 does not allow exporting to Excel files with macros"? Did you read that somewhere or is it a guess? — ashleedawg
– ashleedawg, Commented Mar 14, 2018 at 17:03
I don't think that you can "Export to an Excel workbook" if the workbook is currently open, can you? You should get some file permission or "can't open file" error. — RBarryYoung
– RBarryYoung, Commented Mar 14, 2018 at 17:11
I tested numerous ways, yes. have you tried exporting to a different file? — ashleedawg
– ashleedawg, Commented Mar 14, 2018 at 17:18
@ashleedawg The [GROUP] after the file name is not what I believed it to mean. It merely means more than one Excel sheet is selected, as soon as I select only one sheet it disappears. I moved my ORIGINAL file in question to my desktop (Local Drive) it the code still does not run for it. This is really frustrating. — Craig Kostka
– Craig Kostka, Commented Mar 14, 2018 at 17:42

Erik A · Accepted Answer · 2018-03-14 17:17:26Z

1

You can try using an INSERT query from Access:

CurrentDb.Execute "INSERT INTO [Excel 12.0 Macro;HDR=Yes;DATABASE=M:\Merch. Strategy\Merch Forecasting\Automotive Division\File Sharing\Automotive Deal-Page-Slot View\Deal-Page-Slot-View.xlsm].[Sheet1$] SELECT * FROM Data_SlotView;"

answered Mar 14, 2018 at 17:17

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ashleedawg Over a year ago

Interesting test:... my theory is it won't work because the file transfer method isn't the issue.

Erik A Over a year ago

Perhaps. Anyway, the appropriate way, to do it is to import from Excel, instead of exporting to Excel.

ashleedawg Over a year ago

It should work either way, but yes. I'm curious about this one because I've probably used TransferSpreadsheet a million times... I was surprised to see that it even works for me if the Excel file is open. I could see the data removed & replaced, as if it was a DB table. I'm pretty sure that didn't used to work.

Collectives™ on Stack Overflow

Export Access Query to Existing Excel Workbook (which has Macros)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related