How to use Application.Run in Excel VBA to run a macro from another workbook automatically. Watch the video:
How can we use Application.Run in Excel?
If we wish to run a macro from an event or from another macro in the same workbook or Excel file we call the macro like this in your code :
We do not use Call but it makes it clearer when we read the code. It is evident that another macro is being called.
But what if wish to run a macro that is in another workbook. We can use the same strategy to run an Add-in.
We can use Application.Run code as shown below if both the files, that is, the macro requesting and macro containing files are open:
data-for-printing.xlsm is the file that contains the macro and it is important to enclose the file name in single quotes. We also place an exclamation mark after the file name. Doesn’t this remind you of accessing a cell from another worksheet?
PrintHeaderOnFirstPage is the name of the macro in the file data-for-printing.xlsm
Given below is the complete macro code to run a macro from another Excel workbook if the file containing the macro VBA code is closed:
Function IsWorkBookOpen(FileName As String)
Dim FF As Integer, ErrNum As Integer
On Error Resume Next ‘ We turn off error checking
FF = FreeFile() ‘ The inbuilt function gets a free file number.
Open FileName For Input Lock Read As #FF ‘we try to open the file and lock it
Close FF ‘ Close the file
ErrNum = Error ‘ capture the error number
On Error GoTo 0 ‘ Turn on normal error checking
‘Find which error happened
Select Case ErrNum
‘ No error
‘ File is not open
Case 0: IsWorkBookOpen = False
‘ File already open
Case 70: IsWorkBookOpen = True
‘ Some other error occurred. Capture the error number for further action
Case Else: Error ErrNum
‘we check whether the workbook is open using a function that is shown below
info = IsWorkBookOpen(“C:\excel-training-videos-15\data-for-printing.xlsm”)
If info = False Then