title: VBA is sometimes not recognizing Excel file that has been opened through SAP GUI script
body: <p>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.</p>
  <p>Some days I get a runtime error</p>
  <blockquote>
  <p>Subscript out of range</p>
  </blockquote>
  <p>on <code>Set ws2 = Workbooks(&quot;FEBA_EXPORT_&quot; &amp; today2 &amp; &quot;.XLSX&quot;).Worksheets(&quot;Sheet1&quot;)</code>.</p>
  <p>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.</p>
  <p>I tried to insert the below code in front of the <code>Set ws2</code> line that is giving an error, and this code is always giving the massage that the file is open.</p>
  <pre class="lang-vb prettyprint-override"><code>Dim Ret
  Ret = IsWorkBookOpen(filepath &amp; &quot;FEBA_EXPORT_&quot; &amp; today2 &amp; &quot;.XLSX&quot;)
  If Ret = True Then
      MsgBox &quot;File is open&quot;
  Else
      MsgBox &quot;File is Closed&quot;
  End If
  </code></pre>
  <p>This is the function:</p>
  <pre class="lang-vb prettyprint-override"><code>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
  </code></pre>
  <p>This is the relevant part of the code:</p>
  <pre class="lang-vb prettyprint-override"><code>Sub CopyExportedFEBA_ExtractFEBRE()
  
      Dim SapGuiAuto As Object
      Dim SAPApp As Object
      Dim SAPCon As Object
      Dim session As Object
  
      Set SapGuiAuto = GetObject(&quot;SAPGUI&quot;)
      Set SAPApp = SapGuiAuto.GetScriptingEngine
      Set SAPCon = SAPApp.Children(0)
      Set session = SAPCon.Children.ElementAt(0) ' &lt;--- Assumes you are using the first session open. '
      
      Dim ws0, ws1, ws2, ws6, ws7 As Worksheet
      
      Set ws0 = Workbooks(&quot;FEB_BSPROC.xlsm&quot;).Worksheets(&quot;INPUT&quot;)
      Set ws1 = Workbooks(&quot;FEB_BSPROC.xlsm&quot;).Worksheets(&quot;FEB_BSPROC&quot;)
      Set ws6 = Workbooks(&quot;FEB_BSPROC.xlsm&quot;).Worksheets(&quot;FBL3N_1989&quot;)
      
      Dim today2, filepath As String
      today2 = ws0.Range(&quot;E2&quot;)
      filepath = ws0.Range(&quot;A7&quot;)
      
      ' Check if  if FEBA_EXPORT wb is open
      ' This is giving the message that the file is open
      
      Dim Ret
      Ret = IsWorkBookOpen(filepath &amp; &quot;FEBA_EXPORT_&quot; &amp; today2 &amp; &quot;.XLSX&quot;)
      If Ret = True Then
          MsgBox &quot;File is open&quot;
      Else
          MsgBox &quot;File is Closed&quot;
      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(&quot;FEBA_EXPORT_&quot; &amp; today2 &amp; &quot;.XLSX&quot;).Worksheets(&quot;Sheet1&quot;)
      
      'This is never giving any errors
      Set ws7 = Workbooks(&quot;1989_&quot; &amp; today2 &amp; &quot;.XLSX&quot;).Worksheets(&quot;Sheet1&quot;)
  </code></pre>
  <p>The <code>filepath</code> 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.<br />
  The <code>today2</code> variable is also correct.</p>
  <p>I thought that it would work if I could close the <code>ws2</code> 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.</p>
  <p>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.</p>