aa2zz6

Storing Data in Excel sheet

3 posts in this topic

#1 ·  Posted (edited)

I'm looking to revise my script so I can reduce the amount of hours updating my real estate mapping I started a year ago. The script pulls data from a real estate website and write my data to an excel sheet as [A1-A3] [ B1-B3] and it repeats for each house. When I import my data to my map it needs to be A1 - C1 in order for the map to read the {house number}{Market Status} and {property cost} when I join the data to our parcel layers. 

It's currently pulling data and storing it like below

<A1>8632 byrd ave Ne, Alliance, OH 44601

<A2>Off Market

<A3>$161,944 est

How to change format to below. While importing fresh data.

<A1>8632 byrd ave Ne, Alliance, OH 44601 |  <B1>Off Market  | <C1>$161,944 est

#include <IE.au3>
#include <Array.au3>
#include <File.au3>
#include <Excel.au3>
Local $URLlist, $url, $file, $ListUrlFile = _FileListToArray(@ScriptDir, "*.txt")

Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\Test.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True)

If Not @error Then
    For $file = 1 To $ListUrlFile[0]
        $URLlist = FileReadToArray($ListUrlFile[$file])
        If @error Then ContinueLoop
        For $url = 0 To UBound($URLlist) - 1
            If Not StringInStr($URLlist[$url], "http") Then ContinueLoop
            Local $oIE = _IECreate($URLlist[$url], 0, 0)
            Local $oTable = _IETableGetCollection($oIE) ; Get number of tables
            $iNumTables = @extended ;counts number of tables
            ;MsgBox(64, "Table Info", "There are " & $iNumTables & " tables on the page")
            $oTable = _IETableGetCollection($oIE, $iNumTables - 1) ; Get last table
            $aTableData = _IETableWriteToArray($oTable)
            ;_ArrayDisplay($aTableData)
            With $oWorkbook.ActiveSheet ; process active sheet
                $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells
                $iLastRow = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count
            EndWith
            _Excel_RangeWrite($oWorkbook, Default, $aTableData, "A" & ($iLastRow + 1))
            _Excel_BookSave($oWorkbook)
        Next
    Next
EndIf

Data.png

Edited by aa2zz6

Share this post


Link to post
Share on other sites



Use _ArrayTranspose($aTableData) before _Excel_RangeWrite

1 person likes this

Share this post


Link to post
Share on other sites

Thank you, Subz. 

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