HELP!! Download data to workbook
Matthew
Thank you very much for your help.
I runned using this code at the end and worked well for downloading excel
file, the problem, as you mentioned, is how to get that file to open via code.
I have code that handles the downloaded openned file and loads it into the
worbook, but before that there are 2 instances that ask if the file should be
accepted before it opens.
objIE.document.all("ctl00$pageBody$gvCall$ctl27$ct l00").Click
I tried the other code and besides giving ByRef error messages, at the end
the path appeared on the web page which was empty.
Hoping to hear from you.
Regards
farid2001
" wrote:
On May 9, 1:08 am, farid2001
wrote:
Dear Gentlemen, I really need some help to download data into workbook.
I have this code:
Private Sub Workbook_Open()
History
End Sub
Sub History()
Dim objIE As Object
Dim strServAcct As String
strServAcct = "3484690293"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
objIE.Navigate "https://partner.net2phone.com/apps/common/login.aspx"
WaitForLoad objIE
objIE.document.all("txtUserID").Value = "famaperu45"
objIE.document.all("txtPassword").Value = "my69car"
objIE.document.all("btnlogin").Click
WaitForLoad objIE
objIE.document.all("ctl00$pageBody$txtServiceAccou nt").Value = strServAcct
objIE.document.all("ctl00$pageBody$btnSearch").Cli ck
WaitForLoad objIE
'Page that has data I need to download
objIE.Navigate "https://partner.net2phone.com/apps/account/calls.aspx"
End Sub
Sub WaitForLoad(IE As Object)
Application.Wait (Now + TimeValue("0:00:05"))
Do While IE.Busy And Not IE.ReadyState = 4
DoEvents
Loop
End Sub
It works perfectly, but I need code either to click the excel icon(export to
excel) or to download the information into the workbook, either will work
fine for me.
Please, help!!
Thanks
farid2001
Farid2001,
The code below will require you to add some additional procedures to
clean up the data, so I'll see if I can figure out a separate way to
get the file to download. (If you allow pop-ups from the site, then
you can click the file via objIE.document.all("ctl00$pageBody$gvCall
$ctl27$ctl00").Click, but then the issue becomes getting the file from
that point in time. I've never done anything specifically like this
before, so I'll try to do some research on this.)
Best,
Matthew Herbert
Add the following code to your procedu
strTempPath = ThisWorkbook.Path & "/temp.txt"
SaveTextToFile objIE.document.body.innerText, strTempPath
Workbooks.OpenText Filename:=strTempPath, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True
Add the following procedure to your module:
Sub SaveTextToFile(strContent As String, strPath As String)
Dim objFSO As Object
Set objFSO = CreateObject("scripting.filesystemobject")
With objFSO.CreateTextFile(strPath, True)
.Write strContent
.Close
End With
End Sub
|