• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA help to save a download file to a specific location

riyaj

New Member
Hi Guys,


Please help. I am new to VBA macro's and want to prepare a macro which will


1. take a path from excel cell ,

2.open internet explorer , (please note here, I have hypelinks path which will download as I navigate to asked path),

3.and it will save downloaded file to specific folder path mentioned in excel cell.


Fortunately I have now a macro which can do first 2 steps but I am struggling to work for 3rd step. Could you please help me ?


Here's VBA macro I have for your reference:


---------------------------------------------------------------------------------

Const MyUrl As String = "http://orbitobi.nat.bt.com/analytics/saw.dll?Download&Format=excel&Path=/users/XXXXX/AHT%20data"

Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")

ie.Visible = True

ie.navigate "http://orbitobi.nat.bt.com/analytics/saw.dll? ownload&Format=excel&Path=/users/XXXXX/AHT%20data"


' Here Internet asks for location to save file or open a file. I need to choose save and location here


On Error Resume Next

While ie.busy

DoEvents

Wend

Set lform = ie.document.forms(0)

With lform

Set txtPassowrd = ActiveSheet.Shapes("TextBox2").OLEFormat.Object

Set txtEIN = ActiveSheet.Shapes("Textbox1").OLEFormat.Object

On Error Resume Next

lform.Item("user").Value = txtEIN.Object.Value

lform.Item("PASSWORD").Value = txtPassowrd.Object.Value

'Application.Wait Now + TimeValue("00:00:04")

lform.Submit

Application.Wait Now + TimeValue("00:00:04")


ie.document.all.Item("YES").Value = "Yes"

ie.document.all.Item("YES").FireEvent ("onClick")

'Application.Wait Now + TimeValue("00:00:04")

lform.Yes

Application.Wait Now + TimeValue("00:00:04")

End With


End Sub

----------------------------------------------------------------------------


Please let me know if you have any more questions to clarify..
 

Marc L

Excel Ninja
Hi,


maybe with an API function :

[pre]
Code:
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$ = "here the name with extension"
URL$ = "http://here the download website address/" & DownloadFile
LocalFilename$ = "here the drive and download directory" & DownloadFile
MsgBox "Download Status : " & URLDownloadToFile(0, URL, LocalFilename, 0, 0) = 0
End Sub
[/pre]
 

riyaj

New Member
Hi Marc,


Your code and solution is brilliant. Many thanks for your prompt responce !


Was little worried if you could help me here, as when I added Website address , path and name of sheet to download, it gives me error as RUN TIME ERROR 13 - TYPE MISMATCH


I googled about it, but no success yet. Showing you macro which I added into excel sheet --


--------------------------------------------------------------------


Sub Download()

DownloadFile$ = "AHT.xls"

URL$ = "http://orbitobi.nat.bt.com/analytics/saw.dll?Download&Format=excel&Path=/users/606823201/AHT%20data" & DownloadFile

LocalFilename$ = "C:GenesysAutomation" & DownloadFile

MsgBox "Download Status : " & URLDownloadToFile(0, URL, LocalFilename, 0, 0) = 0


End Sub


--------------------------------------------------------------------------


Please note , when I click debug it highlights line - MsgBox "Download Status : " & URLDownloadToFile(0, URL, LocalFilename, 0, 0) = 0


Could you please assist ?
 

Marc L

Excel Ninja
URL$ = "http://here the download website address/" & DownloadFile

LocalFilename$ = "here the drive and download directory"
  

Check yours ‼   You didn't pay attention to the last slash or backslash …

 
 

riyaj

New Member
Thank you Mark ,must be missing the same thing.


I am not on my desk at my desk so Apologies I could not find if your solution is right, but I am sure you are pointing at correct thing :)


The website that I was trying to download data needed a Login ,password for obvious reasons. As our code does not need IE to start , I could not add login password here.Is is possible to add a code which will add them?
 

Marc L

Excel Ninja
The login need is the point you should start your subject with …

And in this case I hadn't post an answer …


So you have to pilot IE and it's not easy for a VBA newbie !

You must do a research and learn how to pilot IE …
 

SirJB7

Excel Rōnin
Hi, riyaj!


Give a look to these links:

http://chandoo.org/forums/topic/bring-to-front-macro

http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/


The first is a simple IE object pilot example, the last one deals with UserID & Password.


Regards!


PS: But as Marc L correctly stated, for newbies to VBA it'd be either funny or hardly painful, depending on your sense of humor.
 

dharmendrashah

New Member
dear riyaj,
if you succeed to create above excel sheet... please share... i am also want to do same...
i am using hyperlink to download data from excel sheet...but it saves on default folder or specify in browser setting...

thanks
 

heavican03

New Member
Hi,


maybe with an API function :

[pre]
Code:
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$ = "here the name with extension"
URL$ = "http://here the download website address/" & DownloadFile
LocalFilename$ = "here the drive and download directory" & DownloadFile
MsgBox "Download Status : " & URLDownloadToFile(0, URL, LocalFilename, 0, 0) = 0
End Sub
[/pre]
This code works absolutely perfect for me once I deleted the "Download Status" part. Now the problem is though I don't want the msgbox because I just want to run it and not have a user do anything. If I remove the MsgBox though the code no longer works. I don't understand code and I can't figure out why it is the case but with the MsgBox command it works perfect without it, it doesn't save the file. Is there another command I could use.
 
Top