Jump to content
Sign in to follow this  
RickB75

Need a little help with formatting in Excel

Recommended Posts

RickB75

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

 

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
RickB75

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 by RickB75

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
RickB75

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.

#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

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
RickB75

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.

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
RickB75

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.

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
RickB75

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.

Share this post


Link to post
Share on other sites
water

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
RickB75

I'm gonna give it a try.

Share this post


Link to post
Share on other sites
water

If you have any questions I will be happy to answer them :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×