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..
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..