MaoMao

_ExcelWriteFormula seems a powerful function. It could be a good way to try. The worksheet now working at got different formulars for each row and got data different for each column. Used to do it manually to copy high light the column and then drag the formular to the rest of the columns. (or pastespecial special the formula to the rest of columns. It seems there is no lazy way to do it. Just need more Autoit program.

Thanks. May force to do it cell by cell use ExcelWriteFormula if PasteSpecial do not work. Trying to find easy way if there are code for PasteSpecial(Foumulars) $oExcel.Activesheet.PasteSpecial(?????) ; ????? (Foumulars) code not found. Anyone know if the $oExcel.Activesheet.PasteSpecial(Formulars) works or the code of PasteFormulars for PasteSpecial(?????)?

Hello, Searched the site for $oExcel.Activesheet.PasteSpecial. Only able to found example of paste copied content. Need to copy formular of a new Excel column. Please help. $oExcel.Sheets("ExcelBook1").Select $oExcel.Activesheet.Columns(3).copy $oExcel.Activesheet.Columns(4).Select $oExcel.Activesheet.PasteSpecial(Foumulars) ; ???? (Foumulars) not working Found Example: $oExcel.ActiveSheet.Cells(1,1).PasteSpecial(0xFFFFEFBD);Paste the copied content from the first Workbook into the second Workbook What is the code for $oExcel.PasteSpecial(?????)?

3. All necessary table info is able to store in html format file in seconds with the Inet function. Then use StringMid to retrieve the body part of html table file content quickly. Are there good method able to input html table file to array for processing. Example some of the few thousand recived file record attached for reference. <table border='0' cellspacing='0' cellpadding="2"> <tr> <th style="text-align:left">Count</th> <th style="text-align:left"><a href='/cus/cid.asp?part=243&d=1/19/2012&sort=nameup'><b>Issue</b></a></th> <th style="text-align:right"><a href='/cus/cid.asp?part=243&d=1/19/2012&sort=holddn'><b>Units</b></a></th> <th style="text-align:right"><a href='/cus/cid.asp?part=243&d=1/19/2012&sort=valnup'><b>Value</b></a></th> <th></th> <th style="text-align:right"><a href='/cus/cid.asp?part=243&d=1/19/2012&sort=stakdn'><b>Stake</b></a></th> <th style="text-align:right;width:100px"><a href='/cus/cid.asp?part=243&d=1/19/2012&sort=datedn'><b>Date</b></a></th> <th></th> </tr> <tr> <td>1</td> <td style="text-align:left"> <a href="csections.asp?issue=229&amp;d=2011-01-17">SECTION A:O</a> </td> <td style="text-align:right">9,86,000</td> <td style="text-align:right">168,418,040</td> <td></td> <td style="text-align:right">0.1967%</td> <td style="text-align:right">17-Jan-2011</td> <td style="text-align:right"><a href="cday.asp?issue=229&amp;part=243">history</a></td> </tr> <tr> <td>2</td> <td style="text-align:left"> <a href="csections.asp?issue=42&amp;d=2011-01-17">SECTION B:O</a> </td> <td style="text-align:right">1,168,752</td> <td style="text-align:right">108,386,042</td> <td></td> <td style="text-align:right">0.0265%</td> <td style="text-align:right">17-Jan-2011</td> <td style="text-align:right"><a href="cday.asp?issue=42&amp;part=243">history</a></td> </tr> </tr> <tr> <td>1428</td> <td style="text-align:left"> <a href="csections.asp?issue=5021&amp;d=2012-01-17">SECTION BZ:W</a> </td> <td style="text-align:right">0</td> <td style="text-align:right">0</td> <td>*</td> <td style="text-align:right">0.0000%</td> <td style="text-align:right">2-Jul-2011</td> <td style="text-align:right"><a href="cday.asp?issue=5021&amp;part=243">history</a></td> </tr> </table> </td> </tr> <tr> <td> <hr />

Thanks. It helps to find the root cause of the problem. Right. Something is stilling loading while all necessary table figures already displayed using Chrome/Firefox manually. 1. Disabled All the add-on for IE. Loading the page is a bit faster. But still not within a few second for acceptable range. 1.1 Tried _IECreate not waiting. IE got the same Autoit error message box. Line 1852 ...IE.au3...Case $i_index >-1 And $i_index < $o_object.document.GetElementsByTagNames("table").length...... Error: The requested action...... failed... It is able to show the top part of screen then need to load all the content before able to interface with the page. 1.2 Use the status of _IEPropertyGet, _IETableGetCollection or _IEGetObjByName or _IEDocReadHTML Study the possibility for bypass and speed up the page loading and process. P.S. Need some more programming work to be able to use _IEErrorHandlerRegister. 2. The extra items need long time to load is not used in the table content for analysis. Now looking at is it possible to ignore the background loading to continue the table to Array? use _IE or _FireFox? 3. Look at the possibility load the table to array use the content from InetRead, _INetGetSource. All necessary table info is able to store in html format file in seconds with the Inet function. P.S. Able use StringMid to retrieve the body part of html table quickly. Will be good if there are function able input html table file to array for processing. 4. Use FireFox UDF if FF.au3 give more flexibility and performance for web page and array handling. Testing FF.au3 (V0.6.0.0b). Ver. Jun 2009!? Not sure if that's the latest update. FFStart the table web page still very slow. Seems some of the function didn't work with the new FireFox3+. i.e. FFQuit, FFConnect(); FFTableWriteToArray look simple but the example didn't work. Not sure if worth to spend more time on it. Lots of work in those different directions. Need to find out which one is better to focus. To be continue........work to break up this question to the direction of error handling or html table file.

P.S. Checking in progress: Thank for the help in the mean time. New to this. Pardon me for the patient. ;Working on statistic on web format info most of them in table style (or partially tabled but the layout may not be propertly written to display smoothly and quick). Testing different ways to try solve the problem quickly. (Use Post1 for Array or Post2 for Excel solution question. Both got difficulities) _IECreate($Url) ; working with Array but slow. Page and usable table content was displayed then it seems loading something in background for another >30Sec. _IECreate($Url,0,0,1); not waiting cause _IECreate program breaking error. Something may missing if not waiting for all the info passing back. Use another X86 old PC(New WinXP n IE) took >3 minutes to load whole page. Same old PC use Chrome/Firefox took <10 seconds only. Thanks Dale. It seems _IETableGetCollection works with _IE function only. Now looking for more info to understand this. Searching if there are other faster way pass the page info to _IETableWriteToArray ($oTable). If not successful for Array. That will force back to use Excel for analysis process. Are there Chrome/FireFox function by the way?

Trying to find faster way to write a web page table to Array. The _IETableGetCollection ($oIE, 0) is not getting any Table data with InetRead($Url) and _INetGetSource($Url, 'True') Help please? Sample code: $oIE = InetRead($Url) ; not getting the table ;$oIE = _IECreate($Url) ; slow ;$oIE = _INetGetSource($Url, 'True') ; not working ;MsgBox(0, "oIE Info", $oIE) $oTable = _IETableGetCollection ($oIE) ; Get number of tables $iNumTables = @extended ;MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page") $oTable = _IETableGetCollection ($oIE, 0) ; Get last table $aTableData = _IETableWriteToArray ($oTable) _ArrayDisplay($aTableData)

Thanks. _ExcelWriteFormula works with the R1C1 method! One step ahead! now. Still finding way to do ExcelBookOpen the WebPage.HTML file, to ignore error of missing main.css and calender.css. Are there work around to ignore the error message?

Hi JLogan3o13, Thanks for the help. IE visible is for debug only. Not helping the performance when set to invisible. The site is one testing example. There are other sites with over 1000 record slows down the IECreate performance. (>30 seconds) For repeating loop. It is not acceptable. I'm not sure why Other large table sites opened much faster in FireFox i.e. 12 seconds in the same computer. The InetRead($Url)) and _INetGetSource($Url) is fast to load the html content but not getting the table to Array. Need advice for using the InetRead and INetGetSource html content file load to array for processing. Trying load html to Excel for processing but this alternative got other problem.

_ExcelWriteFormula question When writing formula to Excel: ExcelWriteFormula($oExcelfile, '=SUM(A80:A1800)', 1801, 5) The Excel table got error in the cell [ #NAME? ] Details written in the cell become [ =SUM('A80':'A1800') ] There are four more ( ' )s written in the formular. What's the solution please help?

Trying to speed up reading Web page use InetRead($Url) and INetGetSource($Url). When ExcelBookOpen the WebPage.HTML file, got error of missing main.css and calender.css. Are there work around to ignore the error message? ;$oIE = BinaryToString(InetRead($Url)) ; Ex2 not getting the table $oIE = _INetGetSource($Url, 'True') ; Ex3 not getting the table ;FileWrite( @WorkingDir & "\"& "WebPage.HTML", _IEBodyReadText ($oIE)) FileWrite( @WorkingDir & "\"& "WebPage.HTML", _IEBodyReadText ($oIE)) $oExcelfile = _ExcelBookOpen(@WorkingDir&"\WebPage.HTML")

The _IECreate is very slow. Not sure why when open with IE for the url. But FireFox open very fast on the computer. Tried alternative (InetRead, INetGetSource) but not working. No table is written to array. Please help. #include <IE.au3> #include <Array.au3> #include <Inet.au3> Local $Url = 'http://www.bts.gov/publications/national_transportation_statistics/html/table_01_37.html' ;$oIE = _IECreate($Url, 0, 0) ; Ex1 slow ;$oIE = BinaryToString(InetRead($Url)) ; Ex2 not getting the table $oIE = _INetGetSource($Url, 'True') ; Ex3 not getting the table ConsoleWrite($oIE) MsgBox(0, "oIE Info", $oIE) $oTable = _IETableGetCollection ($oIE) ; Get number of tables $iNumTables = @extended MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page") $oTable = _IETableGetCollection ($oIE, 0) ; Get table $aTableData = _IETableWriteToArray ($oTable) _ArrayDisplay($aTableData) $oTable = _IETableGetCollection ($oIE, 1) ; Get next table $aTableData = _IETableWriteToArray ($oTable) _ArrayDisplay($aTableData)

Good. Sum is calcualted. The _IECreate is very slow. Not sure why open with IE for the url. But FireFox open very fast on the computer. Tried alternative but not working. ;$oIE = _IECreate($Url, 0, 0) ; Working but very slow ;$oIE = InetRead($Url) ; not getting the table?? ;$oIE = _INetGetSource($Url) ; fast but not getting the table $oIE = _INetGetSource($Url, 'True') ; fast but not getting the table Any advice?

Working with array may faster. But got problem in calculation. Got the Web value in Array : 108,326,000 95,585,808 90,883,640 90,880,000 86,986,500 Sum is only calculating the first few digits before the "," ( comma ) For $i= 1 to 10;UBound($aArray2) -1 $Sum = $aArray2[$i][3]+ $Sum Next MsgBox(0, "Table Info", "There are Sub Total " & $Sum & " sum on the page") Result = 108 + 95 + 90 +90 +86 = 469 Are there function to correct this?

An early post by Water seem solve the problem. _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) and array display seems have difficulity writing cell with large columns. $aArray2=$oExcel.transpose($aArray) _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) and array display fine. Now trying to find way to calculate the sum of a column.