long time user, first question.
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;
Dim Table As Object, Set Table = ie.document.getElementsByTagName("TABLE")(11)
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; https://qships.tmr.qld.gov.au/webx/, 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;
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
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;
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 > 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
and I have this in a module
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 "&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) <> 0 Then
r = GetWindowLW(hWnd, GWL_ID)
sID = CLng("&H" & 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 : " & sWindowText
Debug.Print " Window Class : " & sClassname
Debug.Print " Window Handle: " & 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
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 Can VBA Reach Across Instances of Excel? 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.
==================================EDIT=======================================
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 WinHttp like Robert Co recommended. For some reason the code exits without an error message on the line wb2.Sheets(1).Copy After:=wb1.Sheets("Import") but re-opening seems to work fine and it's only used once or twice a day.
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\" & Environ("username") & "\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\" & Environ("username") & "\Downloads\Traffic.xls" '"C:\Users\owner\Downloads\Traffic.xls"
wbPath(2) = "C:\Users\" & Environ("username") & "\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
