Downloading URL to SaveAs in Directory

[Varsakov]

Hi All,

I am trying to download about 250 hyperlinks to .pdfs. Obviously, this is a very tedious process if done manually so I am trying to automate it using VBA. I tried URLDownloadtofile but it doesn't correctly download the page so that I can open it. I think the reason with this has to do with the fact that the site has to authenticate. So, I've trying the XMLHTTP approach. I figured I'd first try to find some code that works for this purpose and then modify it to suit my needs. So far I have something that I found online (btw, couldn't use hyperlinks because I have fewer than 5 posts apparently) :

Sub Save()
SaveWebFile "I would have the hyperlink in here", "C:\blahblah"
End Sub


Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
Set oXMLHTTP = CreateObject("msxml2.xmlhttp")
oXMLHTTP.Open "GET", vWebFile, False
oXMLHTTP.send
oResp = oXMLHTTP.ResponseBody
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF
Set oXMLHTTP = Nothing
End Function

However, when I run it, I get the following error "Run-time error '75': Path/File access error. Can someone please explain why that is the case and what I can do to remedy the problem?

I guess, ultimately, I am not married to using the above process, but from what I understand it one of the ways to authenticate to a website, right? Thank you for your help!

[Varsakov]

Hi All,

I was wondering if someone had a chance to look at my above inquiry. I really hope I can figure this out soon. Thank you in advance.

-Vasilis

[Kenneth Hobs]

I don't see the purpose in downloading hyperlinks. Do you mean the address of a hyperlink? Is it an address in an Excel hyperlink or on the web site? Is the address a file such as http://www.ken.html/files/ken.pdf?

If it is a secure site, I don't see either method working. Obviously, the .ResponseBody would only return the html's body code in text format.


Until you get 5 posts, just breaks urls into words.

Here is an example of how I used urldownloadtofile.
http://www.vbaexpress.com/forum/showthread.php?t=43015

You can attach an Excel file.

[Varsakov]

I don't see the purpose in downloading hyperlinks. Do you mean the address of a hyperlink? Is it an address in an Excel hyperlink or on the web site? Is the address a file such as

If it is a secure site, I don't see either method working. Obviously, the .ResponseBody would only return the html's body code in text format.


Until you get 5 posts, just breaks urls into words.

Here is an example of how I used urldownloadtofile.


You can attach an Excel file.

Hi Kenneth,

Thank you for your reply. Let me clarify: I am trying to download the destinations of the hyperlinks which are basically online pdf reports, much like the URL you provided and save as to the directory. Attached is a file I have been working with:
Example File(1).xlsx

Column A is the Name of the Deal, Column B you can disregard, Column C is the hyperlink that leads to the pdf I want to save, Column F is the Location to which I want to save the file, and column G is ultimately the name I want to save the file as.

The crux now is downloading the file, because when I use URLDownloadtofile, it downloads something, but I get an error when I try to open it. The following code used to work for a very similar file for the same website from which I am trying to download the files (meaning the following code worked with authentication), but it doesn't anymore:

[VBA]Public Sub test()

Row = 0
Dim fileName As Variant
Dim hoursecond As Variant
hoursecond = Hour(Now) & Second(Now)

'On Error Resume Next
Do While Sheets("sheet1").Range("a2").Offset(Row, 0) <> ""

fileSource = Sheets("sheet1").Range("a2").Offset(Row, 3)
fileName = Sheets("sheet1").Range("a2").Offset(Row, 0)
saveLocation = Sheets("sheet1").Range("a2").Offset(Row, 5)
prevSavedFile = Sheets("sheet1").Range("a2").Offset(Row, 6)

savedFile = ""
If UBound(fileName) > 0 Then
Application.StatusBar = "Checking " & Sheets("sheet1").Range("a2").Offset(Row, 0) & "..."

Sheets("sheet1").Range("a2").Offset(Row, 4) = Date
savedFile = Download_File(fileSource, fileName, saveLocation, prevSavedFile, hoursecond)

If savedFile <> "" Then
Sheets("sheet1").Range("a2").Offset(Row, 6) = savedFile
End If
End If
Row = Row + 1


Loop
MsgBox "DONE"
End Sub


Function Download_File(ByVal vWebFile As String, ByVal vLocalFile As String, ByVal saveLocation As String, ByVal prevFile As String, ByVal hoursecond As String) As String
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte

'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
oXMLHTTP.Open "GET", vWebFile, "intern1", "Welcome1" 'Open socket to get the website
oXMLHTTP.Send 'send request
savedFile = ""

'Wait for request to finish
Do While oXMLHTTP.readyState <> 4
DoEvents
Loop

fileName = vLocalFile



If Not FileFolderExists(saveLocation & "\remits\") Then
MkDir saveLocation & "\remits\"
End If

vLocalFile = saveLocation & "\remits\" & fileName & "_" & Month(Now) & "_" & Day(Now) & "_" & Year(Now) & "_" & hoursecond & ".pdf"
'vlocalFileTemp = saveLocation & "\remits\" & fileName & "temp.pdf"

'If FileFolderExists(vLocalFile) Then
' Kill vLocalFile
'End If

oResp = oXMLHTTP.responseBody 'Returns the results as a byte array

'Create local file and save results to it
vFF = FreeFile

Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF

'If FileLen(vLocalFile) = FileLen(vlocalFileTemp) Then
' Kill vlocalFileTemp
'End If

filenamePrevarr = Split(prevFile, "\")
If UBound(filenamePrevarr) > 0 Then
fileNamePrev = filenamePrevarr(UBound(filenamePrevarr))
End If

fileNameCurArr = Split(vLocalFile, "\")
If UBound(fileNameCurArr) > 0 Then
fileNameCur = fileNameCurArr(UBound(fileNameCurArr))
End If

If prevFile <> "" Then
If FileLen(prevFile) = FileLen(vLocalFile) And fileNamePrev <> fileNameCur Then
Kill vLocalFile
Else
Download_File = vLocalFile
End If
Else
Download_File = vLocalFile
End If

' say unavailable in remittance column except what if file already exists?
If FileLen(vLocalFile) < 3000 And prevFile = "" _
And UCase(Sheets("sheet1").Range("a2").Offset(Row, 1)) <> "NEED PASSWORD" Then

Sheets("sheet1").Range("a2").Offset(Row, 2) = "Unavailable"

End If

' if less than 3kb delete
If FileLen(vLocalFile) < 3000 Then
Kill vLocalFile
End If





'Clear memory
Set oXMLHTTP = Nothing

End Function


Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Ken Puls
'Macro Purpose: Check if a file or folder exists

On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True

EarlyExit:
On Error GoTo 0

End Function

Sub TestUbound()
Dim Msg As String
Msg = UBound(fileName)
MsgBox Msg

End Sub[/VBA]

I think the reason it may not be working this time is because I am running this on a 64 bit computer, so I am trying to build this from the ground up. Any ideas on what I can be doing? Thank you so much Kenneth - I look forward to hearing your thoughts.

Best,
Vasilis

[Varsakov]

I don't see the purpose in downloading hyperlinks. Do you mean the address of a hyperlink? Is it an address in an Excel hyperlink or on the web site? Is the address a file such as

If it is a secure site, I don't see either method working. Obviously, the .ResponseBody would only return the html's body code in text format.


Until you get 5 posts, just breaks urls into words.

Here is an example of how I used urldownloadtofile.


You can attach an Excel file.

Hi Kenneth,

I was wondering if you had a chance to take a look at what I'm trying to do and why I am receiving the Cannot Access Path error I cited above. As I explained, I am ultimately trying to use VBA to download and save a PDF located at the end of a URL that needs authentication (Username and Password)to a specific file location. I have tried URLDownloadtofile (that fails because it won't authenticate websites and downloads corrupt/encrypted pdf files); I have tried the print as pdf way (but can't send sendkeys to the save as screen); I have tried the createObject("MSXML2.ServerXMLHTTP.6.0") method and that gives me errors... I know there is a way to do this but I just haven't been able to figure it out. Please can someone help me! Thank you in advance.

-V

[codeperson]

The Function SaveWebFile in the first posting of this thread works on my computer. However, it always automatically choose Internet Explorer to open the URL and to download the pdf files. I was wondering how I can modify the code so that it will choose Google Chrome to open the URL and to download files. Thanks!