Getting data from excel based on first column

[water]

The following script (quick and dirty with near-to-no documentation) splits your example workbook into multiple pieces:

expandcollapse popup

#include <Excel.au3>

Global $sPrevious = "", $iRowCount = 0, $iStartExcelRow = 2, $iBlockStartExcelRow = $iStartExcelRow, $iStartColumn = 0, $iEndColumn = 7
Global $sInputPath = @ScriptDir & "\test.xlsx", $sSavePath = @ScriptDir
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, $sInputPath)
Global $aUsedRange = _Excel_RangeRead($oWorkbook)
_ArraySort($aUsedRange, 0, 1) ; Sort ascending on column 0 (first column), ignores header line
_ArrayDisplay($aUsedRange)
For $iRow = 1 To UBound($aUsedRange, 1) - 1
    ; Column A has changed. Add processing of the previous block here
    If $sPrevious <> $aUsedRange[$iRow][0] And $sPrevious <> "" Then
        _Excel_RangeDelete($oWorkbook.Worksheets(1), $iRow + 1 & ":99999") ; Delete all rows after the current block
        If $iBlockStartExcelRow > $iStartExcelRow Then
            _Excel_RangeDelete($oWorkbook.Worksheets(1), $iStartExcelRow & ":" & $iBlockStartExcelRow) ; Delete all rows before the current block
        EndIf
        _Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iBlockStartExcelRow][0], Default, True) ; Overwrite
        If @error Then Exit MsgBox($MB_ICONERROR, "Test-Script", "@error = " & @error & ", @extended = " & @extended & " when saving the workbook!")
        _Excel_BookClose($oWorkbook)
        $oWorkbook = _Excel_BookOpen($oExcel, $sInputPath)
        If MsgBox($MB_OKCANCEL, "Test-Script", "Column A changed from '" & $sPrevious & "' to '" & $aUsedRange[$iRow][0] & "'. " & $iRowCount & " rows processed." & @CRLF & _
                "Data has been copied to '" & $sPrevious & ".xlsx'. Please press 'OK' to continue or 'Cancel' to end the script.") = $IDCANCEL Then Exit
        $iRowCount = 0
        $iBlockStartExcelRow = $iRow
    EndIf
    $sPrevious = $aUsedRange[$iRow][0]
    $iRowCount = $iRowCount + 1
Next
; End of table reached. Add processing of the last block here
If $iBlockStartExcelRow > $iStartExcelRow Then
    _Excel_RangeDelete($oWorkbook.Worksheets(1), $iStartExcelRow & ":" & $iBlockStartExcelRow)         ; Delete all rows before the current block
EndIf
_Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iBlockStartExcelRow][0], Default, True)         ; Overwrite
If @error Then Exit MsgBox($MB_ICONERROR, "Test-Script", "@error = " & @error & ", @extended = " & @extended & " when saving the workbook!")
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)
MsgBox($MB_OKCANCEL, "Test-Script", "End of table reached. " & $iRowCount & " rows processed." & @CRLF & _
        "Data has been copied to '" & $sPrevious & ".xlsx'.")

 

[Taxyo]

I cannot thank you enough! 

 

I did some slight tweaking to my initial idea, and also noticed that some of the Vendor names contained illegal characters for file names (such as ":"). So I added a step where I first cleaned column A:A for illegal characters and then read that data to the array. The rest works wonders and that is obviously information you didn't know so you couldn't take that into account. 

 

This small project alone definitely highlights my biggest weakness right now which definitely is loop-management and nested "if/else" functions. 

Do you have any tips on resources to get me started from the basics regarding those? Or is it just trial and error until it "clicks"? 

 

Again, thanks a ton. Appreciate the help and believe me when I say that while I wish I understood more of what you did, I learned enough as to decipher what - I just wouldn't be able to replicate it nor think of the solution myself! 

 

[water]

A good place to start is the wiki (link can be found at the top of this page). There you'll find tons of tutorials

[water]

Skript with a bit of documentation

expandcollapse popup

#include <Excel.au3>

Global $sPrevious = ""                                       ; Value of column A of the previous row so we know that a change has happened
Global $iRowCount = 0                                        ; Number of row a block consists of (e.g. number of rows with "B" in column A)
Global $iStartExcelRow = 2                                   ; Row where the data lines start (means. row 1 is the column header, data starts at row 2)
Global $iBlockStartExcelRow = $iStartExcelRow                ; Variable to hold the first row of the currently processed block
Global $sInputPath = @ScriptDir & "\test.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
Global $aUsedRange = _Excel_RangeRead($oWorkbook)            ; Read all data from worksheet 1
; 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 $iRow = 1 To UBound($aUsedRange, 1) - 1
    ; Column A has changed. We now process the current block here
    If $sPrevious <> $aUsedRange[$iRow][0] And $sPrevious <> "" Then
        _Excel_RangeDelete($oWorkbook.Worksheets(1), $iRow + 1 & ":99999")                                    ; Delete all rows after the current block
        If $iBlockStartExcelRow > $iStartExcelRow Then
            _Excel_RangeDelete($oWorkbook.Worksheets(1), $iStartExcelRow & ":" & $iBlockStartExcelRow)        ; Delete all rows before the current block
        EndIf
        _Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iBlockStartExcelRow][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 MsgBox($MB_OKCANCEL, "Test-Script", "Column A changed from '" & $sPrevious & "' to '" & $aUsedRange[$iRow][0] & "'. " & $iRowCount & " rows processed." & @CRLF & _
                "Data has been copied to '" & $sPrevious & ".xlsx'. Please press 'OK' to continue or 'Cancel' to end the script.") = $IDCANCEL Then Exit
        $iRowCount = 0                                                                                        ; Reset the row count for the next block
        $iBlockStartExcelRow = $iRow                                                                          ; Save the row where the new block starts
    EndIf
    $sPrevious = $aUsedRange[$iRow][0]                                                                        ; Save the value of column A of the currently processed row beofre moving to the next row
    $iRowCount = $iRowCount + 1                                                                               ; Count the processed row
Next
; End of table reached. Add processing of the last block here
If $iBlockStartExcelRow > $iStartExcelRow Then
    _Excel_RangeDelete($oWorkbook.Worksheets(1), $iStartExcelRow & ":" & $iBlockStartExcelRow)                ; Delete all rows before the current block
EndIf
_Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iBlockStartExcelRow][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. " & $iRowCount & " rows processed." & @CRLF & _    ; Done!
        "Data has been copied to '" & $sPrevious & ".xlsx'.")

What I noticed while writing the documentation: The Excel workbook has to be sorted on column A. You might need to sort and save the sorted workbook at the start of the script.

[water]

What we now have is part one of your project
Part 2 would be to automate sending of the mails (easy if you have Outlook)
Part 3 is to retrieve the returned data and insert into the input script.

[Taxyo]

Wow, again you keep delivering!

 

And about the sorting; yeah I actually decided to break this down more easily, as the script is supposed to be usable by more persons than me I decided to add two functions and hotkeying them.

 

Where Hotkey1 manipulates/edits the excel-sheet to be ready for step 2. Which is your part of the function. So the initial Excel editing, adding column, removing illegal save file characters and also removing abundant Columns (now also sorting ascending) will be done before the main body of the script starts.  So that should do the trick

[Taxyo]

1 minute ago, water said:

What we now have is part one of your project
Part 2 would be to automate sending of the mails (easy if you have Outlook)
Part 3 is to retrieve the returned data and insert into the input script.

Part 2: We're sadly not using Outlook but a 3rd party program; I am fairly certain I could manage a program to automatically send the e-mails. Where I guess the main headache would be to match the correct attachment to the correct vendor. I assume I would need some sort of a master-list which maps "if the attachment is filename A = "Fill the To: with mailto: vendor A" So that would have to be built unless you know of a more clever way. That would be how I would solve that. Although as of now I want to have the manual control of who gets which data, Due to GDPR regulations sending the wrong data to the wrong vendor could have some repercussions I am not willing to risk on a program. 

Step 2 might therefore remain manual; or I might finish the mail-body+attachment and then add the human interaction to press "send". 

 

Part 3: Retrieving the data sadly can't be completely automated as there is no way to ensure when each vendor will reply; so I will probably just expand the current script with another hotkey which imports the data from the retreived workbook to the "Main file" which is used for importing to our system. That way we can import the updated data for each different replying vendor. 

[seadoggie01]

You could use an ini file for a simple lookup from filename to vendor name. And if you're concerned with mistakes, first code a message box yes/no that verifies if you want to send the email or not, then go back and put in all of your email creation. Eventually, you can comment out the message box once you find that it runs adequately.

; ...
$oMailItem.Display

If $IDNO = MsgBox($MB_YESNO, "EMail Verification", "Is the email okay to send?") Then Exit ConsoleWrite("! Bad email" @CRLF)

$oMailItem.Send
; ...

[Taxyo]

On 10/29/2019 at 3:32 PM, water said:

If needed you could protect parts of the workbook so the vendor is only able to fill in the delivery date and not modify/delete anything else.

Hey, 

I kept looking through the helpfile for all functions under _Excel, but couldn't find anything which specifically allowed me to Protect seperate cells; only on a workbook or sheet level. Is there a function which allows me to protect certain cells that I have missed which you perhaps can point me to for me to toy around with. I quite like the idea of being able to prevent the vendor from just winging it and typing their data in a made up column.

Thanks! 

[Taxyo]

2 hours ago, seadoggie01 said:

You could use an ini file for a simple lookup from filename to vendor name. And if you're concerned with mistakes, first code a message box yes/no that verifies if you want to send the email or not, then go back and put in all of your email creation. Eventually, you can comment out the message box once you find that it runs adequately.

; ...
$oMailItem.Display

If $IDNO = MsgBox($MB_YESNO, "EMail Verification", "Is the email okay to send?") Then Exit ConsoleWrite("! Bad email" @CRLF)

$oMailItem.Send
; ...

Thanks for the input! I'll definitely consider that when/if I decide to go towards automating the e-mailing as well. Knowing me I'll probably get that going within next week... 

[seadoggie01]

In regards to the protecting particular cells, you'll need to pretend your code is VBA... (or that's how I think of it). All cells by default will be locked when you lock the sheet, so you can do something like this:

; Get $oWs before this

; Unlock all cells
$oWs.Cells.Locked = False

; Lock Range A1 to Z42
$oWs.Range("A1:Z42").Locked = True

; Protect the sheet to enforce
$oWs.Protect("password")

(Check out the Worksheet.Protect method on MS Docs, there are more options than just the password)

[water]

23 minutes ago, Taxyo said:

I kept looking through the helpfile for all functions under _Excel, but couldn't find anything which specifically allowed me to Protect seperate cells;

The UDFs delivered with AutoIt only provide functions useful to most users.  But you can always extend the functionality by using the UDF functions and then go on by using the Excel COM yourself (The Excel UDF functions are just wrappers aroutn Excel COM).

You could manually lock the cells before starting the script (I'm not 100% sure locked cells can be processed by the script - never tried) or the script could lock the cells before creating the per vendor workbook.

What would you like to see?

Edited October 31, 2019 by water

[seadoggie01]

Quote

I'm not 100% sure locked cells can be processed by the script - never tried

Just tried, can read the cell, COM error on write (Error 4, Extended -2147352567)

[Taxyo]

23 minutes ago, seadoggie01 said:

In regards to the protecting particular cells, you'll need to pretend your code is VBA... (or that's how I think of it). All cells by default will be locked when you lock the sheet, so you can do something like this:

; Get $oWs before this

; Unlock all cells
$oWs.Cells.Locked = False

; Lock Range A1 to Z42
$oWs.Range("A1:Z42").Locked = True

; Protect the sheet to enforce
$oWs.Protect("password")

(Check out the Worksheet.Protect method on MS Docs, there are more options than just the password)

Alright,

So if I get this straight, bear with me as I am very new to actually coding and programming, whilst I have a basic understanding I am basically learning everything as I go.

What this would do is that I would first have to declare $oWorksheet (or $oWs in your example) as is already done prior in the script example above. And then by using $oWs."Then Speciy the object in VBA/COM" and that works just aswell? 

So in my example I want the entire workbook to be locked except for Column "B2:B9999999" (side note, is there any way to specify "B2 and onwards" instead of implying a range when I already know that I want to allow editing anywhere below B1?)

Then I might be able to do it by something like this?

;Assuming $oWs is declared to active sheet I presume?

$oWorkbook.ActiveSheet.Locked = True
$oWs.Range("B2:B999").Locked = False
$oWs.Protect("password")

Am I on the right track or would the statement of locking the entire sheet, then unlocking select rows contradict each other?

[water]

Yes and no
If cells are protected you can read them using Excel COM but you can't modify them (delete lines etc.). This means that the input Excel file needs to be unprotected. I now split the input file into pieces and then protect this worksheets.
Hence I modified the script I posted above a bit (jsut 3 lines which I marked with <==):

expandcollapse popup

#include <Excel.au3>

Global $sPrevious = ""                                       ; Value of column A of the previous row so we know that a change has happened
Global $iRowCount = 0                                        ; Number of row a block consists of (e.g. number of rows with "B" in column A)
Global $iStartExcelRow = 2                                   ; Row where the data lines start (means. row 1 is the column header, data starts at row 2)
Global $iBlockStartExcelRow = $iStartExcelRow                ; Variable to hold the first row of the currently processed block
Global $sInputPath = @ScriptDir & "\test.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
Global $aUsedRange = _Excel_RangeRead($oWorkbook)            ; Read all data from worksheet 1
; 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 $iRow = 1 To UBound($aUsedRange, 1) - 1
    ; Column A has changed. We now process the current block here
    If $sPrevious <> $aUsedRange[$iRow][0] And $sPrevious <> "" Then
        _Excel_RangeDelete($oWorkbook.Worksheets(1), $iRow + 1 & ":99999")                                    ; Delete all rows after the current block
        If $iBlockStartExcelRow > $iStartExcelRow Then
            _Excel_RangeDelete($oWorkbook.Worksheets(1), $iStartExcelRow & ":" & $iBlockStartExcelRow)        ; Delete all rows before the current block
        EndIf
        $oWorkbook.ActiveSheet.UsedRange.Locked = True                                                        ; <== Lock all used cells
        $oWorkbook.ActiveSheet.Range("B2:B999").Locked = False                                                ; <== Unlock column B (excluding B1)
        $oWorkbook.ActiveSheet.Protect("password")                                                            ; <== Use a password to protect the worksheet
        _Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iBlockStartExcelRow][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 MsgBox($MB_OKCANCEL, "Test-Script", "Column A changed from '" & $sPrevious & "' to '" & $aUsedRange[$iRow][0] & "'. " & $iRowCount & " rows processed." & @CRLF & _
                "Data has been copied to '" & $sPrevious & ".xlsx'. Please press 'OK' to continue or 'Cancel' to end the script.") = $IDCANCEL Then Exit
        $iRowCount = 0                                                                                        ; Reset the row count for the next block
        $iBlockStartExcelRow = $iRow                                                                          ; Save the row where the new block starts
    EndIf
    $sPrevious = $aUsedRange[$iRow][0]                                                                        ; Save the value of column A of the currently processed row beofre moving to the next row
    $iRowCount = $iRowCount + 1                                                                               ; Count the processed row
Next
; End of table reached. Add processing of the last block here
If $iBlockStartExcelRow > $iStartExcelRow Then
    _Excel_RangeDelete($oWorkbook.Worksheets(1), $iStartExcelRow & ":" & $iBlockStartExcelRow)                ; Delete all rows before the current block
EndIf
_Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iBlockStartExcelRow][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. " & $iRowCount & " rows processed." & @CRLF & _    ; Done!
        "Data has been copied to '" & $sPrevious & ".xlsx'.")

 

Edited October 31, 2019 by water

[Taxyo]

Aww. I wanted to post my example before you got the chance, but it shows that I really was on the right track - I am actually right now doing the exact same modifications to the code to try it out.

I started by just making the code as a separate function to see if I could even get it to work and then it was just a matter of me guessing that I should protect the data before the SaveAs line. 

I did some reading on VBA and object handling in excel and realized that there are so many more things I can automate just by looking at the different objects and manipulating them instead of having to repeat a lot of actions. 

 

Now could I ask for some pointers, or some directions on how you would approach the "automation of e-mails". In this case I wont be able to use outlook so I guess I'll have to automate the process a bit more "manual" for the application we're using at my office. We're using a program called "Focalscope". 

My current "brainstormed" approach is something to the point of

1. The separated workbooks will each day consist of different vendors depending on our current open lines. So I will have to have some sort of logic which determines which e-mail adress I use based on the name of the attached file.

2. Focalscope seems to be very unresponsive to "Auto IT window helper" so I can't get any help there - and I am not very keen on the idea of having a script based on mouse coordinates: Although the program itself has many Hotkeys which I assume I could reach by simulating keystrokes. 

3. As of now the thought process goes:
Everytime I run the "Auto-email" part of the script I probably need to grab all the current file-names in the "Vendor folder" (@Scriptdir\Vendors) where each workbook is. And read them to either a string or an array? (I guess an array?) 

And then use hotkeys/navigation to open a new e-mail, navigate to "attach file" and then enter the first file-name in the attach-window from the array. Each file name would also have to be indexed against each vendors e-mail adress and from there on I guess it would grab the corresponding e-mail - fill in the "To" field. And then press send.

Then the next time an e-mail opens, the initial process would be the same but it would use the 2nd file name found in the array; and match that file name to the corresponding e-mail.

..

 

Thats my idea at least.. Am I over-complicating it? I feel thats something I tend to do when brainstorming. 

[seadoggie01]

For sending an email, check out this UDF by Jos. Should make your life loads easier  

 

[Taxyo]

1 minute ago, seadoggie01 said:

For sending an email, check out this UDF by Jos. Should make your life loads easier  

 

I would love to be able to use this, but sadly our application for e-mailing is also used for ticket and agent management for my entire team. And I do not think this UDF will create the mail as if it was sent from the application more so than it will "generate the email based on the parameters"? Or am I wrong? 

[seadoggie01]

If you can get these parameters of your email... (server? application? I'm not sure what word is right), then I think you'll be able to send them, regardless of what else you use the email application for

$SmtpServer = "MailServer"              ; address for the smtp-server to use - REQUIRED
$IPPort = 25                            ; port used for sending the mail
$ssl = 0                                ; enables/disables secure socket layer sending - put to 1 if using httpS

and you can just try ssl with True/False if you don't know it, that's what I did.  I would expect that you can find the provider of your email and get the above from them or the internet in general.

[Taxyo]

@water Hi, I've been running the script for a week and today I ran into some issues I haven't had before. Not sure if you maybe could try to help me as to what is the issue?

 

Edit1: It seems to be related to the protect workbook command line, when thats commented I am not getting the issue. So how would I ensure that I protect the sheet, save as "new name" then unprotect the sheet to further the script, whilst ensuring that the saved file is protected? Do I parse a new line for unprotecting the sheet after the "save as" line? 

 

When running the "test" script it parses the first part, splitting the document based on "Column A being the same values", edits the excel file, saves as "based on name in Column A". But then where it previously "reverted to the documents original state, and went on to processing the next block" I get this error:

"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (227) : ==> Variable must be of type "Object".:
$oExcel.Windows($oWorkbook.Name).Visible = $bVisible
$oExcel.Windows($oWorkbook.Name)^ ERROR
 

Which seems to point to an error in the Excel.au3 file and not the script itself. 

Any guess as to why it's doing this? 

Ill paste my entire code so you know what I've been working with: 

Func Hotkey1()  should just be manipulating the initial file to the raw document which we later want to process and split up in files; and removing "illegal file name characters" since some Vendor names we've had from our systems have "." and "/" in the name and that messes up with the saving. 

 

expandcollapse popup

#include <Excel.au3>
HotKeySet("{NUMPAD1}", Hotkey1)
HotKeySet("{NUMPAD2}", Hotkey2)

HotKeySet("{ESC}", Terminate)
Global $sPrevious = "", $iRowCount = 0, $iStartExcelRow = 2, $iBlockStartExcelRow = $iStartExcelRow, $iStartColumn = 0, $iEndColumn = 7
Global $sInputPath = @ScriptDir & "\master.xlsx", $sSavePath = @ScriptDir & "\Vendor"
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, $sInputPath)
;~    _Excel_RangeSort($oWorkbook, Default, Default, Default, Default, Default, $xlYes, Default, Default)


While 1
   sleep(100)
WEnd


Func Hotkey1()

   _Excel_RangeDelete($oWorkbook.Activesheet, "H:ZZ")
   _Excel_RangeInsert($oWorkbook.activesheet, "B:B")
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Shipping Date", "B1")
   _Excel_RangeReplace($oWorkbook, Default, Default, ":", "")
   _Excel_RangeReplace($oWorkbook, Default, Default, ".", "")
   _Excel_RangeReplace($oWorkbook, Default, Default, "&", "")
   _Excel_RangeReplace($oWorkbook, Default, Default, "_", "")
   _Excel_RangeReplace($oWorkbook, Default, Default, "/", "")
   _Excel_RangeReplace($oWorkbook, Default, Default, "\", "")
   _Excel_RangeSort($oWorkbook, Default, Default, "A1:H999", Default, Default, $xlYes, Default, Default)
   _Excel_BookSave($oWorkbook)
   Global $aUsedRange = _Excel_RangeRead($oWorkbook)
   MsgBox(1, "Success", "Data in sheet manipulated for step 2")
EndFunc



Func Hotkey2()
Global $aUsedRange = _Excel_RangeRead($oWorkbook)
_ArraySort($aUsedRange, 0, 1) ; Sort ascending on column 0 (first column), ignores header line
_ArrayDisplay($aUsedRange)

For $iRow = 1 To UBound($aUsedRange, 1) - 1
    ; Column A has changed. Add processing of the previous block here
    If $sPrevious <> $aUsedRange[$iRow][0] And $sPrevious <> "" Then
        _Excel_RangeDelete($oWorkbook.Worksheets(1), $iRow + 1 & ":99999") ; Delete all rows after the current block
        If $iBlockStartExcelRow > $iStartExcelRow Then
            _Excel_RangeDelete($oWorkbook.Worksheets(1), $iStartExcelRow & ":" & $iBlockStartExcelRow) ; Delete all rows before the current block
         EndIf
        Local $oWs = $oWorkbook.ActiveSheet
        $oWs.Cells.Locked = True
        $oWs.Range("B2:B10000").Locked = False
        $oWs.Protect("password")
        _Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iBlockStartExcelRow][0], Default, True) ; Overwrite
        If @error Then Exit MsgBox($MB_ICONERROR, "Test-Script", "@error = " & @error & ", @extended = " & @extended & " when saving the workbook!")
        _Excel_BookClose($oWorkbook)
        $oWorkbook = _Excel_BookOpen($oExcel, $sInputPath)
        If MsgBox($MB_OKCANCEL, "Test-Script", "Column A changed from '" & $sPrevious & "' to '" & $aUsedRange[$iRow][0] & "'. " & $iRowCount & " rows processed." & @CRLF & _
                "Data has been copied to '" & $sPrevious & ".xlsx'. Please press 'OK' to continue or 'Cancel' to end the script.") = $IDCANCEL Then Exit
        $iRowCount = 0
        $iBlockStartExcelRow = $iRow
    EndIf
    $sPrevious = $aUsedRange[$iRow][0]
    $iRowCount = $iRowCount + 1
Next
; End of table reached. Add processing of the last block here
If $iBlockStartExcelRow > $iStartExcelRow Then
    _Excel_RangeDelete($oWorkbook.Worksheets(1), $iStartExcelRow & ":" & $iBlockStartExcelRow)         ; Delete all rows before the current block
EndIf
_Excel_BookSaveAs($oWorkbook, $sSavePath & "\" & $aUsedRange[$iBlockStartExcelRow][0], Default, True)         ; Overwrite
If @error Then Exit MsgBox($MB_ICONERROR, "Test-Script", "@error = " & @error & ", @extended = " & @extended & " when saving the workbook!")
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)
MsgBox($MB_OKCANCEL, "Test-Script", "End of table reached. Press ESC to Terminate script")
EndFunc



Func Terminate()
    Exit
 EndFunc

 

Thanks in advance. 

Edited November 6, 2019 by Taxyo
Deeper thought