water Posted November 6, 2019 Posted November 6, 2019 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Taxyo Posted November 6, 2019 Author Posted November 6, 2019 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 Posted November 6, 2019 Posted November 6, 2019 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'.") My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Taxyo Posted November 7, 2019 Author Posted November 7, 2019 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.
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now