1 Attachment(s)
Download Multiple tables following URLs list in an Excel's worksheet Through VBA
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.
Code:
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