Unable to Activate spreadsheet already open

[L33ds]

I have 2 spreadsheets, Test123.xlsx and Test456.xlsx. The VBA code I have written opens Test456.xlsx, and writes some information to it. I also want to activate Test123.xlsx which will already be open when I run the code and write some information to that but I keep getting a Subscript out of Range error message. Can you please assist me with this? My code is as follows:

Sub RiskSummaryStatsHideMovt()
' RiskSummaryStatsHideMovt Macro
'
    
'   Open Test456 if not already open
    Ret = IsWorkBookOpen("H:\_Misc\_Misc\Test456.xlsx")

    If Ret = False Then
        Set wbLineage1 = Workbooks.Open("H:\_Misc\_Misc\Test456.xlsx")
        wbTest456 = "Test456.xlsx"
    End If

    strFilePath = "H:\_Misc\_Misc\"
    strFileName = "Test123.xlsx"
    strsOpenSpreadsheet = strFilePath & strFileName

    Set OpenSpreadsheet = Workbooks(strFileName)     'Error Message appears on this line of code

    Workbooks(wbTest456).Activate
    Worksheets("Sheet456").Range("A2").Select
    ActiveCell.Value = "Hello World2"

End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

 

[RoryA]

That error would be caused either by the workbook name being incorrect, the workbook not actually being open, or by the workbook being open in a different instance of Excel.

 

[L33ds]

Rory,
Many thanks for your response.
By process of elimination I am assuming I must be opening the files in different instances. How can I be sure this is the case? Assuming this is the case, how do I open Excel files using VBA code using just 1 instance?
Kind Regards L33ds

 

[RoryA]

If you use Workbooks.Open, the workbooks will open in the same instance as the workbook with the code.

 

[L33ds]

Rory,
Apologies but my previous question was not very clear.
I am opening Test123.xlsx before running the macro using Windows Explorer or dragging the file onto a blank spreadsheet. Is there a way I can force Excel to open Test456.xlsx in the same instance as Test123.xlsx was opened in?
Kind Regards L33ds

 

[RoryA]

Yes, but unless that's the same instance as your code is in, you'd have to rewrite all of that code to refer to the correct instance. Can't you just open the workbook from the File-Open dialog of the workbook with the code in it?

 

[L33ds]

Rory,
I might have to rethink things here.
Test123 is a spreadsheet that contains all of our sales information since the year dot. Each month I run a batch file (of ~400 different combinations) which opens up Test123 and Test456, filters Test123 based upon region and product type, copies this data into Test456, then saves Test456 and closes Test123 and Test456.
Test123 is a large spreadsheet, so, to save time I only want to open Test123 once.
Each time I run a new combination it runs in a new instance. Is there any way I can get around opening Test123 for every combination?
Thank you very much for your assistance so far, you have been a great help.
Kind Regards Nick

 

[RoryA]

It depends on what you are trying to do with that workbook. In your code you can use Getobject(, "full path to the 123 workbook") to get a reference to that workbook as an object and then use that in your code, but some operations will not work across application instances.

 

[L33ds]

Rory,
At the moment I am using slicers in Test123 to select the relevant criteria for the report. As part of my drive to improve performance I am going to try and change to use filters. I am then copying the pivoted data into Test456.
I need to go away and try to get things going using GetObject as you have suggested.
Thank you very much for your swift responses, you have been a great help.
Kind Regards Nick

 

[RoryA]

Nick,

From what you describe, that should all be achievable across instances, so you shouldn't have any issues (fingers crossed!).