Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!! Download data to workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!! Download data to workbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!! Download data to workbook
On May 9, 7:05*pm, farid2001
wrote: 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- Hide quoted text - - 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!! Download data to workbook
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$txtS erviceAccount") AccountBox.Value = strServAcct Set SearchButton = _ objIE.document.getElementById("ctl00$pageBody$btnS earch") 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 " wrote: On May 9, 7:05 pm, farid2001 wrote: 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email (LDAP) data download into a single Excel cell - data separat | Excel Worksheet Functions | |||
items chosen to download are NEVER all there after download comple | New Users to Excel | |||
how to download task data | Excel Programming | |||
Data Download | New Users to Excel | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming |