HELP!! Download data to workbook

[farid2001]

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$txtServiceAccount").Value = strServAcct
objIE.document.all("ctl00$pageBody$btnSearch").Click
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

 

[meh2030]

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$txtServiceAccount").Value = strServAcct
objIE.document.all("ctl00$pageBody$btnSearch").Click
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(exportto
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 procedure:

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

 

[farid2001]

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$ctl00").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

 

[meh2030]

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$ctl00").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












- Show quoted text -

Farid,

I noticed that while I was searching for solutions that you have had
another active post. Let me know if you need me to still post back to
this thread or not. (I still haven't found anything for downloading
the file after clicking the Excel icon).

Best,

Matt

 

[farid2001]

Hello Matthew

This is the final code I have:

Private Sub Workbook_Open()
Sheets.Add

Net2Phone
End Sub

Sub Net2Phone()

Dim objIE As Object
Dim strServAcct As String
Dim Password As Object

strServAcct = "8570450851"

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
objIE.Navigate "https://partner.net2phone.com/apps/account/search.aspx"
WaitForLoad objIE

Set UserId = objIE.document.getElementById("txtUserID")
If Not UserId Is Nothing Then
'Input user name and password
UserId.Value = "famaperu45"
Set Password = objIE.document.getElementById("txtPassword")
If Not Password Is Nothing Then
Password.Value = "my69car"

'submit the form by clicking "Login"
Set Login = objIE.document.getElementById("btnlogin")
Login.Click

WaitForLoad objIE
End If
End If
Set AccountBox = _
objIE.document.getElementById("ctl00$pageBody$txtServiceAccount")
AccountBox.Value = strServAcct

Set SearchButton = _
objIE.document.getElementById("ctl00$pageBody$btnSearch")
SearchButton.Click

WaitForLoad objIE

objIE.Navigate "https://partner.net2phone.com/apps/account/calls.aspx"
WaitForLoad objIE
RowCount = 1
'format the phone number as text
'long phone number become scientific notsation 1.3E+14
'which don't look like phone numbers
Columns("C").NumberFormat = "@"
Columns("B").NumberFormat = "dd/mm hh:mm:ss"
RowCount = 1
'Done indicats we have no more pages in the call history
Done = False
With Sheets("Hoja1")
Do While Done = False
' wait for History page to load
'or Next page when multiple pages
WaitForLoad objIE

'the call history table start with class name equal gridview
State = "Find_GridView"
'get all items in html data
For Each itm In objIE.document.all

Select Case State

'skip everything until GRIDVIEW is found
Case "Find_GridView":
'when gridview is found output header row
If itm.classname = "gridview" Then
'set state to grid view to process
'call histor table
State = "Found_GridView"
'only put header row for 1st page
If RowCount = 1 Then
ColCount = 1
'put header row on worksheet
For Each Cell In itm.Cells
.Cells(RowCount, ColCount) = Cell.innertext
ColCount = ColCount + 1
Next Cell
RowCount = RowCount + 1
End If
End If
Case "Found_GridView":
'Input tag is the table on spreadsheet
'moving to next and previous page
If itm.tagname = "INPUT" Then
src = UCase(itm.src)
'we are looking for item NEXT_PG_1
'to move to next page.
'not sure when there are more than two pages
'what the src is going to look like
'so I'm going to move to next page
'except under the case NEXT_PG_0
If InStr(src, "NEXT") > 0 Then
If InStr(src, "NEXT_PG_0") = 0 Then
'found next page
'need to execute the command
'so I put the command in OnClick
'Then execute on click
itm.Onclick = src
'gop to next page
itm.Click
Exit For
End If
End If
End If

'If we don't find next page then we are at
'last page and the end of the gridview table
'is indicated by the ! - stop processing
If itm.tagname = "!" Then
'reached last page - stop
Done = True
Exit For
End If
'each item in call history has the tag TR
If itm.tagname = "TR" Then
'the end of the call history is a blank innertext
'we don't w\ant to put blank data into worksheet
'we can't end because we still need to check for
'a next page
If itm.innertext <> "" Then
ColCount = 1
For Each Cell In itm.Cells
.Cells(RowCount, ColCount) = Cell.innertext
ColCount = ColCount + 1
Next Cell
RowCount = RowCount + 1
End If
End If
End Select
Next itm
Loop
End With

objIE.Application.Quit

Call Macro_Fama_Per_Ru_e

Call Macro_pegado_valores

Application.ActiveWorkbook.Save
End Sub

Sub WaitForLoad(IE As Object)

Do While IE.Busy = True Or IE.readystate <> 4
DoEvents
Loop
End Sub

This works perfect for up to 400 or 500 rows of data, for larger call
history records, it would be much better to have the Export to Excel file,
since this would be much faster to download.
The problem is to go around the 2 messages that appear when the excel file
is ready to download into the computer.

I would very much appreciate if you can find a solution for that.

Thanks a million for your effort and time to help me.

Regards
farid2001