Excel – Can Excel VBA call Macro in Different Macro Workbook

microsoft excelmicrosoft-excel-2013vba

Can VBA code in an excel spreadsheet call a different XLSM Workbook file's Macro?

I have a local XLSM that needs to call a method in a different XLSM file, how can that be done?

Best Answer

Open file workbook, close if already open

codeWorkBookPath = "Directory\"
codeWorkBookName = "file.xlsm"
codeWorkBookFullPathName = UCase(Trim(codeWorkBookPath)) &         
UCase(Trim(codeWorkBookName))
codeWorkbookAlreadyOpen = CommonWorkbook.isWbOpen(codeWorkBookName)
If Not codeWorkbookAlreadyOpen Then
 Workbooks.Open Filename:=codeWorkBookFullPathName, UpdateLinks:=False, 
 ReadOnly:=True
End If

activate the starting spreadsheet

Workbooks(startWorkBook).Activate
Sheets(startWorkSheet).Activate

Run the macroName

argString = "'" & Trim(codeWorkBookName) & "'!" & Trim(macroName)
APPLICATION.Run argString

Related Solutions

Excel – Recovering VBA modules from corrupt Excel workbook

sigil's solution has worked for me. Instead of steps 3-5, however, I found it safer to export the module(s) and then import them into the desired workbook. This also has the added advantage of having the macro(s) always available.

Follow these steps:

Click the Start menu (Windows 7 and 8), and then type "excel /safe" in the Search box (right above Start: box has gray 'Search programs and files' text)
Open the corrupted workbook
Open the VBA editor (Alt+F11)
Right-click on the module you want to recover and click 'Export File...'. Choose a location, type a name, and save your module. Repeat for all modules you will need.
Close the corrupted workbook
Create a new workbook or open the workbook in which you want to use the macro(s)
Open the VBA editor (Alt+F11)
Right-click on the VBA Project that has the filename of your workbook, and select Import File...'. Navigate to the exported VBA module and click Open. The module will now be a part of your workbook.

Excel – Run excel macros (vba) from vbscript file

You are on the right track. What you need first is to set up a new variable for the Excel.Application using late binding. It's under that object where all the normal excel VBA goodies hide. Below is an example vbs script that will open a new workbook, add a new sheet, name it "TEST", and add some content into cell A1 of that new sheet. When it's done, vbs will echo "Finished"

Sub createWB()

  'Set the excel application variable
  set xlApp = CreateObject("Excel.Application")

  'Make it visible, or hide it in the background
  xlApp.Application.Visible = true

  'Open a new workbook, for instance
  set xlWB = xlApp.Workbooks.Add()
  xlWB.Activate

  'Add a sheet, name it test, bring it front and center
  set xlSheet = xlWB.Worksheets.Add()
  xlSheet.Name = "TEST"
  xlSheet.Activate

  'Put something in cell A1
  xlSheet.Cells(1,1).value = "TEST CELL CONTENT"

End Sub

'Call the subroutine above
call createWB

WScript.Echo "Finished."
WScript.Quit

You may have to monkey around a bit with your existing VBA code to make vbs happy, but you'll find that writing your macros in vbs, is just as functional as writing them in vba.

Best Answer

Related Solutions

Excel – Recovering VBA modules from corrupt Excel workbook

Excel – Run excel macros (vba) from vbscript file

Related Question