title: Reference workbooks between instances of Excel
body: <p>long time user, first question.</p>
  
  <p>So an internet site that my business used to get information on coal ship movements has recently been reworked, so I have to rework my program to scrape the ship information. I had been navigating to each port using button click events and using;
  <code>Dim Table As Object, Set Table = ie.document.getElementsByTagName("TABLE")(11)</code>
  to get the actual table. On the new site they have the option to export all ship movements to excel and it would be a lot quicker if I could automate the macro to get the excel files. To clarify I am just trying to get my program to go to this site; <a href="https://qships.tmr.qld.gov.au/webx/" rel="nofollow noreferrer">https://qships.tmr.qld.gov.au/webx/</a>, click on 'Ship Movements' up the top, click 'Tools', click 'Export to excel' then open the file and go back to the site and click 'Vessel At birth', 'Tools', 'Export to excel' and open that file, then use somthing like; </p>
  
  <p><code>Windows("Traffic.xls").Activate
      Application.ActiveProtectedViewWindow.Edit
      Sheets("Traffic").Select
      Application.DisplayAlerts = False
      Sheets("Traffic").Move After:=Workbooks("Search Ship Schedule.xlsm").Sheets(4)
      Application.DisplayAlerts = True</code></p>
  
  <p>To get the sheets from the workbooks back to my main workbook, where I will then search through and get the ones I want. Here's what I've got sofar;</p>
  
  <pre><code>Dim ws1, ws2 As Worksheet
  Set ws1 = ActiveSheet
  Set ws2 = ThisWorkbook.Sheets("Sheet1")
  ws2.Cells.ClearContents
  
  
  Dim Site, BtnPage(1 To 2), Btn As String
  Site = "https://qships.tmr.qld.gov.au/webx/"
  Dim ie As InternetExplorer
  
  Set ie = CreateObject("InternetExplorer.Application")
  ie.Visible = True
  ie.navigate Site
  
          Do While Not ie.readyState = 4 Or ie.Busy
              DoEvents
          Loop
          Application.Wait (Now() + TimeValue("0:00:3"))
  
  ie.document.getElementById("Traffic").Click
  
  
          Do While Not ie.readyState = 4 Or ie.Busy
              DoEvents
          Loop
          Application.Wait (Now() + TimeValue("0:00:3"))
  
  ie.document.getElementsByClassName("ui-widget fg-button fg-button-icon-left ui-corner-all grid-tools")(0).Click
  Sleep 100
  ie.document.getElementById("0").Click
  
          Do While Not ie.readyState = 4 Or ie.Busy
              DoEvents
          Loop
  
  Sleep 2500
  
  SendKeys "%o"
  
          Do While Not ie.readyState = 4 Or ie.Busy
              DoEvents
          Loop
  Sleep 6500
  
  'Sleep_DoEvents 7
  
  ie.document.getElementById("InPort").Click
  
  
  Do While Not ie.readyState = 4 Or ie.Busy
              DoEvents
          Loop
          Application.Wait (Now() + TimeValue("0:00:3"))
  
  ie.document.getElementsByClassName("ui-widget fg-button fg-button-icon-left ui-corner-all grid-tools")(0).Click
  Sleep 100
  ie.document.getElementById("0").Click
  
          Do While Not ie.readyState = 4 Or ie.Busy
              DoEvents
          Loop
  
          'Windows("Traffic").Activate
          'Application.Windows("Traffic.xls").ActiveProtectedViewWindow.Edit
          'Application.Windows("Traffic.xls").Activate
  
          Static hWnds() As Variant
          Sleep 500
          r = FindWindowLike(hWnds(), 0, "Public Pages - Internet Explorer", "*", Null)
  
          Sleep 3000
  
          If r &gt; 0 Then
              SetFocusAPI (hWnds(1))
              'Sleep 1000
              SendKeys "%o"
              Do While Not ie.readyState = 4 Or ie.Busy
                  DoEvents
              Loop
              Sleep 6000
              'Application.ActiveProtectedViewWindow.Edit
          End If
  'ie.Close
  </code></pre>
  
  <p>and I have this in a module</p>
  
  <pre><code>Public Declare Function BlockInput Lib "USER32.dll" (ByVal fBlockIt As Long) As Long
  
  
  #If VBA7 Then
      Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
  #Else
      Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
  #End If
  
   Declare Function SetFocusAPI Lib "User32" Alias "SetForegroundWindow" _
      (ByVal hWnd As Long) As Long
     Declare Function GetWindow Lib "User32" (ByVal hWnd As Long, _
      ByVal wCmd As Long) As Long
     Declare Function GetDesktopWindow Lib "User32" () As Long
     Declare Function GetWindowLW Lib "User32" Alias "GetWindowLongA" _
      (ByVal hWnd As Long, ByVal nIndex As Long) As Long
     Declare Function GetParent Lib "User32" (ByVal hWnd As Long) As Long
     Declare Function GetClassName Lib "User32" Alias "GetClassNameA" _
      (ByVal hWnd As Long, ByVal lpClassName As String, _
       ByVal nMaxCount As Long) As Long
     Declare Function GetWindowText Lib "User32" Alias "GetWindowTextA" _
      (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) _
       As Long
  
     Public Const GWL_ID = (-12)
     Public Const GW_HWNDNEXT = 2
     Public Const GW_CHILD = 5
     'FindWindowLike
     ' - Finds the window handles of the windows matching the specified
     '   parameters
     '
     'hwndArray()
     ' - An integer array used to return the window handles
     '
     'hWndStart
     ' - The handle of the window to search under.
     ' - The routine searches through all of this window's children and their
     '   children recursively.
     ' - If hWndStart = 0 then the routine searches through all windows.
     '
     'WindowText
     ' - The pattern used with the Like operator to compare window's text.
     '
     'ClassName
     ' - The pattern used with the Like operator to compare window's class
     '   name.
     '
     'ID
     ' - A child ID number used to identify a window.
     ' - Can be a decimal number or a hex string.
     ' - Prefix hex strings with "&amp;H" or an error will occur.
     ' - To ignore the ID pass the Visual Basic Null function.
     '
     'Returns
     ' - The number of windows that matched the parameters.
     ' - Also returns the window handles in hWndArray()
     '
     '----------------------------------------------------------------------
     'Remove this next line to use the strong-typed declarations
     #Const WinVar = True
     #If WinVar Then
     Function FindWindowLike(hWndArray() As Variant, _
      ByVal hWndStart As Variant, WindowText As String, _
       Classname As String, ID) As Integer
     Dim hWnd
     Dim r
     Static level
     Static iFound
     #ElseIf Win32 Then
     Function FindWindowLike(hWndArray() As Long, ByVal hWndStart As Long, _
      WindowText As String, Classname As String, ID) As Long
     Dim hWnd As Long
     Dim r As Long
     ' Hold the level of recursion:
     Static level As Long
     ' Hold the number of matching windows:
     Static iFound As Long
     #ElseIf Win16 Then
     Function FindWindowLike(hWndArray() As Integer, _
      ByVal hWndStart As Integer, WindowText As String, _
      Classname As String, ID) As Integer
     Dim hWnd As Integer
     Dim r As Integer
     ' Hold the level of recursion:
     Static level As Integer
     'Hold the number of matching windows:
     Static iFound As Integer
     #End If
     Dim sWindowText As String
     Dim sClassname As String
     Dim sID
     ' Initialize if necessary:
     If level = 0 Then
     iFound = 0
     ReDim hWndArray(0 To 0)
     If hWndStart = 0 Then hWndStart = GetDesktopWindow()
     End If
     ' Increase recursion counter:
     level = level + 1
     ' Get first child window:
     hWnd = GetWindow(hWndStart, GW_CHILD)
     Do Until hWnd = 0
     DoEvents ' Not necessary
     ' Search children by recursion:
     r = FindWindowLike(hWndArray(), hWnd, WindowText, Classname, ID)
     ' Get the window text and class name:
     sWindowText = Space(255)
     r = GetWindowText(hWnd, sWindowText, 255)
     sWindowText = Left(sWindowText, r)
     sClassname = Space(255)
     r = GetClassName(hWnd, sClassname, 255)
     sClassname = Left(sClassname, r)
     ' If window is a child get the ID:
     If GetParent(hWnd) &lt;&gt; 0 Then
     r = GetWindowLW(hWnd, GWL_ID)
     sID = CLng("&amp;H" &amp; Hex(r))
     Else
     sID = Null
     End If
     ' Check that window matches the search parameters:
     If sWindowText Like WindowText And sClassname Like Classname Then
     If IsNull(ID) Then
     ' If find a match, increment counter and
     '  add handle to array:
     iFound = iFound + 1
     ReDim Preserve hWndArray(0 To iFound)
     hWndArray(iFound) = hWnd
     ElseIf Not IsNull(sID) Then
     If CLng(sID) = CLng(ID) Then
     ' If find a match increment counter and
     '  add handle to array:
     iFound = iFound + 1
     ReDim Preserve hWndArray(0 To iFound)
     hWndArray(iFound) = hWnd
     End If
     End If
     Debug.Print "Window Found: "
     Debug.Print "  Window Text  : " &amp; sWindowText
     Debug.Print "  Window Class : " &amp; sClassname
     Debug.Print "  Window Handle: " &amp; CStr(hWnd)
     End If
     ' Get next child window:
     hWnd = GetWindow(hWnd, GW_HWNDNEXT)
     Loop
     ' Decrement recursion counter:
     level = level - 1
     ' Return the number of windows found:
     FindWindowLike = iFound
     End Function
  </code></pre>
  
  <p>My problem is that when these excel files are opening, they open in a new instance of excel and I can't reference them any regular way. Since they are not actually saved I can't use GetObject() like recommended in this answer <a href="https://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel">Can VBA Reach Across Instances of Excel?</a> and I don't know how to reference the excel workbooks using a handle. I think that they are opening in a new instance of excel because the macro is running and even when using Sleep() it doesn't let excel open the new workbooks. I have tried using a Do DoWhile Loop to let excel open the workbooks but that doesn't seem to work. So, if anyone could help me open the workbooks in the same instance of excel so that I can reference them easier or reference between excel instances without GetObject() that would be greatly appreciated.</p>
  
  <p>==================================EDIT=======================================</p>
  
  <p>This was the final result I wound up with. Thanks to user3565396 I just saved it in the downloads folder like you recommended, I couldn't figure out how to use <code>WinHttp</code> like Robert Co recommended. For some reason the code exits without an error message on the line <code>wb2.Sheets(1).Copy After:=wb1.Sheets("Import")</code> but re-opening seems to work fine and it's only used once or twice a day.</p>
  
  <pre><code>Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Integer
  
  Function DelTrafficAndInPort()
  
  'Clear all ws's like "Traffic" or "In Port" and all wb's
  
      'In VBE, click Tools, References, find "Microsoft Scripting Runtime"
      'and check it off for this program to work
      Dim fso As FileSystemObject
      Dim fold As Folder
      Dim f As File
      Dim folderPath As String
      Dim cbo As Object
  
      folderPath = "C:\Users\" &amp; Environ("username") &amp; "\Downloads"
  
      Set fso = New FileSystemObject
      Set fold = fso.GetFolder(folderPath)
  
      For Each f In fold.Files
          If ((Left(f.Name, 7) = "Traffic" Or Left(f.Name, 7) = "In Port") And Right(f.Name, 4) = ".xls") Then
              fso.DeleteFile f.Path
          End If
      Next
  End Function
  
  
  
  Sub BtnScrape_Click()
  
      Application.ScreenUpdating = False
      Application.DisplayStatusBar = False
      Application.Calculation = xlCalculationManual
      Application.EnableEvents = False
  
  
  Dim wb1, wb2 As Workbook
      Set wb1 = ActiveWorkbook
  
      Run DelTrafficAndInPort() ' from downloads
  
      Dim ws As Worksheet
      Application.DisplayAlerts = False
      For Each ws In wb1.Worksheets
          If (Left(ws.Name, 7) = "Traffic" Or Left(ws.Name, 7) = "In Port") Then ws.Delete
      Next ws
      Application.DisplayAlerts = True
  
  Dim ie As InternetExplorer 'SHDocVw.InternetExplorer
  Dim sw As New SHDocVw.ShellWindows
  
  Set ie = CreateObject("InternetExplorer.Application")
  ie.Visible = True
  ie.navigate "https://qships.tmr.qld.gov.au/webx/"
  
  Do While Not ie.readyState = 4 Or ie.Busy
  DoEvents
  Loop
  
  Dim BtnName(1 To 2), wbPath(1 To 2) As String
      BtnName(1) = "Traffic"
      BtnName(2) = "InPort"
      wbPath(1) = "C:\Users\" &amp; Environ("username") &amp; "\Downloads\Traffic.xls" '"C:\Users\owner\Downloads\Traffic.xls"
      wbPath(2) = "C:\Users\" &amp; Environ("username") &amp; "\Downloads\In Port.xls"
  
  Dim I As Integer
  For I = 1 To 2
      ie.document.getElementById(BtnName(I)).Click
  
      Do While Not ie.readyState = 4 Or ie.Busy
      DoEvents
      Loop
  
      Application.Wait (Now() + TimeValue("00:00:04"))
  
      ie.document.getElementsByTagName("span")(8).Click 'Tools
      Application.Wait (Now() + TimeValue("00:00:01"))
      ie.document.getElementById("0").Click             'Export to Excel    'ie.document.getElementsByTagName("span")(27).Click
      Application.Wait (Now() + TimeValue("00:00:5"))
  
      SetForegroundWindow (ie.hwnd)
      Application.Wait (Now() + TimeValue("00:00:01"))
      SendKeys "%S" 'Save
      Application.Wait (Now() + TimeValue("00:00:02"))
      Set wb2 = Workbooks.Open(wbPath(I))
      wb2.Sheets(1).Copy After:=wb1.Sheets("Import")
      wb2.Close False
  Next I
  ie.Quit
  
  wb1.Sheets("Import").Select
  
  Run DelTrafficAndInPort() ' from downloads
  
      Application.ScreenUpdating = True
      Application.DisplayStatusBar = True
      Application.Calculation = xlCalculationAutomatic
      Application.EnableEvents = True
  
  MsgBox "Finished"
  End Sub
  </code></pre>