title: VBA continues without waiting for second workbook to open/close
body: <p>I'm looping through a list of csv files from a website which I want to open and extract the data from. The reason I'm using VBA is because the specific files I need to open varies day per day, and those specific file references is available only in an excel view. The second reason is because I've already made a similar scraping application in vba, so I already had half the code.</p>
  
  <p>The end user of the application doesn't need clean or fast code, just that it works, because checking these files manually now is a daily chore of 2 hours per day.</p>
  
  <p>So far I'm already logged into the website where the secondairy files are stored (This website has no API so I'm scraping it) and I'm opening those files by letting the code click the buttons. The code then clicks the export button, which opens the dialog box to </p>
  
  <pre><code>open, save (dropdown), cancel
  </code></pre>
  
  <p>I just want to open and extract the data then close, so I'm using the code from <a href="https://stackoverflow.com/questions/10400795/vba-internet-explorer-automation-how-to-select-open-when-downloading-a-file">VBA Internet Explorer Automation - How to Select &quot;Open&quot; When Downloading a File</a> . It feels like my code is bugging on this part...</p>
  
  <pre><code>Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
  Dim ie As InternetExplorer
  Dim h As LongPtr
  
  Function Download()
      Dim o As IUIAutomation
      Dim e As IUIAutomationElement
      Set o = New CUIAutomation
      h = ie.Hwnd
      h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString)
      If h = 0 Then Exit Function
  
      Set e = o.ElementFromHandle(ByVal h)
      Dim iCnd As IUIAutomationCondition
      Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Open")
  
      Dim Button As IUIAutomationElement
      Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
      Dim InvokePattern As IUIAutomationInvokePattern
      Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
      InvokePattern.Invoke
  End Function
  </code></pre>
  
  <p>before I invoke this function I check the amount of workbooks and make excel wait upto 20 seconds until another workbook is opened. I do that with this code</p>
  
  <pre><code>    xnum1 = Application.Workbooks.Count
  
      Download
  
      t = Now
      tStop = t + TimeValue("00:00:20") 'Adjust the TimeValue as needed "hh:mm:ss"
      Do Until t = tStop Or Application.Workbooks.Count &gt; xnuml
          DoEvents
          t = Now
      Loop
  </code></pre>
  
  <p>However it seems that even with this wait time, excel doesn't want to open the file during every loop. If I run it step by step, it does open the file, when I let it run on it's own, it doesn't.</p>
  
  <p>After I downloaded the file I export the data to the main file and then close it, using <em>Application.DisplayAlerts</em> to ensure no dialog box prevents the closing of the file. But because the code bugs in the download I've had instances where the file opened too late and then it didn't close, and because they all have the same filename I made an extra safegaurd in the main code: </p>
  
  <pre><code>    xnum1 = Application.Workbooks.Count
      For y = 1 To xnum1
          If Left(Application.Workbooks(y).Name, 10) = "export.csv" Then
              Application.Workbooks(y).Close
          End If
      Next
  </code></pre>
  
  <p>I can't seem to figure out why excel isn't opening the files properly and then closing them properly... Seems like the code just runs, goes into error mode because the file didn't open in the second run, although no error is given by the code itself even when I step through it.</p>
  
  <p>My gut is telling me that this Download function is where the problem lies, but I can't point out how to fix it...</p>
  
  <p>I've also tried with sendkeys %{O}, the shortkey to click open, but this also didn't open the file. When I manually click alt + O it does open the file...</p>
  
  <p>Any suggestions?</p>