Download zipped CSV from Website and unzip (website has log in)

[johnmpc]

Hi,

Please can someone help me. I am trying to download a zip file from a website using VBA. The website requires a login. I have the login saved in internet explorer or Chrome.

At the moment the macro runs but internet explorer prompts to save or open.

Is there a better way to do this? I've seen URLDownloadtofile but didn't think this worked when requiring a login.
And then some help UNzipping this would be great.
Thanks

Sub Web_Scraping()

   Dim Internet_Explorer As InternetExplorer
   Dim saveInFolder As String, saveAsFilename As String
  Set Internet_Explorer = New InternetExplorer
  Internet_Explorer.Visible = False
  
Internet_Explorer.Navigate ("https://www.pencarrie.com/export/products.zip")


End Sub

 

[johnmpc]

Solved

 

[Fluff]

Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[johnmpc]

Heres the code i used.

Sub DownloadFileFromURL()

     Dim FileUrl As String
     Dim objXmlHttpReq As Object
     Dim objStream As Object

     FileUrl = "https://www.pencarrie.com/export/products.zip"

  Kill "C:\Users\JohnGlanville\OneDrive - MPC Embroidery\Desktop\Customers\Pencarrie Price\*.*"

     Set objXmlHttpReq = CreateObject("Microsoft.XMLHTTP")
     objXmlHttpReq.Open "GET", FileUrl, False, "USERNAME", "PASSWORD"
     objXmlHttpReq.send

     If objXmlHttpReq.Status = 200 Then
          Set objStream = CreateObject("ADODB.Stream")
          objStream.Open
          objStream.Type = 1
          objStream.Write objXmlHttpReq.responseBody
          objStream.SaveToFile "C:\Users\JohnGlanville\OneDrive - MPC Embroidery\Desktop\Customers\Pencarrie Price" & "\" & "PencarriePrice.zip", 2
          objStream.Close
     End If
 Dim File        As Object
    Dim Files       As Object
    Dim MainFldr    As Object
    Dim MainPath    As Variant
    Dim oShell      As Object
    Dim ZipFile     As Variant
    Dim ZipFldr     As Object
    
        MainPath = "C:\Users\JohnGlanville\OneDrive - MPC Embroidery\Desktop\Customers\Pencarrie Price"
        
        Set oShell = CreateObject("Shell.Application")
            
        Set MainFldr = oShell.Namespace(MainPath)
        
            Set Files = MainFldr.Items
                Files.Filter 32, "*.zip"
                
            For Each File In Files
                Set ZipFldr = oShell.Namespace(File)
                For Each ZipFile In ZipFldr.Items
                    MainFldr.CopyHere ZipFile.Path
                Next ZipFile
            Next File
End Sub

 

[gk0508771]

Hi johnmpc,
How can i get url from excel cell to our VBA Code and assign value to (FileUrl) in above code

 

[Joe4]

Hi johnmpc,
How can i get url from excel cell to our VBA Code and assign value to (FileUrl) in above code

Welcome to the Board!

If your URL was in cell A1, simply replace this line:

    FileUrl = "https://www.pencarrie.com/export/products.zip"

with this

    FileUrl = Range("A1")