Need a little help with excel from the Autoit Wizards!

[RickB75]

Guys,

My story:

My boss wants me to break down our pricing on our website into a spreadsheet by year, make, model, trim, ect... I'm trying to automate as much of this as possible.

This is what I'm trying to do. My script reads our webpage and grabs inner text between a certain tag on the page and places that data into a spreadsheet. What I'm trying to do is put this data into a spreadsheet so basicly I can apply a formula to populate a certain column. The amount of data varies between the Tags therefore moving the dollar amounts that my formula needs into different cells therefore not rendering the correct discount. I basicly need to get the data into certain cells in excel. How can I do this. Any help that you guys can offer is greatly apprectiated.

Here is a link to my site

http://www.beamantoyota.com/new-inventory/index.htm?SBmake=Toyota&SByear=clear&SBmodel=Corolla&SBtrim=clear&SBbodystyle=clear&SBhighwayMPG=clear

Here is my script

expandcollapse popup

#include
#Include
#include
#include

Local $oExcel = _ExcelBookNew() ;open new excel book
Local $file = FileOpen("C:\Documents and Settings\salespc2\My Documents\websiteScripts\ToyotaModelsTwoAnalyze.txt") ;open text file for reading

;add and name sheets to the excel book that was just opened and delete sheet 1-3
_ExcelSheetAddNew($oExcel, "Camry")
_ExcelSheetAddNew($oExcel, "CamryHybrid")
_ExcelSheetAddNew($oExcel, "Corolla")
_ExcelSheetAddNew($oExcel, "4Runner")
_ExcelSheetAddNew($oExcel, "Avalon")
_ExcelSheetAddNew($oExcel, "Highlander")
_ExcelSheetAddNew($oExcel, "Highlander Hybrid")
_ExcelSheetAddNew($oExcel, "Prius C")
_ExcelSheetAddNew($oExcel, "Prius")
_ExcelSheetAddNew($oExcel, "PriusV")
_ExcelSheetAddNew($oExcel, "Sienna")
_ExcelSheetAddNew($oExcel, "Sequoia")
_ExcelSheetAddNew($oExcel, "Tacoma")
_ExcelSheetAddNew($oExcel, "Tundra")
_ExcelSheetAddNew($oExcel, "Venza")
_ExcelSheetAddNew($oExcel, "Rav4")
_ExcelSheetAddNew($oExcel, "Land Cruiser")
_ExcelSheetDelete($oExcel, "Sheet1")
_ExcelSheetDelete($oExcel, "Sheet2")
_ExcelSheetDelete($oExcel, "Sheet3")


;start loop
While 1
Local $line = FileReadLine($file);read txt file that was opened
If @error = -1 Then ExitLoop ;if we can't open the text file then error and exit
$oIE = _IECreate ($line,0,0,1); open IE in the background and input website address from the text file


$HElements = _IETagNameGetCollection($oIE, "LI");tells the script to look for the<li>html tag
$EMElements = _IETagNameGetCollection($oIE, "EM")
$icounter = 2 ;skip to the 2nd row in excel
Local $header [10]= [" ","Year","Make","Model","Trim","Vin","MSRP","Discount","Sale Price","Percentage Discount"] ;headers on excel spreadsheet

_ExcelWriteArray ($oExcel, 1, 1,$header,0);wirtes headers into excel


For $HElement In $HElements ;find LI element and start loop
$aArray = Stringsplit($HElement.innerText, " ") ;split up the inner text inside the LI element based on the blank space in between the words


If UBound($aArray) > 5 Then ;omit the excess data from the webpage except for items with more than 5 cells of data

Select ;start looking for these spcific peices of text
Case $aArray[4]= "CAMRY" And $aArray[5] ="HYBRID" ;camry in the 4 data cell and hybrid in the 5th data cell
_ExcelSheetActivate($oExcel, "CamryHybrid");activate the camryhybrid sheet
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0);write out all the data inside the LI tag on the page
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40) ;formula to apply in cell 40 and using a icounter
$icounter = $icounter + 1; this starts icounter on the 2nd row in excel
Case $aArray[4] = "CAMRY"
_ExcelSheetActivate($oExcel, "Camry")
_ExcelWriteArray($oExcel,$icounter, 1,$aArray)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "COROLLA"
_ExcelSheetActivate($oExcel, "Corolla")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "4RUNNER"
_ExcelSheetActivate($oExcel, "4Runner")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "AVALON"
_ExcelSheetActivate($oExcel, "Avalon")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "HIGHLANDER" And $aArray[5] = "HYBRID"
_ExcelSheetActivate($oExcel, "Highlander Hybrid")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "HIGHLANDER"
_ExcelSheetActivate($oExcel, "Highlander")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "PRIUS" And $aArray[5] = "C"
_ExcelSheetActivate($oExcel, "Prius C")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "PRIUS" And $aArray[5] = "V"
_ExcelSheetActivate($oExcel, "PriusV")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "PRIUS"
_ExcelSheetActivate($oExcel, "Prius")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "SIENNA"
_ExcelSheetActivate($oExcel, "Sienna")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
$icounter = $icounter + 1
Case $aArray[4] = "SEQUOIA"
_ExcelSheetActivate($oExcel, "Sequoia")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "TACOMA"
_ExcelSheetActivate($oExcel, "Tacoma")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "TUNDRA"
_ExcelSheetActivate($oExcel, "Tundra")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "VENZA"
_ExcelSheetActivate($oExcel, "Venza")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "RAV4"
_ExcelSheetActivate($oExcel, "Rav4")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
Case $aArray[4] = "LAND" And $aArray[5] = "CRUISER"
_ExcelSheetActivate($oExcel, "Land Cruiser")
_ExcelWriteArray ($oExcel, $icounter, 1,$aArray,0)
_ExcelWriteCell($oExcel, "=AF" & $icounter & "/AD" & $icounter & "*100", $icounter, 40)
$icounter = $icounter + 1
EndSelect ;stop looking for the particular text

ENDIF ;end if


Next ;go to the next until EOF


_IEQuit($oIE) ;close ie before opening another ie window

WEnd ;exit loop


MsgBox(0, "AT END", "Done"); message box done running script

ToyotaModelsTwoAnalyze.txt

Edited November 6, 2012 by RickB75

[water]

How far does your script work? I don't have the needed text file so can#t run the script.

[RickB75]

The script will run until done. My problem is when the data gets into excel. Example: "Corolla" if one corolla is color labled "blue" and the next one is labled "light blue" then the pricing info for these vehicles will be in different cells in excel because the script is set to _StringSplit on the " " blankspace between the words. When the array is written into excel at the end of row is the pricing info for each vehicle. Since the vehicles have different amounts of data, the pricing info will be in different columns, therefore my formula won't work because it is set to caculate based on columns. What i need is to be able to put the pricing info for each vehicle into specific columns on each row in excel. The data I need falls between the <LI> tags. Once you run it, I think you will be able to understand my problem. Sorry for not including the txt file before.

[Myicq]

To me it seems like you make some assumptions about the structure of the data, which may break your script.

So have a (very) close look at the source of the data.

Speding just 2 mins, I could see that you have a certain structure around the price

<em>
<abbr title="dollar">$</abbr>20,139
</em>

This seems unique to pricing.

Guess you can do same thing for other properties.

[RickB75]

I did try to use the <EM> tag along with the Just using the <H2> Tag but the problem I kept running into is sometimes there are a different number of EM tags inside the main LI tag which holds the H2 and EM tags. The Ideal thing to do I think would be to write out every EM tag thats Inside of the LI tag. I just don't know how to keep the EM tag data on the same row in excel With the LI data. One LI tag could have one EM tag and the next LI tag could contain three EM tags.

[RickB75]

How can I keep the EM tags that reside inside each LI tag on the same line in Excel?

[Myicq]

I would personally break this into smaller parts..

1) carefully analyze the source and find it's structure. Test and test and test regular expressions on the source until you can exclude bits and pieces to your satisfaction.

2) take bits and pieces apart, until you can get just raw data, then debug using simple things like text files or editfields

3) THEN start making arrays to insert into Excel

Debugging both $oIE, regular expressions and Excel insertion in one step is often causing errors.

“The journey of 1000 miles begins with a single step.”

[RickB75]

I completly agree with analyzing the data. I've looked at it many, many times. I need a loop to loop inside each LI Tag that contains more than 5 cells of data and get the inner text for each EM tag because all the EM tags I need sit inside the LI tags. Once I create that loop I can use the same loop structure to find the vin #'s and other data. My problem is, I don't know how to build that loop.

[RickB75]

I've made a little progress with this challenge. I found the _StringBetween function in the help file and that is helping me grab the data inside the LI tag that I need.

[GMK]

If you wanted to break things further down, you can get the tags within tags by using _IETagNameGetCollection and using the tag objects, instead of just the $oIE object as the first parameter.

[RickB75]

@GMK

Can you explain or give me an example. I don't understand. I thought you had to use $oIE as the first parameter.

[Myicq]

Just came to think of another strategy you could follow on this one: open each car's page by itself, this won't take long at all in a script since you don't load images.

And you have something fairly structured:

<div class="widgetWrap">
[...]
<dl>
<dt>
<span>FieldName</span>
</dt>
<dd>
<span>FieldData</span>
</dd>
[...more fields ...]
</dl>
</div>

so just iterate over the <dl>..</dl> inside the widgetWrap.

a third strategy would be to create a regexp to extract the things for you into an array. If you can find unique markers for start/end of each car data.

Many ways lead to Rome. (all in fact, some say)

[MrMitchell]

Here's a method to do it which will:

• Grab the source of the page
• Use a stringregex on it to grab VIN/MSRP pairs and drop them into a temporary array
  • Use the first iteration to create the "final" array by copying the temporary array into it
  • Otherwise, move on to the next page(s) and repeat step 2 then concatenate the temporary array with the final array
• The final array is then dropped into a spreadsheet.

This is fully working (at least for me) so you should be able to run it as is. Like the last poster said there's a billion ways to go about it. Mine probably not the most efficient, but hey, still works.

#include <Array.au3>
#include <Excel.au3>
#include <Inet.au3>
Dim $aFinalVINPrice
Dim $oExcel, $row
$url = "http://www.beamantoyota.com/new-inventory/index.htm?SBmake=Toyota&SByear=clear&SBmodel=Corolla&SBtrim=clear&SBbodystyle=clear&SBhighwayMPG=clear"
$string = _INetGetSource($url)
;~ ConsoleWrite($string)
$aPages = StringRegExp($string, '<li><a href="?start=(d*)" rel', 3)
$aPages = _ArrayUnique($aPages)
;~ _ArrayDisplay($aPages)
For $i = 1 To $aPages[0]
$string = _INetGetSource($url & "&start=" & $aPages[$i])
ConsoleWrite("URL:" & $url & "&start=" & $aPages[$i] & @CRLF)
$regex = '<dt>VIN:</dt><dd>(w*)</dd>[sS.]*?<span>MSRP</span>[sS]*?<em>[sS]*?<abbr title="dollar">$</abbr>([d,]*)'
$aVINPrice = StringRegExp($string, $regex, 3)
If IsArray($aFinalVINPrice) Then
  _ArrayConcatenate($aFinalVINPrice, $aVINPrice)
Else
  $aFinalVINPrice = $aVINPrice
EndIf
Next
;~ _ArrayDisplay($aFinalVINPrice)
$oExcel = _ExcelBookNew()
_ExcelWriteCell($oExcel, "VIN", 1, 1)
_ExcelWriteCell($oExcel, "MSRP", 1, 2)
$row = 2
For $j = 0 To UBound($aFinalVINPrice) - 1 Step 2
_ExcelWriteCell($oExcel, $aFinalVINPrice[$j], $row, 1)
_ExcelWriteCell($oExcel, $aFinalVINPrice[$j + 1], $row, 2)
$row += 1
Next

[GMK]

See post #16.

Edited November 21, 2012 by GMK

[MrMitchell]

To Rick,

Since this is your place of employment, do you not have the ability to access the back-end database or some kind of export or dump file for this information?

And as for my script, I apologize it only includes MSRP, I realized later that you need much more than that.

[GMK]

expandcollapse popup

#region ; Includes
#include <Excel.au3>
#include <IE.au3>
#include <Array.au3>
#endregion ; Includes

#region ; Options
Opt("TrayIconDebug", 1)
Opt("MustDeclareVars", 1)
#endregion ; Options

#region ; Variables
; #ARRAYS# ======================================================================================================================
Global $asModels[7] = ["Corolla", "Camry", "Highlander", "Highlander%20Hybrid", "Land%20Cruiser", "Prius%20Hybrid", "RAV4"]
Global $aVehicles, $asSplit
Global $asArray[1][9] = [["Year", "Make", "Model", "Trim", "VIN", "MSRP", "Discount", "Sale Price", "% Discount"]]
Global $aiFind1, $aiSearch
; ===============================================================================================================================

; #STRINGS# =====================================================================================================================
Global $sTitle, $sURL, $sHTML, $sSearch, $sSheet
; ===============================================================================================================================

; #OBJECTS# =====================================================================================================================
Global $oIE = _IECreate(), $oH2s, $oDLs, $oDDs, $oSPANs, $oEM, $oExcel
; ===============================================================================================================================

; #INTEGERS# ====================================================================================================================
Global $iH2Count, $iH2, $iPct, $iDLCount, $iDL, $iVIN = 0, $iSPANs, $iSPAN, $iPrice = 0, $iCalcState, $iFound, $iTempRow, $iLastRow
; ===============================================================================================================================

; #FLAGS# =======================================================================================================================
Global $fScreenUpdateState, $fStatusBarState, $fEventsState
; ===============================================================================================================================
#endregion ; Variables

#region ; Internet Explorer
For $iModel = UBound($asModels) - 1 To 0 Step -1
    $sTitle = "Toyota " & StringReplace($asModels[$iModel], "%20", " ")
    $sURL = "http://www.beamantoyota.com/new-inventory/index.htm?SBmake=Toyota&SByear=clear&SBmodel=" & $asModels[$iModel] & "&SBtrim=clear&SBbodystyle=clear&SBhighwayMPG=clear&start=1"
    _IENavigate($oIE, $sURL)
    $sHTML = _IEDocReadHTML($oIE)
    If Not StringInStr($sHTML, "Sorry, no ") Then
        While 1
            If Not StringInStr(_IEPropertyGet($oIE, "locationurl"), "SBmake") Then ExitLoop
            $sHTML = _IEDocReadHTML($oIE)
            $aVehicles = StringRegExp($sHTML, "Viewing (d*) - (d*) of (d*) Vehicles", 1)
            $oH2s = _IETagNameGetCollection($oIE, "H2") ; Find all H2 tags
            $iH2Count = @extended ; Find number of H2 tags found
            ProgressOn($sTitle, "Finding models...", "0%")
            $iH2 = 0
            For $oH2 In $oH2s
                If StringRegExp($oH2.innerText, "d{4}.*") Then ; Add H2 to array if it contains a year
                    $asSplit = StringSplit($oH2.innerText, " ") ; Split H2 into Year/Make/Model
                    ReDim $asArray[UBound($asArray) + 1][9]
                    $asArray[UBound($asArray) - 1][0] = $asSplit[1] ; Set Year
                    $asArray[UBound($asArray) - 1][1] = $asSplit[2] ; Set Make
                    $asArray[UBound($asArray) - 1][2] = $asSplit[3] ; Set Model
                    If $asSplit[0] > 4 Then
                        For $iCounter = 4 To $asSplit[0] - 1
                            $asArray[UBound($asArray) - 1][2] &= " " & $asSplit[$iCounter] ; Additional model description
                        Next
                    EndIf
                    $asArray[UBound($asArray) - 1][3] = $asSplit[$asSplit[0]] ; Set Trim
                EndIf
                $iH2 += 1 ; Add 1 to row count
                $iPct = Int($iH2 / $iH2Count * 100) ; Get completion percentage
                ProgressSet($iPct, $iPct & "%")
            Next
            ProgressOff()
            $oDLs = _IETagNameGetCollection($oIE, "DL") ; Find all DL tags
            $iDLCount = @extended ; Find number of DL tags
            ProgressOn($sTitle, "Finding VINs...", "0%")
            $iDL = 0
            For $oDL In $oDLs
                $oDDs = _IETagNameGetCollection($oDL, "DD") ; Find all DD tags within DL tags
                For $oDD In $oDDs
                    If StringLen(StringStripWS($oDD.innerText, 8)) = 17 Then
                        $iVIN += 1
                        $asArray[$iVIN][4] = StringStripWS($oDD.innerText, 8) ; VIN
                    EndIf
                Next
                $iDL += 1
                $iPct = Int($iDL / $iDLCount * 100)
                ProgressSet($iPct, $iPct & "%")
            Next
            ProgressOff()
            $oSPANs = _IETagNameGetCollection($oIE, "SPAN") ; Find all SPAN tags
            $iSPANs = @extended
            ProgressOn($sTitle, "Finding prices...", "0%")
            $iSPAN = 0
            For $oSPAN In $oSPANs
                If StringInStr($oSPAN.className, "price") Then
                    $oEM = _IETagNameGetCollection($oSPAN, "EM", 0) ; Find first (only) EM tag within SPAN tag
                    If StringInStr($oSPAN.innerText, "MSRP") Then
                        $iPrice += 1
                        $asArray[$iPrice][5] = $oEM.innerText ; Set MSRP
                    ElseIf StringInStr($oSPAN.innerText, "Instant Savings") Then
                        $asArray[$iPrice][6] = $oEM.innerText ; Set Discount
                    ElseIf StringInStr($oSPAN.innerText, "Adjusted Price") Then
                        $asArray[$iPrice][7] = $oEM.innerText ; Set Sale Price
                    EndIf
                EndIf
                $iSPAN += 1
                $iPct = Int($iSPAN / $iSPANs * 100)
                ProgressSet($iPct, $iPct & "%")
            Next
            ProgressOff()
            If $aVehicles[1] = $aVehicles[2] Then ExitLoop
            _IELinkClickByText($oIE, "Next Page")
        WEnd
    EndIf
Next
_IEQuit($oIE) ; Quit IE
#endregion ; Internet Explorer

#region ; Sort array by model, then by year
_ArraySort($asArray, 0, 1, 0, 2) ; Sort by model first
For $iRow1 = 1 To UBound($asArray) - 1
    $aiFind1 = _ArrayFindAll($asArray, $asArray[$iRow1][2], 1, 0, 0, 0, 2)
    If UBound($aiFind1) > 1 Then
        _ArraySort($asArray, 0, $aiFind1[0], $aiFind1[UBound($aiFind1) - 1], 0) ; Sort by year second
        $iRow1 = $aiFind1[UBound($aiFind1) - 1]
    EndIf
Next
;~ _ArrayDisplay($asArray)
#endregion ; Sort array by model, then by year

#region ; Excel
SplashTextOn($sTitle, "Opening Excel...", @DesktopWidth, 50)
$oExcel = _ExcelBookNew()

; The following is meant to enhance speed
$fScreenUpdateState = $oExcel.ScreenUpdating
$fStatusBarState = $oExcel.DisplayStatusBar
$iCalcState = $oExcel.Calculation
$fEventsState = $oExcel.EnableEvents
With $oExcel
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .Calculation = $xlCalculationManual
    .EnableEvents = False
EndWith

SplashTextOn("Toyota", "Loading data into Excel...", @DesktopWidth, 50)
For $iRow = UBound($asArray) - 1 To 1 Step -1 ; Loop through array from bottom to top
    $sSearch = $asArray[$iRow][2]
    If StringLen($sSearch) > 31 Then
        $sSheet = StringLeft(StringRegExpReplace($sSearch, "[*][:'/?]", ""), 31)
    Else
        $sSheet = StringRegExpReplace($sSearch, "[*][:'/?]", "")
    EndIf
    ConsoleWrite($sSheet & @CRLF)
    _ExcelSheetAddNew($oExcel, $sSheet)

    $aiSearch = _ArrayFindAll($asArray, $sSearch, 1, 0, 0, 0, 2) ; Search for model
    Global $asTemp[UBound($aiSearch) + 1][9] ; Create temp array
    For $iCol = 0 To UBound($asArray, 2) - 1
        $asTemp[0][$iCol] = $asArray[0][$iCol]
    Next
    $iFound = 0
    For $iFoundRow = 0 To UBound($aiSearch) - 1
        $iFound += 1
        For $iCol = 0 To UBound($asArray, 2) - 2
            $iTempRow = $aiSearch[$iFoundRow]
            $asTemp[$iFound][$iCol] = $asArray[$iTempRow][$iCol]
        Next
    Next
    $iRow = $aiSearch[0]

    $asTemp = $oExcel.Transpose($asTemp) ; We have to transpose the array before inserting it
    If UBound($asTemp, 0) > 1 Then
        $oExcel.Sheets($sSheet).Range("A1").Resize(UBound($asTemp, 2), UBound($asTemp, 1)).Value = $asTemp ; Insert multi-row array
    Else
        $oExcel.Sheets($sSheet).Range("A1").Resize(1, UBound($asTemp, 1)).Value = $asTemp ; Insert one-row array
    EndIf
    $iLastRow = $oExcel.Sheets($sSheet).UsedRange.Rows.Count ; Get last row
    $oExcel.Sheets($sSheet).Range("I2:I" & $iLastRow).Formula = '=IF(G2="","",ABS(G2)/F2*100)' ; Set formula for discount
    $oExcel.Sheets($sSheet).UsedRange.Columns.Autofit ; Autofit all columns
Next

For $iSheet = 1 To 3 ; Delete Sheets 1-3
    _ExcelSheetDelete($oExcel, "Sheet" & $iSheet)
Next

;Now we set the following back to their original settings
With $oExcel
    .ScreenUpdating = $fScreenUpdateState
    .DisplayStatusBar = $fStatusBarState
    .Calculation = $iCalcState
    .EnableEvents = $fEventsState
EndWith

SplashOff()
#endregion ; Excel

Edit: Added ".Value" for a quicker array insert.

Edited November 30, 2012 by GMK

[RickB75]

WOW!!!! Those scripts are amazing. It's gonna take me a week just to decipher them so that I can understand. I am very green at this and both of you guys have blown my mind. How do you write something like that so quickly? With my capabilities right now, I would not be able to write something this complex. Thank you, Thank you, Thank you!!!!

@Mr. Mitchell

There are reports that does contain this info in our internal system but, the reports contain alot of unnecessary data and typically from the time a price gets changed internally, it takes 3 days before it populates online. This will give us basically a real time pricing analysis and we will be able to see what is displayed online that day and see what has been updated and what hasn't versus printing a report on a printer that's per-historic and still not knowing if the pricing has been published online.

Once again, Thank you sooooo much to each one of you guys that has helped me with this.