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

1
votes

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.

1
excelvbams-access
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
...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
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
I tested numerous ways, yes. have you tried exporting to a different file? - ashleedawg
@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

1 Answers

1
votes

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;"