CODEWITHSUNDEEP
excelvbainternet-explorerdownload
heavican03
heavican03

Reputation: 1

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

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.

Upvotes: 0

Views: 1658

Answers (2)

heavican03
heavican03

Reputation: 1

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. 

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

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


End Sub

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.

Upvotes: 0

Josh Fierro
Josh Fierro

Reputation: 140

Sorry, my rep isn't high enough to leave a comment, so this isn't an answer; it's a question/suggestion... I wanted to ask if you are trying to get the program to download the file automatically, or if by "stuck" you are saying that the dialog box that comes up with the open/save buttons doesn't allow you to proceed; i.e., you can't actually click the open/save buttons? The reason I ask is that I imagine it would be more efficient if the url is always the same to just download the file through vba without using the dialog box at all.

You might find some answers here: How do i download a file using VBA (Without internet explorer)

Upvotes: 1

Related Questions