Getting data from excel based on first column

[water]

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.

 

[Taxyo]

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? 

[water]

Fixed version:

expandcollapse popup

#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'.")

 

[Taxyo]

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.