Jump to content
aa2zz6

Excel Range counter

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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
aa2zz6

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

Share this post


Link to post
Share on other sites
water

And if it doesn't as well ^_^


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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
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 by aa2zz6

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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
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 by aa2zz6

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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
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

 

Untitled1.png

Edited by aa2zz6

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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
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))

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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
aa2zz6

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

Share this post


Link to post
Share on other sites
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 :)


 

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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
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! :)

  • Like 1

SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource        Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites
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 :lol:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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

×