Response to download Web URL files to Hard drive and path

[rmplant]

Dan,
UPPER case is for emphasis. I am not yelling. Thanks.

I downloaded your posting and tried it. First of all thanks. BUT it did NOT work for me regarding the
DownloadFile DocumentURL, "D:\TEMP\SaveDocumentHere.Doc" substituting my drive:\path\file name and when using a variable such as DestinationFile.

For the Function I copied it and kept getting a Compile Error for (FileExpress) as variable NOT defined. I then inserted right above that line a Dim statement and it now runs through my loop to try and download some 177 files BUT NO FILES are copied.

Here is my coding: YOUR Function first in a separate Module as listed.

Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As Long
#Else
    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
#End If

Public Function DownloadFile(ByVal URL As String, ByVal DestinationFile As String) As Boolean
    ' If the API returns ERROR_SUCCESS (0), DownloadFile returns True.
   
    Dim Error_Success As Integer  ' My addition which averts the error message.
   
    DownloadFile = (URLDownloadToFile(0&, URL, DestinationFile, &H10, 0&) = CLng(Error_Success))
End Function

and here is my FULL coding. The files I am trying to download are about 177 files from the United States Coast Guard on Sample Exams they post each year for merchant mariners to sit for their credential exams. NOT ONE is copied and saved on my hard drive in Drive C: or on my Drive Z:. I am using a MAC BOOK PRO running Parallels with Windows 11 on a Virtual Machine.

Sub Download_NMC_And_Copy()

    Dim DocumentURL As String
    Dim DestinationFile As String
    Dim myfile As String
    Dim imax, icount As Integer
    Dim Error_Success As Integer
    Dim myURL As String

    ' files to Download from NMC for Deck

    imax = 178     ' In Column B, starting at Row 2, I have the 178 file names listed that I need to download
    icount = 2      ' Because it is Row 2

    Application.ScreenUpdating = True

    Do While icount <= imax
        myfile = Range("B" & icount)
        Range("B" & icount).Select        ' The URL for the NMC website where the files are stored on the USCG computer is correct.
                                                           ' I can MANUALLY open the files and then SAVE AS to my hard drive but that takes a lot of time and is a pain.
        DocumentURL = "[URL]https://www.dco.uscg.mil/Portals/9/NMC/pdfs/examinations/[/URL]" & myfile    '  "q100_ror_inland-international.pdf"     is my FIRST file on the NMC website which you might want to replace for "myfile".
        DestinationFile = "C:/NMC/2022/" & myfile
        DownloadFile DocumentURL, DestinationFile
        icount = icount + 1
    Loop

End Sub

NOW my program previously use to work BUT a NEWER version of Visual Basic was installed and now many of my macros are giving me problems. This one is a major problem. So far I can fix the others I have encountered.

THANK YOU very much in advance.

rmplant

 

[rmplant]

Dan,

Thank you very much for ALL of your efforts. It's still NOT WORKING. Same error message. I am at a school where internet security might be high. I will keep all of the information in my computer and try it when I get home tomorrow night. THANK YOU.

rmplant

 

[Dan_W]

No problem. Keep us posted.

 

[rmplant]

No problem. Keep us posted.

Dan, I looked at my downloaded files and do see on September 6, 2022, just days ago when my problem started, a "ie 55 up.js" file was installed under auto-update. I do NOT use Internet Explorer which I think what this is file is for. I use Google Chrome. Do you know of anyway to UNINSTALL this ie 55 up.js file?
rmplant

 

[Dan_W]

If it's something that you've simply downloaded, I don't see that it will automatically have been installed. In any event, it's a Javascript file, which as far as I'm aware, is not something that can be installed - it is something that is called by the browser when required.

If it's something installed as part of a Windows update (which is surprising, because IE isn't meant to be available at all in Windows 11), then you really shouldn't touch it, and I really doubt it's the cause the problem. It may be part of Edge installation, which provides compatability for websites made for viewing in IE, but again, I don't think it'd be causing this error.

 

[Dan_W]

Did you get it working?

 

[rmplant]

Hi, it's not quite what I meant re: COM but that's ok. We'll find out soon enough - either the code will work or it won't

In terms of the code, I had intended that you should be able to use your existing code (with the loop and the logic identifying what is to be downloaded) which you've called Download_NMC_And_Copy, with this alternative replacement DownloadFile routine. But that's ok - I've reproduced the entirety of what I think you'll need (in theory) below. I changed the name of the DownloadFile routine to DownloadFileCOM because I noticed that the earlier code you were trying has declared the same named function to be public. This will just confuse VBA, so lets call this version DownloadFileCOM.

As to why you can't see it in the list of available macros, that list only shows subroutines that run without any additional programmatic input from the user - like your Download_NMC_And_Copy, for example. Whereas the DownloadFile (now, DownLoadFileCOM) requires two arguments - the source URL and the destination filepath. Hope that makes sense, but let me know if not.

In any event, the code below is (hopefully) selfcontained. I'd recommend putting it in its own module and see how it goes.

Sub Download_NMC_And_Copy()

    Dim DocumentURL         As String
    Dim DestinationFile     As String
    Dim FileName            As String
    Dim imax                As Integer
    Dim icount              As Integer
   
    imax = 178     ' In Column B, starting at Row 2, I have the 178 file names listed that I need to download
    icount = 2      ' Because it is Row 2

    Application.ScreenUpdating = True

    Do While icount <= imax
        FileName = Range("B" & icount)
        DocumentURL = "https://www.dco.uscg.mil/Portals/9/NMC/pdfs/examinations/" & FileName            '  "q100_ror_inland-international.pdf"
        DestinationFile = "C:/NMC/2022/" & FileName
        DownloadFileCOM DocumentURL, DestinationFile
        icount = icount + 1
    Loop

End Sub

Sub DownloadFileCOM(ByVal SourceURL As String, ByVal Destination As String)

    Dim HTTPS           As Object
    Dim TargetFile      As Object
    Set HTTPS = CreateObject("WinHttp.WinHttpRequest.5.1")
    On Error GoTo ErrHandler
   
    HTTPS.Open "GET", SourceURL, False
    HTTPS.send

    If HTTPS.Status = 200 Then
       Set TargetFile = CreateObject("ADODB.Stream")
       DoEvents
       With TargetFile
           .Type = 1 ' Early-binding constant = adTypeBinary
           .Open
           .Write HTTPS.ResponseBody
           .SavetoFile Destination
           .Close
       End With
       Set TargetFile = Nothing
     Else
        MsgBox "Unable to Download - Server Response Code " & HTTPS.Status
     End If
    
ErrHandler:
     If Err.Number <> 0 Then MsgBox "Error " & Err.Number & " - " & Err.Description
     Set HTTPS = Nothing
End Sub

DAN,

IT WORKS FROM HOME!
Now to figure out WHICH of my four versions I am using and see what the code is. I will post it after I KNOW exactly which one is working. I am in the midst of MOVING, packing up my house Saturday and Sunday and the movers show up Monday. So it might be a few days before I get back on the internet. Furniture goes into storage for 4-5 months until my house new house is finished and I head back to Maryland from Florida on Wednesday.

THANK YOU SO VERY MUCH for your help. Must have been the server at the meeting I was attending BLOCKING my downloads, but then again, I really started to have troubles before I left for the meeting. I need to research ALL of this and figure out what code is working.

Kindest regards,

rmplant

 

[Dan_W]

Glad to hear it.