akin
04-07-2024, 03:16 AM
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
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