Download Multiple tables following URLs list in an Excel's worksheet Through VBA

[akin]

Hello, VBA gurus,
I am a VBA learner. I need an assistance on how to download bulk of tables from a website automatically. I was told an excel VBA can perform the logic. I have the list of all the URLs of the pages which contain the tables. The workbook for the list is attached. From the list, I want a VBA script that will pick the URL one after the other in column B as shown, open, download and save it with the title in column A as shown in a folder on the window ("C:\Users\Akinyele\Desktop\Stock HV").
I have copied so many scripts online like the one below, but it has not been working. Your help will be greatly appreciated. Thanks.

Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

'~~> This is where the table will be saved. Change as applicable
Const ParentFolderName As String = "C:\Users\Akinyele\Desktop\Stock HV"

Sub Sample()
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim Folderpath, strPath As String

Set ws = Sheets("Sheet1")

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

Folderpath = ParentFolderName & ws.Range("A" & i).Value & ""

If Len(Dir(Folderpath, vbDirectory)) = 0 Then
MkDir Folderpath
End If

strPath = Folderpath & "File" & i & ".csv"
Ret = URLDownloadToFile(0, ws.Range("B" & i).Value, strPath, 0, 0)

If Ret = 0 Then
ws.Range("C" & i).Value = "File successfully downloaded"
Else
ws.Range("C" & i).Value = "Unable to download the file"
End If

Next i
End Sub

[jdelano]

There is a button on the page that allows you to download a csv file. The URL it uses is:

https://query1.finance.yahoo.com/v7/finance/download/AMAT?period1=1680858276&period2=1712480676&interval=1d&events=history&includeAdjustedClose=true

You can use the code you have but instead use this URL and replace the /AMAT? with each of the ones you want downloaded, if you're running this in 64-bit Office add PrtSafe to the declaration of the function to download the file.
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

This simple code snippet downloaded the first in your list


Dim downloadURL As String
downloadURL = "https://query1.finance.yahoo.com/v7/finance/download/AMAT?period1=1680858276&period2=1712480676&interval=1d&events=history&includeAdjustedClose=true"
Dim ret As Variant
ret = URLDownloadToFile(0, downloadURL, "F:\temp\test.csv", 0, 0)

[akin]

There is a button on the page that allows you to download a csv file. The URL it uses is:

https://query1.finance.yahoo.com/v7/finance/download/AMAT?period1=1680858276&period2=1712480676&interval=1d&events=history&includeAdjustedClose=true

You can use the code you have but instead use this URL and replace the /AMAT? with each of the ones you want downloaded, if you're running this in 64-bit Office add PrtSafe to the declaration of the function to download the file.
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

This simple code snippet downloaded the first in your list


Dim downloadURL As String
downloadURL = "https://query1.finance.yahoo.com/v7/finance/download/AMAT?period1=1680858276&period2=1712480676&interval=1d&events=history&includeAdjustedClose=true"


Dim ret As Variant
ret = URLDownloadToFile(0, downloadURL, "F:\temp\test.csv", 0, 0)



Really appreciate.
Do you mean I will be altering the code for each stock I am to download?
I just released the sample for testrun. I have up to 200 of such urls.
Thanks.

[p45cal]

You can try something like this:
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Sub blah()
DownloadURLPt1 = "https://query1.finance.yahoo.com/v7/finance/download/"
DownloadURLPt2 = "?period1=1680858276&period2=1712480676&interval=1d&events=history&includeAdjustedClose=true"
Dim ret As Variant

For Each cll In Range("A1:A10").Cells
ret = URLDownloadToFile(0, DownloadURLPt1 & cll.Value & DownloadURLPt2, "C:\Users\Akinyele\Desktop\Stock HV\" & cll.Value & ".csv", 0, 0)
Next cll
End Sub

[Bob Phillips]

How about loading them into PowerQuery and then loading into Excel. You could have a table of URLs in Excel that you use as the source for a base query that then gets the rest.

[p45cal]

How about loading them into PowerQuery and then loading into Excel. You could have a table of URLs in Excel that you use as the source for a base query that then gets the rest.
Yes, as a starting point, see attached.

[p45cal]

grrrr.
https://www.excelforum.com/excel-programming-vba-macros/1421770-auto-download-multiple-tables-from-excels-url-list.html

[akin]

I am eternally grateful. It works perfectly. Thanks.

[Aussiebear]

akin, did you read and understand the rules about cross posting?