VBA - Save a file from a Website

[giffordj]

I ran into a problem with the following code

Dim URL As String
URL = Worksheets("References & Resources").Range("URLMSL")
Dim IE As Object
Set IE = CreateObject("internetexplorer.application")
IE.Navigate URL
IE.Visible = True

My problem is that on most of the workstations here, it will open internet explorer, then open the file in Excel. I just want it to Save the file. I know there is probably another way to do this that will satisfy my need.

 

[arkusM]

Have not figured out this code but I got the way to my goal with another method. Found Here.

Colo,

This code seems to hang on me.
I am running it like this:
I have asked the orignial question here (crosspost disclosure)

Public Sub LaunchtheWebGasDay()
 
    Dim objIE As InternetExplorer
    Set objIE = New InternetExplorer 'Create Internet Explorer Object
 
    objIE.Navigate "http://www.transcanada.com/Customer_Express/tools/gdsr_transcanada.htm" 'Navigate the URL
 
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop 'Wait for page to load
            objIE.Document.forms(1).elements(0).Checked = True '"Alberta"
            'objIE.Document.forms(1).elements(1).Checked = True '"Metric"
            'objIE.Document.forms(1).elements(2).Checked = True '"Mainline"
            objIE.Document.forms(1).elements(3).Checked = True '"Imperial"
            objIE.Document.forms(1).elements(4).Value = "20091021"
            objIE.Document.forms(1).elements(6).Value = ".csv"
            objIE.Document.forms(1).elements(7).Click 'Click submit
 
                Do While objIE.ReadyState <> 4
                    DoEvents
                Loop
                SendKeys "%S"
            objIE.ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DODEFAULT    'SaveAs
 
Set objIE = Nothing
End Sub

 

[arsaboo]

I want to download some files of the internet, but the problem is that I do not know the filename until the file is downloaded. I can use URLDownloadToFile, but the problem is is that I have to specify the filename beforehand. The URL that I use to download the file is (the URL will not work for you as this is only accessible on campus, but you get the idea):

http://www.mergentonline.com.ezaccess.libraries.psu.edu/printedgar.asp?ID=20983&Type=RTF

Is there a way that I can capture the filename that is returned and then use it to save the file?

Appreciate any help..

 

[savindrasingh]

Is there a way that I can capture the filename that is returned and then use it to save the file?

Alteast, the file name you want to download is something you should know. But the output file/target file name to be saved to local drive could be anything you specify. However, you can rename it after you download it.

 

[arsaboo]

There are about 40000 files that I want to download and the filename itself has some characters that are random. If there is a simple way to just copy the URL (which I have in one of the cells) and create an IE object and navigate to that URL and then just save the file, that will be perfect

 

[savindrasingh]

If you know the logic behind how these filenames are generated then you can build the URL like this:

FileURL = "http://discovery.nbfn.com/Discovery/livelink/66475310/" & ThisWeekFile & ".zip?func=doc.Fetch&nodeid=66475310&viewType=1"

and use it in the code further:

DownloadFile FileURL, "C:\Temp\" & ThisWeekFile & ".zip"

Supposing you have this function available:

Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Public Function DownloadFile(ByVal URL As String, LocalFilename As String) As Boolean
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True
End Function

 

[arsaboo]

The problem is that there are some random characters in the name, so there is no clear logic behind the name

 

[ammage]

Ie.ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DODEFAULT 'SaveAs
</pre>Hello Colo-

When I execute this code, it generates a Compile error: Variable not defined for "OLECMDID_SAVEAS". If I remove "OLECMDID_SAVEAS" then it generates the same error for "OLECMDEXECOPT_DODEFAULT"

Where should these variables be defined?

Thanks in advance.

 

[savindrasingh]

You need to declare Constants at the begining of the code:

(the complete list of contants is available at: http://msdn.microsoft.com/en-us/library/ms691264(v=vs.85).aspx and http://msdn.microsoft.com/en-us/library/ms683930(v=vs.85).aspx)

Const OLECMDEXECOPT_DODEFAULT = 0
Const OLECMDID_SAVEAS = 4

 

[bapcki1]

OMG looking for this for aaaaaaages - GRAZZI :D!


Paddy

 

[reshmasingh2089]

I am using URLDownloadToFile API to download the excel file (with .xlsx extension).. macro download the file but I am not able to open it as excel says "excel cannot open the file as the file format and its extension it not valid". Kindly help to solve this issue as I am searching for this since yesterday but not getting correct result. Thanks in advance!