Excel Range counter

[aa2zz6]

On my excel spreadsheet I have information put in cells A1:Z3. I'm trying to import more data but it copies over the existing. Is there a way to offset it down 3 cells such as A4:Z6 and continue looping down the excel sheet?

Local $oExcel = _Excel_Open()
            Local $sWorkbook = @ScriptDir & "\Test.xlsx"
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error handler", "Error finding the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
            Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True)
            If @error Then
                MsgBox($MB_SYSTEMMODAL, "Error handler", "Error opening workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
                _Excel_Close($oExcel)
                Exit
            EndIf
            _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, "A1:Z3", False)
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "error handler", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
            MsgBox($MB_SYSTEMMODAL, "", "Range successfully filled with a formula.")
            _Excel_BookSave($oWorkbook)

 

Edited June 6, 2016 by aa2zz6

[JLogan3o13]

@aa2zz6 I guess you could do something like this to loop the RangeWrite. But wouldn't it be better to get all of your data into a single array and then write to Excel once?

#include <Excel.au3>

Local $aTableData[3][26]
For $i = 0 To 2
    For $j = 0 To 25
        $aTableData[$i][$j] = $i & "-" & $j
    Next
Next


Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\Test.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True)
    For $i = 1 to 100 Step 3
        _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, "A" & $i & ":Z" & $i + 2, False)
    Next

MsgBox($MB_SYSTEMMODAL, "", "Range successfully filled with a formula.")
_Excel_BookSave($oWorkbook)

 

Edited June 7, 2016 by JLogan3o13

[aa2zz6]

I'm pulling address data from  URL links at a time so each piece of data from a link is recorded within a excel sheet and then the script grabs the next URL and pulls the address data and places it underneath the previously recorded data but I hadn't had any luck on getting it to place right. I'll provide a zipped file attached below with everything inside. I searched the forums for something similar and I hadn't had any luck on a solution. 

Project File.zip

[water]

If you want to append data at the "end" of the sheet use the UsedRange range. Some examples can be found in the wiki.

[aa2zz6]

Okay, I'll try to implement something after I get off work and re-post if something does work correctly.  

[water]

And if it doesn't as well

[aa2zz6]

I added this piece of code which returns the last used cell (bottom right) in the used range. It reads the column of B and displays the last row B:10. Is there a way I can take the last cell B:10 add + 2 making it B:12 to the row and set that as the range for _Excel_RangeWrite? The only other problem is the _Excel_RangeWrite requires a format like this "B1: D:100" to write data in excel.

Local Const $xlUp = -4162
            With $oWorkbook.ActiveSheet ; process active sheet
                $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells
                $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column
                $iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number
                ConsoleWrite("Last used cell in column B: " & $iLastCell & @CRLF)
            EndWith

 This doesn't work but something like this:

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, $iLastCell & "D100", True)
iLastCell = iLastCell + 2

 

Edited June 7, 2016 by aa2zz6

[aa2zz6]

I got it to post data every 3 rows. Now I'll have to see why it's posting dates instead of my actual data.. possibly a bug?

[water]

You are entering numbers and Excel displays them as dates? Then set the formatting of the cells to Standard or Numbers.

[aa2zz6]

If the data is sent to fast it'll start to glitch and it shows the values as dates or 0's. A simple reset worked. I feel as though the excel portion is completely wrong since I can't figure out why it's not adding the data in correctly.

The data pulled has 3 tables  and is exported to excel. The data is imported into the cell sheet but based on the For loop it repeats the 3 tables 4x times with the same data rather than getting new data. And I can't seem to get the usedrange to begin the new piece of data.

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

            Local Const $xlUp = -4162
            With $oWorkbook.ActiveSheet ; process active sheet
                $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells
                $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column
                $iLastCell = .Cells($iRowCount + 1, "A").End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number
                ConsoleWrite("Last used cell in column A: " & $iLastCell & @CRLF)
                $i = $oRangeLast
            EndWith
            For $i = 1 To 10 Step 3
                _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, "A" & $i & ":Z" & $i + 2, False)
                _Excel_BookSave($oWorkbook)
            Next

 

Edited June 10, 2016 by aa2zz6

[water]

You get exactly what you coded. You are writing the same array 4 times to a fixed range and do not use the usedrange range you calculated before.

[water]

Example:

#include <Excel.au3>
Global $aArray[][] = [["LastName1", "FirstName1", "", "Address", "Data", "Data", "Data"], _
        ["LastName2", "FirstName2", "", "Address", "Data", "Data", "Data"], _
        ["LastName3", "FirstName3", "MiddleName", "", "Data", "Data", "Data"], _
        ["LastName4", "FirstName4", "", "Address", "Data", "Data", "Data"], _
        ["LastName5", "FirstName5", "MiddleName", "", "Data", "Data", "Data"]]
Global $oRange
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
Global $oRange = _Excel_RangeWrite($oWorkbook, Default, $aArray, "A1")
Global $iLastRow = $oWorkbook.Activesheet.UsedRange.Rows.Count
MsgBox(0, "", "Now adding data")

_Excel_RangeWrite($oWorkbook, Default, "Value 1", "A" & ($iLastRow + 1))
_Excel_RangeWrite($oWorkbook, Default, "Value 2", "A" & ($iLastRow + 2))
_Excel_RangeWrite($oWorkbook, Default, "Value 3", "A" & ($iLastRow + 3))

 

Edited June 9, 2016 by water

[aa2zz6]

I still get the same issues when it copies in the excel sheet. It'll copy data to the first three rows A1 & B1 & C1 but when it loads in the next batch of data instead of going to D1 it'll rewrite in C1 and go down. 

Here's what I mean: 

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>
$i = 0
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
Do
$oIE = _IECreate("http://www.realtor.com/propertyrecord-search/North-Canton_OH/Portage-St-NW", 0, 0)
$oTable = _IETableGetCollection ($oIE) ; Get number of tables
$iNumTables = @extended
MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page")
$oTable = _IETableGetCollection ($oIE, $iNumTables-1) ; Get last table
$aTableData = _IETableWriteToArray ($oTable)

Global $oRange

Global $iLastRow = $oWorkbook.Activesheet.UsedRange.Rows.Count
MsgBox(0, "", "Now adding data")

_Excel_RangeWrite($oWorkbook, Default, $aTableData, "A" & ($iLastRow + 1))
$i += 1
Until $i = 10

 

Edited June 10, 2016 by aa2zz6

[water]

There is no need to repeat statements in the loop that do not depend on the loop counter ($i).
Why do you need to write the same data multiple times to the worksheet?

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>
$i = 0
Global $oRange, $iLastRow
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
$oIE = _IECreate("http://www.realtor.com/propertyrecord-search/North-Canton_OH/Portage-St-NW", 0, 0)
$oTable = _IETableGetCollection($oIE) ; Get number of tables
$iNumTables = @extended
MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page")
$oTable = _IETableGetCollection($oIE, $iNumTables - 1) ; Get last table
$aTableData = _IETableWriteToArray($oTable)
Do
    $iLastRow = $oWorkbook.Activesheet.UsedRange.Rows.Count
    MsgBox(0, "", "Now adding data")
    _Excel_RangeWrite($oWorkbook, Default, $aTableData, "A" & ($iLastRow + 1))
    $i += 1
Until $i = 10

 

Edited June 10, 2016 by water

[aa2zz6]

I took the array portion out of the script since it requires txt files. I added the counter loop to replace the multiple data entries the array would of done. The data would be the same but it's how it's set in excel. When the data is grabbed from the url link it grabs (3) tables from the website. In excel, the (3) tables are put in row A1, B1, C1. The problem is if we load in any other data it will start in C1 which has data in it already, rather than D1 which is empty.

I thought that ($iLastRow + 1)) would look at the last used cell, add 1, and move down one cell and prevent data from being copied over but it doesn't work as I thought. 

($iLastRow + 1))

[water]

The picture in your last post shows that the first row which contains data is number 4. Is this correct?
In this case you would need to calculate $iLastRow this way:

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

 

Edited June 10, 2016 by water

[aa2zz6]

Thanks Water, it's finally coping the data into the cells correctly! Grats on 20k post!

[water]

Glad the problems could be solved
20k posts? OMG. Didn't expect to stay that long when I posted my first question more than 10 years ago

 

[Jos]

11 minutes ago, water said:

20k posts? OMG. Didn't expect to stay that long when I posted my first question more than 10 years ago

I know that feeling!

[water]

Checking the post count from time to time is another way to see how time flies
10+ years feel like only 3 or 4 to me. So hitting the 100k mark should be possible before I turn 100