Excel Column Population question

[Fractured]

So im still working on this frustrating Excel proggy and now im trying to populate across columns....I was going to create an array of the alphabet so that it would be easy to do my ranges with $ Range = $array_column[$n_Counter] & $iRows but im feeling lazy and dont want to type out the alphabet!!  So I got to reading all the excel helps and wiki and it mentions "Range Object". Since im feeling obtuse, what is a range object? And is there a quicker way to write to columns ...i.e. A1, B1, C1, D1...without having to type out each column letter or type out an alphabet array?

My brain is Excel fried now....with extra cheese....

 

[TheXman]

Everything you need to know about the Excel Object Model

https://docs.microsoft.com/en-us/visualstudio/vsto/excel-object-model-overview?redirectedfrom=MSDN&view=vs-2019

https://docs.microsoft.com/en-us/office/vba/api/overview/Excel/object-model

 

 

Edited August 18, 2020 by TheXman

[TheXman]

A quick & dirty example:

#include <Excel.au3>

example()

Func example()
    Local $oExcel

    $oExcel = _Excel_Open()
    _Excel_BookNew($oExcel)

    ;Write A-Z across Row 1
    For $iCol = 1 To 26 ;Cols A-Z
        $oExcel.ActiveWorkBook.ActiveSheet.Cells(1, $iCol) = Chr(Asc("A") + ($iCol - 1))
    Next
EndFunc

As you can see, .Cells lets you specify a cell by its row and col.  The example above merely writes the characters across row 1.

Edited August 18, 2020 by TheXman

[Fractured]

Ok..you rock! Now ive got some knowledge and some reading to do and Ill be all over this! Thanks!

[TheXman]

You're welcome! 

[Fractured]

Quick question...If I have 2 books, 1 sheet apiece...and I want to copy specific contents from 1 book to the other, would I use winactivate or is there another way to switch between the 2 books? i.e. Activate 1 book, read from it...Activate the secound book and write to it...

Or...crazyness!!..can I just add sheets to the ActiveWorkBook, and switch between the sheets?....how would it know which sheet should be active? 

Trying to figure out the fastest easiest way to do this!!!

 

[TheXman]

@Fractured

Workbooks have names and Worksheets have names.  The Excel Object Model lets you specify those names using the object model's object, properties, and methods.  I just used ActiveWorkbook & Activesheet as shortcuts.  You can name the workbook that you want to reference by doing something like Application.WorkBooks("Book1") and similarly Workbooks("Book1").Sheets("sheet1") . So you could copy a range from Book1.Sheet1 to Book2.Sheet1 (if that makes sense).  If you need a better explanation or example, please ask.

Quick & Dirty Example of naming the workbook or sheet that you want to reference:

#include <Excel.au3>

example()

Func example()
    Local $oExcel

    $oExcel = _Excel_Open()
    _Excel_BookNew($oExcel)
    _Excel_BookNew($oExcel)

    ;Write A-Z across row 1 to Book1
    For $iCol = 1 To 26 ;Cols A-Z
        $oExcel.Workbooks("book1").Sheets("Sheet1").Cells(1, $iCol) = Chr(Asc("A") + ($iCol - 1))
    Next

    ;Write Z-A across row 1 to Book2
    For $iCol = 26 To 1 Step -1 ;Cols Z-A
        $oExcel.Workbooks("book2").Sheets("Sheet1").Cells(1, $iCol) = Chr(Asc("Z") - ($iCol - 1))
    Next
EndFunc

 

Edited August 18, 2020 by TheXman

[Fractured]

Ill try and work with that..Reading the Microsoft Range Interface pages. Hardcore learning curve in effect!!

[TheXman]

The VBA pages are easier to understand and closer to the AutoIt syntax.  I have provided links in the previous post to point you directly to the Application.Workbooks property and the WorkBook.Sheets property.

Another thing you can do to get a better feel for working with the Excel objects is to look at the code in Excel.au3 UDF. 

Edited August 18, 2020 by TheXman

[TheXman]

2 hours ago, Fractured said:

I want to copy specific contents from 1 book to the other

I forgot to mention that _Excel_RangeCopyPaste() will do this too. 

#include <Excel.au3>

example()

Func example()
    Local $oExcel, $oSourceSheet, $oSourceRange, $oTargetRange

    $oExcel = _Excel_Open()
    _Excel_BookNew($oExcel)
    _Excel_BookNew($oExcel)

    With $oExcel
        ;Write A-Z across row 1 to Book1
        For $iCol = 1 To 26 ;Cols A-Z
            .Workbooks("book1").Sheets("Sheet1").Cells(1, $iCol) = Chr(Asc("A") + ($iCol - 1))
        Next

        ;Copy A1:E1 in Book1/Sheet1 to A1 in Book2/Sheet1
        $oSourceSheet = .Workbooks("Book1").Sheets("Sheet1")
        $oSourceRange = $oSourceSheet.Range("A1:E1")
        $oTargetRange = .Workbooks("Book2").Sheets("Sheet1").Range("A1")

        _Excel_RangeCopyPaste($oSourceSheet, $oSourceRange, $oTargetRange)
    EndWith
EndFunc

 

Edited August 18, 2020 by TheXman

[Fractured]

LOL!! Now ya tell me!! ROFL!! Good stuff!!

[Fractured]

@TheXman - I cant attach the code at the moment, but Im having a new problem with the population. It populates good for some of the sheet then it fails out with: "F:\Autoit Scripting\Models\Inventory\Rework\Inventory Tracker1.0.au3" (284) : ==> The requested action with this object has failed."

It seems to do that when it has alot of info to populate...It dosent always happen on the same line or "spot" in the script. Seems totally random. Ill try and post the code later this morning after meeetings.

 

[TheXman]

The first thing that I would suggest would be to temporarily (or permanently) add additional logging information the the excel.au3 udf's __Excel_COMErrFunc() so that you may be able to find out more about why the error is occurring.  You should be able to replace the function with something like the one below, which will write additional COM information to the console when an error occurs.  Beyond that, one would have to see your script to see if there is an issue that needs to be addressed or whether some additional defensive coding needs to be added to avoid such errors.

Hopefully your script has sufficient status logging so you can tell which record or records are being worked on when the error occurs.  If not, it should because without it, trying to trouble shoot random errors is much more difficult.

 

Func __Excel_COMErrFunc($oComErr)
    Local $sErrMsg

    With $oComErr
        $sErrMsg =  @CRLF
        $sErrMsg &= ">>>  COM ERROR  <<<" & @CRLF
        $sErrMsg &= StringFormat("Script Line Number : %s", .ScriptLine) & @CRLF
        $sErrMsg &= StringFormat("HRESULT            : 0x%x (%s)", .Number, .Number) & @CRLF
        $sErrMsg &= StringFormat("Windows Error      : %s", StringStripWS(.WinDescription, $STR_STRIPTRAILING)) & @CRLF
        $sErrMsg &= StringFormat("Object Description : %s", StringStripWS(.Description, $STR_STRIPTRAILING)) & @CRLF
        $sErrMsg &= StringFormat("GetLastError()     : %s", .LastDllError) & @CRLF
    EndWith

    ConsoleWrite($sErrMsg)

    Return
EndFunc

 

Edited August 20, 2020 by TheXman

[Fractured]

I got it figured out...was calling an object incorrectly. Spelling error so minor my eyes just couldnt find it!!

Only issue now is I have to run a loop, and it takes forever since its looking for different needles in a hugh haystack...I would love the loop to restart after it performs a function so it starts the search back over. Reading more on the Excel stuff to see if I can drop the loop. _Excel_RangeFind might be helpful in this but also reading the excel links you provided!! Alot to learn!

[seadoggie01]

Also, back at the beginning, you asked about getting column names. Excel.au3 has two functions to help you convert numbers into letters: _Excel_ColumnToLetter and _Excel_ColumnToNumber. Give the first a number and it gives you a letter back, while the second does the opposite. It's nice for printing cell addresses when you need

[Fractured]

@seadoggie01 - Awesome!! Thats good to know also!! I really need to start reading the UDF's really well before using them!!!

@TheXman - My errors are being caused by the settings in Sophos, the AV my company uses...it randomly starts and stops, so it seems to be timing out the script in the middle of a call to excel. Was watching task manager as I ran the script and the minute Sophos virus scan hit high CPU it would error out the script.