I'm trying to write a Macro that will refer out to a file I use to track credits(I call it Master6 below). In the code excerpt below I try to do some initial testing like, see if excel is even running, or if the Master6 file is already open. I'm able to get past the line of code where it correctly determines that the Master6 file is open, but then I get a Run Time error 9 at the part where it says "Set xWb = xExcelApp.Workbooks(xExcelFile)". At this line of code I'm trying to specify what xWb(aka my workbook) should be.
Note that I originally asked this question and I got one response saying, "You need to make sure that both applications are run under the same security context (run as admin or same user). There is no way to access/automate application which is run under a different security context." I don't know how to determine what security context a file is open under/how to make it match security contexts, even though I tried researching it.
Sub SearchQueryForCredits()
'Declare some initial variables
Dim xExcelFile As String
Dim xExcelApp As excel.Application
Dim xWb As excel.Workbook
Dim xWs As excel.Worksheet
Dim xExcelRange As excel.Range
Dim Cell As Range
xExcelFile = "C:\Users\USER\Documents\Desktop Credits\MASTER6.xlsm"
'initiate Excel object...Therefore, you can use this to test if there is an Excel instance already open on your machine.
On Error Resume Next
Set ExApp = GetObject(, "Excel.Application")
On Error GoTo 0
If ExApp Is Nothing Then Set ExApp = New excel.Application
'LOGIC TO TELL IF THE MASTER6 FILE IS ALREADY OPEN
Dim Ret
Ret = IsWorkBookOpen(xExcelFile)
'What to do if the Master6 file is open
If Ret = True Then
Set xExcelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
MsgBox "MASTER6 is not running"
Else
MsgBox "MASTER6 is running"
End If
Set xWb = xExcelApp.Workbooks(xExcelFile) <---- THIS IS WHERE I GET "RUN TIME ERROR '9': SUBSCRIPT OUT OF RANGE
Set xWs = xWb.Sheets("Query Run")
Set xExcelRange = xWs.Range("I:I")
'What to do if the Master6 file is NOT open
Else.......'rest of code i don't think is relevant to this issue
Also, my code above uses the function below in order to tell if my Master6 file is open.
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
Any assistance would be greatly appreciated! Thanks!
