VBA code brings up dialog box with open or save options but I am stuck there.

[heavican03]

First post here so be nice to me. I am trying to download an excel file from a website. The website has a button which can be pressed to download the excel file. I looked in the code and was able to come up with a url that if entered into IE takes me directly to the download open/save dialog box. I then used that url in vba in excel that works to open up the download box where it is asking to open or save the file.

I would like to save the file to the same name everytime as I am going to have another workbook that looks at the data and returns results.

I know this question has been asked a million times because I have searched and searched but no code I have tried will work.

This is my code so far

Sub TESTING()
'Need to reference to Microsoft Internet Controls
Dim URL As String
'URL = Worksheets("References & Resources").Range("URLMSL")
URL = "http://cts/Tacs/OperationDrillExport/76"
Dim IE As Object
Set IE = CreateObject("internetexplorer.application")
IE.Visible = True
IE.Navigate URL
Do While IE.ReadyState <> 4
DoEvents
Loop

End Sub

The website is an internal website so it will not work from the outside but I would think the code is the same either way.

 

[heavican03]

So I continue to mess with this and now I am so so close. I totally went a different direction and I am able to now save the file like I want. The problem is it will only work with the MsgBox command at the end of the code and I don't want a message box displayed because I don't want the user to have to click ok. If I delete this code it won't save the file though.

DeclareFunction URLDownloadToFile Lib"urlmon"Alias"URLDownloadToFileA" _
(ByVal pCaller AsLong,ByVal szURL AsString,ByVal szFileName AsString, _ByVal dwReserved AsLong,ByVal lpfnCB AsLong)AsLong

Sub Example()
DownloadFile$="file.xlsx"
URL$="http://cts/Tacs/OperationDrillexport/76234"
LocalFilename$="C:\Users\q6bxh0\Documents\downloads\"& DownloadFile

MsgBox URLDownloadToFile(0, URL, LocalFilename,0,0)=0


EndSub

It is so frustrating. The file saves before I even click ok so it is just something about the command that is allowing it to save that when removed doesn't. I am so new and it is probably so simple but without that MsgBox command it simply will not save. Probably just need a different command but I can't figure it out.

So right now it works but I just need to figure out either how to automatically close the MsgBox which by what I read you can't do or I need a different command to do what every is happening when I run the MsgBox.

 

[Chihiro]

Try replacing MsgBox portion with something like below.

If URLDownLoadToFile(0, URL, LocalFilename,0,0) = 0 Then
    DownloadFile = True
End If

 

[heavican03]

This Works Perfect!!!!!!! Thanks so so much. I am on my way to becoming a copy and paste VBA expert.