Macro to download files from the internet

[ttbuson]

I have this macro, which I want to download all the files given by the hyperlinks in column a. The problem is that the line of code with the Choose function isn't working. Is there a way to enter an array as the value argument in Choose or another function that will let me do what Choose tries to do?

Thanks!

Sub DownloadFiles()
' Downloads files given by any hyperlinks in Column A.
    Const strPath As String = "G:\DownloadData\"
    Dim strFile As String, lnum As Long
    Dim rng As Range
    Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For lnum = 1 To rng.Count
        strFile = Choose(lnum, rng)
        ThisWorkbook.FollowHyperlink strFile
        With ActiveWorkbook
            .SaveAs strPath & strFile
            .Close
        End With
    Next lnum
     
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[ttbuson]

Bump. Anyone with any ideas?

 

[Norie]

Try this.

strfile = Range("A"& lnum).Value

 

[ttbuson]

Thanks for the suggestion, but it didn't work. I should have mentioned that the cells only have the filename as the cell value not the link. The following code works, but it saves the file as an excel workbook that when opened opens the internet file.

Is there a way to save the files it opens as pdfs in the specified folder?

Thanks!

[FONT=Courier New]Sub DownloadFiles()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]    Const strPath As String = "G:\DownloadData\"<o:p></o:p>[/FONT]
[FONT=Courier New]    Dim strFile As String, lnum As Long<o:p></o:p>[/FONT]
[FONT=Courier New]    Dim rng As Range<o:p></o:p>[/FONT]
[FONT=Courier New]    Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)<o:p></o:p>[/FONT]
[FONT=Courier New]     <o:p></o:p>[/FONT]
[FONT=Courier New]    Application.ScreenUpdating = False<o:p></o:p>[/FONT]
[FONT=Courier New]    Application.DisplayAlerts = False<o:p></o:p>[/FONT]
[FONT=Courier New]    <o:p></o:p>[/FONT]
[FONT=Courier New]    For Each cell In rng<o:p></o:p>[/FONT]
[FONT=Courier New]        strFile = "web address" + cell.Value + ".pdf"<o:p></o:p>[/FONT]
[FONT=Courier New]        ThisWorkbook.FollowHyperlink strFile<o:p></o:p>[/FONT]
[FONT=Courier New]        With ActiveWorkbook<o:p></o:p>[/FONT]
[FONT=Courier New]            .SaveAs strPath & cell.Value<o:p></o:p>[/FONT]
[FONT=Courier New]        End With<o:p></o:p>[/FONT]
[FONT=Courier New]    Next cell<o:p></o:p>[/FONT]
[FONT=Courier New]     <o:p></o:p>[/FONT]
[FONT=Courier New]    Application.ScreenUpdating = True<o:p></o:p>[/FONT]
[FONT=Courier New]    Application.DisplayAlerts = True<o:p></o:p>[/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[Norie]

So it's not workbooks you are trying to download?

I can't see how Excel could download other types of files.

 

[circledchicken]

Hi,

I'm not sure if this is what your looking for, but if it helps Chip Pearson has a nice method for downloading files here:
http://www.cpearson.com/excel/DownloadFile.aspx

 

[ttbuson]

Wow, great link! I'll definitely give it a try when I have a chance.

 

[jeffcoleky]

Wow, great link! I'll definitely give it a try when I have a chance.

Did you ever get this working? I'm stumped on a similar issue. Here's my post: http://www.mrexcel.com/forum/showthread.php?t=629238