I am working with the newest version of Excel / Office 2019 and am uncertain how to get an older workbook with VBA designed to pull multiple worksheets of data from a password protected site online.
In the past, I was able to go to Data>From Web>enter URL and sign in>close window>run Macros>and the data would refresh automatically on all pages.
With Excel 19, you have to enter the URL to open a window to download tables from, but there is no ability to navigate and sign into the website.
Is there a new way to enter this information so that when the URL opens I am able to sign in automatically, if I enter the username and password in another area?
Thank you in advance!
This is my Macro
Sub Refresh()
'
' refresh Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim sh As Worksheet
Dim qt As QueryTable
Dim vaSplit As Variant
Dim i As Long
'Loop through all the sheets
For Each sh In ThisWorkbook.Worksheets
'Only for sheets that start with "STATS19"
If Left(sh.Name, 7) = "STATS19" Then
'Make sure the sheet has a query on it
On Error Resume Next
Set qt = Nothing
Set qt = sh.QueryTables(1)
On Error GoTo 0
If Not qt Is Nothing Then
'If the query URL has &d2, we need to update it to today's date
If InStr(1, qt.Connection, "&d2=") > 0 Then
'break the URL into pieces
vaSplit = Split(qt.Connection, "&")
'find the piece that has d2 (the ending date)
For i = LBound(vaSplit) To UBound(vaSplit)
If Left(vaSplit(i), 3) = "d2=" Then
'replace that piece with today's date
vaSplit(i) = "d2=" & Format(Date, "m/d/yyyy")
Exit For 'stop looking
End If
Next i
'Replace the connection with our new URL containing today's date
qt.Connection = Join(vaSplit, "&")
End If
qt.Refresh False
End If
End If
Next sh
In the past, I was able to go to Data>From Web>enter URL and sign in>close window>run Macros>and the data would refresh automatically on all pages.
With Excel 19, you have to enter the URL to open a window to download tables from, but there is no ability to navigate and sign into the website.
Is there a new way to enter this information so that when the URL opens I am able to sign in automatically, if I enter the username and password in another area?
Thank you in advance!
This is my Macro
Sub Refresh()
'
' refresh Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim sh As Worksheet
Dim qt As QueryTable
Dim vaSplit As Variant
Dim i As Long
'Loop through all the sheets
For Each sh In ThisWorkbook.Worksheets
'Only for sheets that start with "STATS19"
If Left(sh.Name, 7) = "STATS19" Then
'Make sure the sheet has a query on it
On Error Resume Next
Set qt = Nothing
Set qt = sh.QueryTables(1)
On Error GoTo 0
If Not qt Is Nothing Then
'If the query URL has &d2, we need to update it to today's date
If InStr(1, qt.Connection, "&d2=") > 0 Then
'break the URL into pieces
vaSplit = Split(qt.Connection, "&")
'find the piece that has d2 (the ending date)
For i = LBound(vaSplit) To UBound(vaSplit)
If Left(vaSplit(i), 3) = "d2=" Then
'replace that piece with today's date
vaSplit(i) = "d2=" & Format(Date, "m/d/yyyy")
Exit For 'stop looking
End If
Next i
'Replace the connection with our new URL containing today's date
qt.Connection = Join(vaSplit, "&")
End If
qt.Refresh False
End If
End If
Next sh
