0

When I open the file Book1.xlsm with a vbs script, it doesn't see that Book2.xlsm is already open, and this is a problem because the macro1 I want to run in Book1 does something different depending on if it is open or not. Is there a line of code to run in the shell or in vba to have excel fix this mistake?

Here is the code in the vbs file:


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts=False
Set wb = objExcel.Workbooks.Open("C:/Test/Book1.xlsm")
objExcel.Application.Run "Book1.xlsm!Macro1"
braX
  • 11,506
  • 5
  • 20
  • 33
AnthonyH
  • 105
  • 8
  • have you tried stepping through line by line and pausing until the wb is fully open? – urdearboy Dec 05 '19 at 23:42
  • You could try [this](https://excel.tips.net/T009451_Finding_Other_Instances_of_Excel_in_a_Macro.html) – Damian Dec 05 '19 at 23:43
  • 1
    You need to get a reference to Book2. Try `Workbooks()` collection or `GetObject`. –  Dec 05 '19 at 23:44
  • 1
    Likely you are opening Book1 in a different instance of Excel. If there's already a single open instance of Excel, try using GetObject() in your script instead of CreateObject() `Set objExcel = GetObject(, "Excel.Application")` – Tim Williams Dec 06 '19 at 01:14
  • 1
    Use `GetObject("c:\book2")` this will connect to any excel window with book2 or open it if not already opened. –  Dec 06 '19 at 01:58
  • 1
    Pardon the hammer here, the [tag:shell] tag should not have been applied here. Anyway, if you don't think the duplicate applies, please [edit] your question to clarify how exactly this is different. – tripleee Dec 06 '19 at 12:39

1 Answers1

-1

You can check all open workbooks and see if the name matches "Book2.xlsm", and if so, do something. 

For Each Workbook in Workbooks
    If Workbook.Name = "Book2.xlsm" Then
        Debug.Print "Do something"

    Else
        Debug.Print "Do something else"

    End If
Next Workbook
PuravTheGreat
  • 136
  • 12