RickB75 Posted September 19, 2013 Share Posted September 19, 2013 Guys, I know this is a simple fix but I've been trying to figure it out for about 2 hrs now and I can't find the answer. I'm writing numbers to excel with my script and I'm trying to remove the comma from a price (26,595.00) to (26595.00). It seems pretty simple. I grabbed a function from here in the forums ( _StringStripChr )and now it's removing the comma, but the script won't write the ".00". I've played around with the arguments at the end and I can't get it to work correctly. My question is do any of you guys have a quick solution to basically convert a column to a number in excel? Something similar like the way you can autofit columns. Basiclly the same result as if you click the drop down in excel and select the number icon. $oExcel.Columns.AutoFit Link to comment Share on other sites More sharing options...
water Posted September 19, 2013 Share Posted September 19, 2013 The Excel UDF has function _ExcelNumberFormat to allow you to format numbers. The content of the cells isn't changed, just the way the value is displayed. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RickB75 Posted September 19, 2013 Author Share Posted September 19, 2013 (edited) Thanks for your reply Water. You've helped me out numerous times. It's greatly appreciated. I tried that before I tried the _StringStripChr function. I'll give it another shot real quick and let you know what the results are. I must be setting the format incorrectly. Edited September 19, 2013 by RickB75 Link to comment Share on other sites More sharing options...
water Posted September 19, 2013 Share Posted September 19, 2013 Use the format you see in Excel when formatting the column for numeric data. Something like "#0.00" My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RickB75 Posted September 19, 2013 Author Share Posted September 19, 2013 Water, I've tried. Here's what I get back. The cell will display "6,900.00". When I click on the cell, the bar above the sheet show's "6900". I'm saving the spreadsheet as a ".txt" doc and it's saving as "6,900.00". I've got to get rid of the comma. When I upload my spreadsheet the server is kicking the pricing because of the comma. Here's the entire script below. I'm using Excel 2007. Whats weird is when I select the column and format the column for a number in Excel, it works perfect. The way it is currently, I can't get the script to duplicate it. If you run the script, don't wait for the entire script to run. You'll know by row 10 or 11 if it's working. expandcollapse popup#include <array.au3> #include <IE.au3> #include <Excel.au3> ConsoleWrite('!--------- STARTING ----------' & @CRLF) $icounter = 2 ;skip to the 2nd row in excel Local $oExcel = _ExcelBookNew(1) Local $header[6] = ["id ", "Title", "link", "image link", "Price", "Condition"];headers on excel spreadsheet _ExcelWriteArray($oExcel, 1, 1, $header, 0);wirtes headers into excel Local $oIE = _IECreate("http://www.beamantoyota.com/used-inventory/index.htm",0,0) While 1 Local $sHTML = _IEDocReadHTML($oIE) Local $oListItems = _IETagNameGetCollection($oIE, "LI") For $oListItem In $oListItems Local $odivs = _IETagNameGetCollection($oListItem, 'div') For $odiv In $odivs Local $dataVin = $odiv.getAttribute("data-vin") If $dataVin <> '' Then ;ConsoleWrite('+ Found it, data-vin: ' & $dataVin & @CRLF) _ExcelWriteCell($oExcel, "VIN: " & $dataVin, $icounter, 1) Local $oAnchors = _IETagNameGetCollection($odiv, 'a') For $oAnchor In $oAnchors Local $sHref = $oAnchor.href ;ConsoleWrite("> Link Info: " & $sHref & @CRLF) _ExcelWriteCell($oExcel, $sHref, $icounter, 3) ExitLoop Next Local $imgs = _IETagNameGetCollection($odiv, 'img') For $img In $imgs Local $pic = $img.src ;ConsoleWrite("> Image Info: " & $pic & @CRLF) _ExcelWriteCell($oExcel, $pic, $icounter, 4) ExitLoop Next Local $titles = _IETagNameGetCollection($oListItem, 'a') For $title In $titles Local $datatitle = $title.getAttribute("data-title") If $datatitle <> '' Then ;ConsoleWrite('> Found it, data-title: ' & $datatitle & @CRLF) _ExcelWriteCell($oExcel, $datatitle, $icounter, 2) ExitLoop EndIf Next If $datatitle = '' Then Local $htitles = _IETagNameGetCollection($oListItem, 'h1') For $htitle In $htitles ;ConsoleWrite('> Found it, Web-title: ' & $htitle.innerText & @CRLF) _ExcelWriteCell($oExcel, $htitle.innerText, $icounter, 2) ExitLoop Next EndIf Local $oSPANs = _IETagNameGetCollection($oListItem, "SPAN") ; Find all SPAN tags For $oSPAN In $oSPANs If StringInStr($oSPAN.className, "msrp final-price") Then If StringInStr($oSPAN.innertext,"$") Then $price = StringSplit($oSPAN.innertext,"$") ;$newstring = _StringStripChr($price[2], "," , 8, 0) _ExcelWriteCell($oExcel, $price[2] & ".00", $icounter, 5) Else ;ConsoleWrite('> Found it, MSRP: ' & $oSPAN.innertext& @CRLF) _ExcelWriteCell($oExcel, $oSPAN.innertext, $icounter, 5) Endif ElseIf StringInStr($oSPAN.className, "internetPrice final-price")Then If StringInStr($oSPAN.innertext,"$") Then $price = StringSplit($oSPAN.innertext,"$") ;$newstring = _StringStripChr($price[2], ",",8,0) _ExcelWriteCell($oExcel, $price[2] & ".00", $icounter, 5) Else ;MsgBox(0,"price",$price[2]) ;ConsoleWrite('> Found it, Internet Price: ' & $oSPAN.innertext& @CRLF) _ExcelWriteCell($oExcel, $oSPAN.innertext, $icounter, 5) EndIf ElseIf StringInStr($oSPAN.className, "retailValue final-price")Then If StringInStr($oSPAN.innertext,"$") Then $price = StringSplit($oSPAN.innertext,"$") ;$newstring = _StringStripChr($price[2], ",",8,0) _ExcelWriteCell($oExcel, $price[2] & ".00", $icounter, 5) Else ;MsgBox(0,"price",$price[2]) ;ConsoleWrite('> Found it, Internet Price: ' & $oSPAN.innertext & @CRLF) _ExcelWriteCell($oExcel, $oSPAN.innertext, $icounter, 5) EndIf ElseIf StringInStr($oSPAN.className, "askingPrice final-price")Then If StringInStr($oSPAN.innertext,"$") Then $price = StringSplit($oSPAN.innertext,"$") ;$newstring = _StringStripChr($price[2], ",",8,0) _ExcelWriteCell($oExcel, $price[2] & ".00", $icounter, 5) Else ;MsgBox(0,"price",$price[2]) ;ConsoleWrite('> Found it, Internet Price: ' & $oSPAN.innertext & @CRLF) _ExcelWriteCell($oExcel, $oSPAN.innertext, $icounter, 5) EndIf EndIf Next Local $format = "#0.00" _ExcelNumberFormat($oExcel,$format,$icounter,5) _ExcelWriteCell($oExcel,"Used", $icounter, 6) $icounter = $icounter + 1 EndIf Next Next $aPage = StringRegExp($sHTML, "Page (\d*) of (\d*)", 1) ;_ArrayDisplay($aPage) If $aPage[0] = $aPage[1] Then ExitLoop Local $oLinks = _IELinkGetCollection($oIE) $sMyString = "Next" For $oLink In $oLinks Local $sLinkText = _IEPropertyGet($oLink, "innerText") If StringInStr($sLinkText, $sMyString) Then _IENavigate($oIE, $oLink.href) ;_IEAction($oLink, "click") ExitLoop EndIf Next WEnd ConsoleWrite('!--------- ENDING ----------' & @CRLF) _IEQuit($oIE) ;======================================================================================================== ; ; Function Name: _StringStripChr($sString_In, $sChr, $iFlags = 2, $iCount = 0) ; Description: ; Parameters: ; $sString_In - The string to be stripped ; $sChr - The characters to be stripped (case sensitive) ; $iFlags - Flag to indicate the type of stripping that should be performed (add the flags together for multiple operations): ; 1 = strip leading instances of characters in $sChr ; 2 (Default) = strip trailing instances of characters in $sChr ; 4 = Replace multiple instances of characters in $sChr with a single instance ; 8 = strip all instances of $sChr (over-rides all other flags) ; $iCount - The max number of leading or trailing instances of $sChr to strip ; 0 (Default) = Strip all ; ; Requirement: None. ; Return Value: Stripped string ; Author: Bowmore ; ; Notes: ; ; Examples: ; _StringStripChr("AAAAbdcaefgA", "A", 3, 0) ; would return ; "bdcaefg" ; ; _StringStripChr("AAAAbdcaefgA", "A", 2, 0) ; would return ; "AAbdcaefg" ; ; _StringStripChr("ABCAAbdcaefgA", "AB", 7, 0) ; would return ; "CAbdcaefg" ; ; _StringStripChr("ABCAAbdcaefgA", "ABe", 8, 0) ; would return ; "Cbdcafg" ; ;======================================================================================================== Func _StringStripChr($sString_In, $sChr, $iFlags = 2, $iCount = 0) Local $sNewString = $sString_In Local $sChr1 = "" If (BitAND($iFlags, 8) = 8) Then For $i = 1 To StringLen($sChr) $sChr1 = StringMid($sChr, $i, 1) $sNewString = StringReplace($sNewString, $sChr1, "", 0, 1) Next Else If (BitAND($iFlags, 4) = 4) Then For $i = 1 To StringLen($sChr) $sChr1 = StringMid($sChr, $i, 1) $sNewString = StringRegExpReplace($sNewString, $sChr1 & "{2,}", $sChr1) Next EndIf If (BitAND($iFlags, 2) = 2) Then If $iCount = 0 Then While (StringInStr($sChr, StringRight($sNewString, 1), 1)) $sNewString = StringTrimRight($sNewString, 1) WEnd Else For $i = 1 To $iCount If (StringInStr($sChr, StringRight($sNewString, 1), 1)) Then $sNewString = StringTrimRight($sNewString, 1) Else ExitLoop EndIf Next EndIf EndIf If (BitAND($iFlags, 1) = 1) Then If $iCount = 0 Then While (StringInStr($sChr, StringLeft($sNewString, 1), 1)) $sNewString = StringTrimLeft($sNewString, 1) WEnd Else For $i = 1 To $iCount If (StringInStr($sChr, StringLeft($sNewString, 1), 1)) Then $sNewString = StringTrimLeft($sNewString, 1) Else ExitLoop EndIf Next EndIf EndIf EndIf Return $sNewString EndFunc ;==>_StringStripChr Link to comment Share on other sites More sharing options...
water Posted September 20, 2013 Share Posted September 20, 2013 This example code formats the numbers in column A with 2 decimals. #include <Excel.au3> $oExcel = _ExcelBookNew() _ExcelWriteCell($oExcel, 6900, 1, 1) _ExcelNumberFormat($oExcel, "0.00", "A:A") My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RickB75 Posted September 20, 2013 Author Share Posted September 20, 2013 Thanks Water! I still have to strip the comma from the number, which is fine and I've applied this to the script to. Once again, your help is greatly appreciated. Link to comment Share on other sites More sharing options...
water Posted September 20, 2013 Share Posted September 20, 2013 The _StringStripChr functions is a bit of an overkill. you could simply use $price[2] = StringReplace($price[2], "," , "") My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RickB75 Posted September 22, 2013 Author Share Posted September 22, 2013 sorry for the delayed response water. That's a great point. I didn't think about using StringReplace like that. Once again thank you sooooo much for your advice and help. I have one more quick question. Any advice on making this run faster? I thought about trying to rewrite the script using InetGetSource but it would be pretty difficult for me. I think I would have to use a lot of RegEx's to grab the data. All I need is the source from the webpage. I don't need to load the images, which slow it down because it's waiting for the full page to load and return before it loops through again. I think thats the time consuming part of the entire script. It writes to excel rather quickly. Link to comment Share on other sites More sharing options...
water Posted September 23, 2013 Share Posted September 23, 2013 I don't know how many cells you need to write in Excel. But it _ExcelWriteCell can be quite slow of you process a lot of cells. You could create an output array and write this array to Excel in one go. Unfortunately you need to run my rewrite of the Excel UDF and the latest beta of AutoIt. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RickB75 Posted September 24, 2013 Author Share Posted September 24, 2013 Water, Can I store the Beta version of Autoit in a different area on my pc and not have it interrupt my current version of Autoit that's not the Beta version? The last thing I want to happen is to download the beta version and it overwrite my current files of Autoit. Also, does Scite work with the beta version or would I need to get a special version of Scite to work with the Beta version of Autoit? These maybe dumb questions but, I don't know the true answer. Thats why I'm asking them. Link to comment Share on other sites More sharing options...
water Posted September 25, 2013 Share Posted September 25, 2013 (edited) Sure. At the moment the UDF is called "Excel Rewrite.au3". So you simply store it in the directory where your script is located and add a "#include <Excel Rewrite.au3>" to your script. The new Excel UDF doesn't interfere with SciTE. SciTE doesn't know about this new UDF. Edited September 25, 2013 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RickB75 Posted September 25, 2013 Author Share Posted September 25, 2013 I'm gonna give it a try. Link to comment Share on other sites More sharing options...
water Posted September 25, 2013 Share Posted September 25, 2013 If you have any questions I will be happy to answer them My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now