VBS to run macro on already open .xlsm (if it's open)

[Technowolf]

Trying to figure out how to do this via VBS. First, check if the workbook is open, and if so, run the macro in it.

I found this, but this opens a readonly version in the background and runs it, which is no good for my purposes

Option Explicit

Dim xlApp, xlBook


Set xlApp = CreateObject("Excel.Application")


Set xlBook = xlApp.Workbooks.Open("C:/Temp Code Setup2.xlsm", 0, True)
xlApp.Run "RunnerFile"
xlBook.Close
xlApp.Quit


Set xlBook = Nothing
Set xlApp = Nothing




WScript.Quit

Suggestions?

 

[Technowolf]

Update:
Okay, I found this, which is exactly what I need, but for some reason isn't working:

wbName = "TempCodeSetup2.xlsm"
wbFullName = "C:\" & wbName


Set xl = GetObject(, "Excel.Application")
For Each wb In xl.Workbooks
  If LCase(wb.Path & "\" & wb.Name) = wbFullName Then
    wb.Application.Run wbName & "!RunnerFile"
  End If
Next

 

[Technowolf]

Update 2: Found a solution that works (in case anyone else has this weird need)

On Error Resume NextSet objWbk = GetObject("C:\TempCodeSetup2.xlsm")


If Err.Number = 0 Then objWbk.Application.Run "TempCodeSetup2.xlsm!Runnerfile" 


wScript.Quit

 

[pitaszek]

Update 2: Found a solution that works (in case anyone else has this weird need)

On Error Resume NextSet objWbk = GetObject("C:\TempCodeSetup2.xlsm")


If Err.Number = 0 Then objWbk.Application.Run "TempCodeSetup2.xlsm!Runnerfile" 


wScript.Quit

For some reason open read only for me. Can you post the code you used back then

Cheers,
Witek

 

[Technowolf]

Hey Pitaszek! This post is pretty old, but I've gotten better with VBA, so maybe I can help you out. Now a days I would use some kind of loop to check something like this, rather than depend on error handling. There's a few ways to do this, but off the top of my head:

Dim wb as Workbook
For Each wb In Application.Workbooks
  If InStr(wbk.Name, "YourWorkBookName") > 0 Then 'Update This Line
    Application.Run ("'YourWorkbookName.xlsm'!YourMacroName") 'Update This Line
  Else
    ' Do something else if workbook isn't open. You can remove also safely remove the "Else"
  End If
Next wb

Hopefully that helps you out!

 

[agentkramr]

Hey Pitaszek! This post is pretty old, but I've gotten better with VBA, so maybe I can help you out. Now a days I would use some kind of loop to check something like this, rather than depend on error handling. There's a few ways to do this, but off the top of my head:

Dim wb as Workbook
For Each wb In Application.Workbooks
  If InStr(wbk.Name, "YourWorkBookName") > 0 Then 'Update This Line
    Application.Run ("'YourWorkbookName.xlsm'!YourMacroName") 'Update This Line
  Else
    ' Do something else if workbook isn't open. You can remove also safely remove the "Else"
  End If
Next wb

Hopefully that helps you out!

i cant get your code to function, i throws an error line 1 char 8. i would like for it to fire one workbook right after another