VBA - Download all documents using URLs & Save As to folder as same extension documents

[Gunasekaran]

Hi Team

I wonder whether someone can help me.

Hyperlink had a different type of extension like .xlsx. xls, pdf, .msg documents, all document should download and save in folder as the same Extention

Could someone possibly tell me please is there a way by which I can amend the code. this code is work as if document as .xlsx, but Other Extention not able to download through macro

To help this is the code that I use to create the list of files.

Option Explicit

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 images will be saved. Change as applicable
Const ParentFolderName As String = "\\gssux002\GBS_CHE_GL_TMS\ICV\"
Sub Download_Report()
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim Folderpath, strPath As String

Set ws = Sheets("ICV Report")

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

For i = 2 To LastRow

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

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

strPath = Folderpath & ws.Range("A" & i).Value & i & "*.*"
Ret = URLDownloadToFile(0, ws.Range("H" & i).Value, strPath, 0, 0)

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

Next i

MsgBox "Completed This process !!!", vbInformation

End Sub

 

[Logit]

.
Perhaps because your file macro is still using the following line of code ?

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

.