Fix Excel Opening Original Workbook When Running Imported Macro
Question details
Running an imported VBA macro in a new Excel workbook unexpectedly triggers the original source workbook to open.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing a VBA module (BAS file) into a different workbook and attempting to run the macro using copied buttons, objects, or ribbons.
- Observed behavior
- The macro or assigned object retains a hidden absolute reference to the original file, causing Excel to automatically open the original workbook whenever the macro is executed.
Before proceeding, ensure you have exported your VBA modules as .bas files to a safe location, as the recommended solution involves temporarily removing macros to clear hidden links.
Save as XLSX and Reimport the Macro
The most effective way to permanently break hidden links to the original workbook is to save the destination file as a standard XLSX file, which strips all embedded VBA links.
By saving the file in a non-macro format, Excel is forced to drop all internal VBA connections. When you reimport the macro afterward, it will be treated as entirely local to the new workbook.
Go to File > Save As, and choose 'Excel Workbook (*.xlsx)' from the format dropdown. Confirm any prompts warning you that macros will be removed.
Close the newly saved XLSX file completely, and then reopen it in Excel.
Press ALT + F11 to open the VBA Editor. Go to File > Import File, select your previously saved .bas module, and import it into the clean workbook.
Finally, go to File > Save As and select 'Excel Macro-Enabled Workbook (*.xlsm)' to preserve your newly imported macro without the old legacy links.
Recreate Buttons and Form Controls
Copied buttons or shapes often carry over the macro path from the original file. Recreating them ensures they reference the local macro instead of the external one.
Seamlessly Manage Macros and VBA in WPS Office
WPS Spreadsheet offers robust support for macros and form controls, allowing you to easily import modules and assign macros to objects without dealing with cumbersome legacy links.
- 1. Enable Developer Tools: Open WPS Spreadsheet, go to the Options menu, and enable the Developer tab to access advanced macro tools.
- 2. Import VBA Modules: Click the Developer tab, open the VBA Editor, and import your .bas files directly into your current project.
- 3. Insert Form Controls: Use the Insert menu under the Developer tab to add fresh buttons, checkboxes, and shapes to your worksheet.
- 4. Assign Macros Locally: Right-click the newly inserted object, choose 'Assign Macro', and select the local macro to ensure it runs seamlessly without opening external files.
Frequently Asked Questions
Why does my imported macro still look for the old workbook?
When you copy a button, shape, or custom object that has a macro assigned to it, Excel often copies the absolute path of that macro (e.g., 'OriginalFile.xlsm'!MacroName). Clicking it forces Excel to open the old file in order to run that specific script.
Can I just edit the macro assignment instead of recreating the button entirely?
Yes, you can right-click the copied button, select 'Assign Macro', and change the reference from the external file's macro to the macro located in 'This Workbook'. However, starting from scratch is often recommended to prevent hidden workbook links from lingering in the background.
How do I export a macro to a BAS file?
Open the VBA Editor by pressing ALT + F11, locate your module in the Project Explorer panel on the left, right-click it, select 'Export File', and save it as a .bas file to your local computer.
Will saving my file as XLSX permanently delete my macros?
Yes, saving a file in the standard .xlsx format will immediately strip all VBA code and macros from that specific file. This is exactly why it is used to clear hidden links, but you must remember to reimport your .bas module and save the file as .xlsm afterward to restore macro functionality.
