I have a SAP GUI script running every day in VBA. In the script I am exporting some data from SAP to several different Excel files, and these are saved to a network drive. In the first macro, I export data. In the second I copy the data to the same workbook as the script is in.
Some days I get a runtime error
Subscript out of range
on Set ws2 = Workbooks("FEBA_EXPORT_" & today2 & ".XLSX").Worksheets("Sheet1").
It looks like the Excel file is not recognized as open. I manually close the file, and reopen it and then the script will run.
I tried to insert the below code in front of the Set ws2 line that is giving an error, and this code is always giving the massage that the file is open.
Dim Ret
Ret = IsWorkBookOpen(filepath & "FEBA_EXPORT_" & today2 & ".XLSX")
If Ret = True Then
MsgBox "File is open"
Else
MsgBox "File is Closed"
End If
This is the function:
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
This is the relevant part of the code:
Sub CopyExportedFEBA_ExtractFEBRE()
Dim SapGuiAuto As Object
Dim SAPApp As Object
Dim SAPCon As Object
Dim session As Object
Set SapGuiAuto = GetObject("SAPGUI")
Set SAPApp = SapGuiAuto.GetScriptingEngine
Set SAPCon = SAPApp.Children(0)
Set session = SAPCon.Children.ElementAt(0) ' <--- Assumes you are using the first session open. '
Dim ws0, ws1, ws2, ws6, ws7 As Worksheet
Set ws0 = Workbooks("FEB_BSPROC.xlsm").Worksheets("INPUT")
Set ws1 = Workbooks("FEB_BSPROC.xlsm").Worksheets("FEB_BSPROC")
Set ws6 = Workbooks("FEB_BSPROC.xlsm").Worksheets("FBL3N_1989")
Dim today2, filepath As String
today2 = ws0.Range("E2")
filepath = ws0.Range("A7")
' Check if if FEBA_EXPORT wb is open
' This is giving the message that the file is open
Dim Ret
Ret = IsWorkBookOpen(filepath & "FEBA_EXPORT_" & today2 & ".XLSX")
If Ret = True Then
MsgBox "File is open"
Else
MsgBox "File is Closed"
End If
' This is giving runtime error 9 Subscript out of range
' If manually close the Excel and the reopen, then it will always work after this
Set ws2 = Workbooks("FEBA_EXPORT_" & today2 & ".XLSX").Worksheets("Sheet1")
'This is never giving any errors
Set ws7 = Workbooks("1989_" & today2 & ".XLSX").Worksheets("Sheet1")
The filepath varaiable is the full filepath to the network drive. So this is not the issue. Also I have another Excel file that is opened in the same way, and that one is never giving any errors.
The today2 variable is also correct.
I thought that it would work if I could close the ws2 workbook with VBA and then reopen it. So I tried to close it without setting it to a variable, but then I got the same error.
With SAP GUI scripting when you export anything to an Excel file, the file will open automatically after it has been saved. I am wondering if this could be the issue? I only have problems with this one Excel file, and not with any of several others that are saved and opened in the same way.
As I said in my above comment, the workbook may be open in a new session, different from the one where the code runs. Please, use the next function to identify if it is a matter of different Excel session:
Function sameExSession(wbFullName As String, Optional boolClose As Boolean) As Boolean
Dim sessEx As Object, wb As Object
Set sessEx = GetObject(wbFullName).Application
If sessEx.hwnd = Application.hwnd Then
sameExSession = True
Else
sameExSession = False
If boolClose Then
sessEx.Workbooks(Right(wbFullName, Len(wbFullName) - InStrRev(wbFullName, "\"))).Close False
sessEx.Quit: Set sessEx = Nothing
End If
End If
End Function
It identify the session where the workbook is open, then compare its handle with the active session one and if not the same, close the workbook (if calling the function with second parameter as True), quit the session and returns False. If only checking, call the function with the second parameter being False (the workbook will not be closed, and session will still remain).
It can be used in the next way:
Sub testSameExSession()
Dim wbFullName As String, wbSAP As Workbook
wbFullName = filepath & "FEBA_EXPORT_" & today2 & ".XLSX"
If sameExSession(wbFullName, True) Then
Debug.Print "The same session"
Set wbSAP = Workbooks("FEBA_EXPORT_" & today2 & ".XLSX")
Else
Debug.Print "Different session..."
Set wbSAP = Workbooks.Open(wbFullName)
End If
Debug.Print wbSAP.Name
'use the set workbook to do what you need...
End Sub
When you have the described problem, please use the above way to test if it is a matter of different sessions.
If so, is easy to input this part in your existing code, I think. If the workbook will be open in a different session, no need to manually close it (and reopen), the above function does it...