Jump to content
Taxyo

Getting data from excel based on first column

Recommended Posts

Which version of AutoIt do you run?
If you use AutoIt < 3.3.14.3 then you need to incorporate (modify Excel.au3) the following changes to fix the error.

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

It seems that the current approved version as of now in our company is 3.3.14.2.

 

And I don't have admin access to modify the include files as they are installed behind Admin priviliges. 

 

O well. As mentioned the error seemed to stem from me modifying the document after applying the "password protection" on the sheets, And I haven't gotten it since I removed that part from the script. So I guess that's a ... "fix" .

 

Another question which I mentioned. If the very first subset of data only contains 1 row it seems to process it together with the second set of data in the script; and also names the save file based on the second Set of data.

 

I.E, only have 1 row of A, it parses A -> B, saves information A but under the filename of B, then it parses A + B together and re-saves the filename as "B".  Then it goes to C ....D ...E and that works as expected. So the issue is only if A = only 1 row of data.

 

If i have 2 rows or more of A this doesn't occur. Is there something which needs to be modified in the For loop? 

Share this post


Link to post
Share on other sites

Fixed version:

#include <Excel.au3>

Global $sPrevious = ""                                       ; Value of column A of the previous row so we know that a change has happened
Global $iArrayRowCount = 0                                        ; Number of row a block consists of (e.g. number of rows with "B" in column A)
Global $iExcelStartRow = 2                                   ; Row where the data lines start (means. row 1 is the column header, data starts at row 2)
Global $iExcelStartBlockRow = $iExcelStartRow -1             ; Variable to hold the first row of the currently processed block
Global $sInputPath = @ScriptDir & "\test-A.xlsx"             ; Directory and filename of the input Excel
Global $sSavePath = @ScriptDir                               ; Directory where to write the output Excel files
Global $oExcel = _Excel_Open()                               ; Start up or connect to Excel
Global $oWorkbook = _Excel_BookOpen($oExcel, $sInputPath)    ; Open the input Excel workbook
If @error Then Exit MsgBox($MB_ICONERROR, "Test-Script", "@error = " & @error & ", @extended = " & @extended & " when opening the workbook!")
Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1)         ; Read all data from worksheet 1
; _Arraydisplay($aUsedRange)
; Ignore this line. The Excel workbook has already to be sorted on column "A". If needed insert a _Excel_RangeSort here
; _ArraySort($aUsedRange, 0, 1)                              ; Sort ascending on column 0 (first column), ignores header line
For $iArrayRow = $iExcelStartRow - 1 To UBound($aUsedRange, 1) - 1
    ; Column A has changed. We now process the current block here
    If $sPrevious <> $aUsedRange[$iArrayRow][0] And $sPrevious <> "" Then
        _Excel_RangeDelete($oWorkbook.Worksheets(1), $iArrayRow + 1 & ":99999")                               ; Delete all rows after the current block
        If $iExcelStartBlockRow >= $iExcelStartRow Then
            _Excel_RangeDelete($oWorkbook.Worksheets(1), $iExcelStartRow & ":" & $iExcelStartBlockRow)        ; Delete all rows before the current block
        EndIf
        $oWorkbook.Worksheets(1).UsedRange.Locked = True                                                      ; <== Lock all used cells
        $oWorkbook.Worksheets(1).Range("B2:B999").Locked = False                                              ; <== Unlock column B (excluding B1)
        $oWorkbook.Worksheets(1).Protect("password")                                                          ; <== Use a password to protect the worksheet
        _Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iExcelStartBlockRow][0], Default, True) ; Save the Excel holding only the current block and overwrite existing file
        If @error Then Exit MsgBox($MB_ICONERROR, "Test-Script", "@error = " & @error & ", @extended = " & @extended & " when saving the workbook!")
        _Excel_BookClose($oWorkbook)                                                                          ; Close the workbook with the current block
        $oWorkbook = _Excel_BookOpen($oExcel, $sInputPath)                                                    ; ReOpen the  workbook holding all data
        If @error Then Exit MsgBox($MB_ICONERROR, "Test-Script", "@error = " & @error & ", @extended = " & @extended & " when opening the workbook!")
        If MsgBox($MB_OKCANCEL, "Test-Script", "Column A changed from '" & $sPrevious & "' to '" & $aUsedRange[$iArrayRow][0] & "'. " & $iArrayRowCount & " rows processed." & @CRLF & _
                "Data has been copied to '" & $sPrevious & ".xlsx'. Please press 'OK' to continue or 'Cancel' to end the script.") = $IDCANCEL Then Exit
        $iArrayRowCount = 0                                                                                   ; Reset the row count for the next block
        $iExcelStartBlockRow = $iArrayRow                                                                     ; Save the row where the new block starts
    EndIf
    $sPrevious = $aUsedRange[$iArrayRow][0]                                                                   ; Save the value of column A of the currently processed row beofre moving to the next row
    $iArrayRowCount = $iArrayRowCount + 1                                                                     ; Count the processed row
Next
; End of table reached. Add processing of the last block here
If $iExcelStartBlockRow > $iExcelStartRow Then
    _Excel_RangeDelete($oWorkbook.Worksheets(1), $iExcelStartRow & ":" & $iExcelStartBlockRow)                ; Delete all rows before the current block
EndIf
_Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iExcelStartBlockRow][0], Default, True)         ; Save the Excel holding only the current block and overwrite existing file
If @error Then Exit MsgBox($MB_ICONERROR, "Test-Script", "@error = " & @error & ", @extended = " & @extended & " when saving the workbook!")
_Excel_BookClose($oWorkbook)                                                                                  ; Close the workbook
_Excel_Close($oExcel)                                                                                         ; Close Excel
MsgBox($MB_OKCANCEL, "Test-Script", "End of table reached. " & $iArrayRowCount & " rows processed." & _       ; Done!
        @CRLF & "Data has been copied to '" & $sPrevious & ".xlsx'.")

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

That absolutely seems to have done the trick :) Thanks a lot! 

 

It even seems to have solved the protection lock/protection part as I don't get the Excel.au3 error now when running with that part included.

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

×
×
  • Create New...