Excel data transfer Script not working

[Mindfork]

Hi everyone,

I am working on another Excel-data transfer script, but this time I have complete lost it... and need your help.

I want to create a script that automates the process of transferring the "Units Ordered" from one Excel data "BusinessReport" (from column "O") to another "Weekly Sales".

Here's how I handle the process:

First: Country Selection: I ask the user to select a country via a graphical interface. This selection is crucial as it determines in which column in the Excel file "Weekly sales" the Units Ordered from BusinessReport will be inserted.

The "Units Ordered" should be assigned on the basis of the following criteria:

The script starts to run through each row in "BusinessReport" to collect the ASIN and SKU data for comparison with the ASIN and SKU from "Weekly Sales".
It is important to dynamically check in "BusinessReport" whether the rows have content because the number of rows from this report is always generated individually. In contrast, the rows in the Excel file "Weekly sales" with the data in it are fixed by me.

( BusinessReport has the ASIN in column "B", the SKU in column "D" and the "Units Ordered" in Column "O". Weekly Sales has the ASIN in colum "B" and the SKU in column "A". )

For each row in "BusinessReport", it will then cycle through each row in "Weekly Sales" to look for an ASIN match. If an ASIN in "BusinessReport" matches with an ASIN in "Weekly Sales", it will proceed to the SKU check >Note: SKU endings on "-B" in BusinessReport are completely ignored<

If the ASINs in both columns from the Excel lists match, the script checks the SKU endings:

- If the SKU in "BusinessReport" ends with "-FBA" and the corresponding SKU in "Weekly Sales" also ends with "-FBA", the script writes the amount from "Ordered Units" from BusinessReport to the predefined column of the selected country in the same row where the check was successful.

- If the SKU in "Weekly Sales" does not end in "-FBA", the script searches for an SKU in "BusinessReport" that also does not end in "-FBA" and also does not end in "-B". The script then writes the amount from "Units ordered" from BusinessReport to the predefined column based on the selected country in the same row where the check was successful.

What comes before the ending of the SKU is not relevant for this comparison. It is only important that the ending in both SKUs either ends in "-FBA" or neither ends in "-FBA" and in "-B" and that the ASIN is the same.

I am currently using the following code and I don't know where the error is in this. I have also changed the code so many times that I don't even know if it has completely broken down by now. In any case, I am stuck:

Code: Select all

#SingleInstance force
Global vCountryChoice  ; Declare the variable as global

; Define the hotkey to start the script
F2::startScript()

; Start the script function
startScript() {
    ; Get an instance of Excel
    excel := ComObjActive("Excel.Application")

    ; Get the workbooks
    workbookBR := getWorkbookByName(excel, "BusinessReport")
    workbookWV := getWorkbookByName(excel, "Weekly Sales")

    if !workbookBR {
        MsgBox, % "BusinessReport workbook not found."
        return
    } else {
        MsgBox, % "BusinessReport workbook successfully loaded."
    }

    if !workbookWV {
        MsgBox, % "Weekly Sales workbook not found."
        return
    } else {
        MsgBox, % "Weekly Sales workbook successfully loaded."
    }

    ; GUI for country selection (display GUI if everything is correct)
    Gui, New
    Gui, Add, DropDownList, v%vCountryChoice%, Germany|USA|Canada|Australia|Japan|United Arab Emirates
    Gui, Add, Button, Default, OK
    Gui, Show,, Country Selection
    return
}

ButtonOK:
    Gui, Submit  ; Get the user input from the GUI.
    countryColumn := getCountryColumn(vCountryChoice)  ; Get the column based on the selected country
	MsgBox, % "Country: " vCountryChoice " Column: " countryColumn
    Gui, Destroy  ; Close the GUI.

    transferData(workbookBR, workbookWV, countryColumn)
return

; Function to transfer data based on the country
transferData(workbookBR, workbookWV, countryColumn) {
    worksheetBR := workbookBR.Sheets(1)
    worksheetWV := workbookWV.Sheets(1)

    ; Assuming data in BusinessReport starts from row 2
    rowBR := 2
    while (rowBR <= worksheetBR.UsedRange.Rows.Count && worksheetBR.Cells(rowBR, 2).Value != "") {
        ASINBR := worksheetBR.Cells(rowBR, 2).Value  ; ASIN in "BusinessReport"
        SKUBR := worksheetBR.Cells(rowBR, 4).Value  ; SKU in "BusinessReport"
        MsgBox, % "BusinessReport - Row " rowBR " - ASIN: " ASINBR " SKU: " SKUBR
        rowWV := 8  ; Start from row 8 in "Weekly Sales"
        while (rowWV <= 75 && (worksheetWV.Cells(rowWV, 1).Value != "" || worksheetWV.Cells(rowWV, 2).Value != "")) {
            ASINWV := worksheetWV.Cells(rowWV, 1).Value  ; ASIN in "Weekly Sales"
            SKUWV := worksheetWV.Cells(rowWV, 2).Value  ; SKU in "Weekly Sales"
            MsgBox, % "Weekly Sales - Row " rowWV " - ASIN: " ASINWV " SKU: " SKUWV
            if (ASINBR = ASINWV) {
                if (isSKUEndingMatched(SKUBR, SKUWV)) {
                    unitsOrdered := worksheetBR.Cells(rowBR, 15).Value
                    MsgBox, % "Match found - Transfer units: " unitsOrdered " by row" rowWV
                    worksheetWV.Cells(rowWV, countryColumn).Value := unitsOrdered
                }
            }
            rowWV++  ; Go to the next row in "Weekly Sales"
        }
        rowBR++  ; Go to the next row in "BusinessReport"
    }
}

; Function to check if SKU endings match
isSKUEndingMatched(SKUBR, SKUWV) {
    return (SubStr(SKUBR, -4) = "-FBA" && SubStr(SKUWV, -4) = "-FBA") || ((SubStr(SKUBR, -2) != "-B" && SubStr(SKUWV, -2) != "-B") && SubStr(SKUBR, -4) != "-FBA")
}

; Function to get the column based on the country
getCountryColumn(country) {
    ColumnMapping := {"Germany": "E", "USA": "I", "Canada": "M", "Australia": "Q", "Japan": "U", "United Arab Emirates": "U"}
    return ColumnMapping[country]
}

; Function to get a workbook by its name
getWorkbookByName(excel, nameStart) {
    Loop, % excel.Workbooks.Count {
        workbook := excel.Workbooks(A_Index)
        if (InStr(workbook.Name, nameStart))
            return workbook
    }
    return ""  ; Return empty string if no matching workbook is found.
}

Can you please help me where I am going wrong and what I need to change?

Btw, this was the original script where I startet everything from:

Code: Select all

#SingleInstance force
Global vCountryChoice  ; Deklarieren der Variable als global

; Define the hotkey to start the script
F2::startScript()

; Start the script function
startScript() {
    ; Get an instance of Excel
    excel := ComObjActive("Excel.Application")

    ; Get the workbooks
    workbookBR := getWorkbookByName(excel, "BusinessReport")
    workbookWV := getWorkbookByName(excel, "Wöchentliche Verkäufe")
; Test des Zugriffs auf ein bekanntes Arbeitsblatt und Ausgabe eines bekannten Zellwerts
    if (workbookBR) {
        worksheetBR := workbookBR.Sheets(1)
        rowCountBR := worksheetBR.UsedRange.Rows.Count
        MsgBox, % "rowCountBR: " rowCountBR " - Erster ASIN-Wert in BusinessReport: " worksheetBR.Cells(2, 2).Value
    } else {
        MsgBox, % "BusinessReport Arbeitsmappe nicht gefunden."
    }

    if (workbookWV) {
        worksheetWV := workbookWV.Sheets(1)
        rowCountWV := worksheetWV.UsedRange.Rows.Count
        MsgBox, % "rowCountWV: " rowCountWV " - Erster ASIN-Wert in Wöchentliche Verkäufe: " worksheetWV.Cells(2, 2).Value
    } else {
        MsgBox, % "Wöchentliche Verkäufe Arbeitsmappe nicht gefunden."
    }
    ; GUI for country selection
    Gui, New
    Gui, Add, DropDownList, v%vCountryChoice%, Deutschland|USA|Kanada|Australien|Japan|Arabische Emirate
    Gui, Add, Button, Default, OK
    Gui, Show,, Länderauswahl
    return
}

ButtonOK:
    Gui, Submit  ; Get the user input from the GUI.
    countryColumn := getCountryColumn(vCountryChoice)  ; Get the column based on the selected country
    Gui, Destroy  ; Close the GUI.

    transferData(workbookBR, workbookWV, countryColumn)
return


; Function to transfer data based on the country
transferData(workbookBR, workbookWV, countryColumn) {
    worksheetBR := workbookBR.Sheets(1)
    worksheetWV := workbookWV.Sheets(1)
    rowCountBR := worksheetBR.UsedRange.Rows.Count
    rowCountWV := worksheetWV.UsedRange.Rows.Count

   MsgBox, % "rowCountBR: " rowCountBR " rowCountWV: " rowCountWV  ; Debugging-Information

    ; Iterate through "BusinessReport" to find matching ASIN and SKU
    Loop, %rowCountBR% {
        rowBR := A_Index
        ASINBR := worksheetBR.Cells(rowBR, 2).Value  ; ASIN in "BusinessReport"
        SKUBR := worksheetBR.Cells(rowBR, 4).Value  ; SKU in "BusinessReport"
        unitsOrdered := worksheetBR.Cells(rowBR, 15).Value  ; Ordered units in "BusinessReport"

        MsgBox, % "ASINBR: " ASINBR " SKUBR: " SKUBR " unitsOrdered: " unitsOrdered  ; Debugging-Information

        ; Iterate through "Wöchentliche Verkäufe" to find matching ASIN and then check for SKU ending
        Loop, %rowCountWV% {
            rowWV := A_Index
            ASINWV := worksheetWV.Cells(rowWV, 2).Value  ; ASIN in "Wöchentliche Verkäufe"

            if (ASINBR = ASINWV) {
                SKUWV := worksheetWV.Cells(rowWV, 1).Value  ; SKU in "Wöchentliche Verkäufe"
                if (isSKUEndingMatched(SKUBR, SKUWV)) {
                    worksheetWV.Cells(rowWV, countryColumn).Value := unitsOrdered
					MsgBox, % "Übereinstimmung gefunden: " ASINBR " in Zeile " rowBR " und " rowWV " unitsOrdered: " unitsOrdered  ; Debugging-Information
                }
            }
        }
    }
}

; Function to check if SKU endings match
isSKUEndingMatched(SKUBR, SKUWV) {
    return (SubStr(SKUBR, -4) = "-FBA" && SubStr(SKUWV, -3) = "FBA") || ((SubStr(SKUBR, -2) != "-B" && SubStr(SKUWV, -1) != "B") && SubStr(SKUBR, -4) != "-FBA")
}

; Function to get the column based on the country
getCountryColumn(country) {
    ColumnMapping := {"Deutschland": "E", "USA": "I", "Kanada": "M", "Australien": "Q", "Japan": "U", "Arabische Emirate": "U"}
    return ColumnMapping[country]
}

; Function to get a workbook by its name
getWorkbookByName(excel, nameStart) {
    Loop, % excel.Workbooks.Count {
        workbook := excel.Workbooks(A_Index)
        if (InStr(workbook.Name, nameStart))
            return workbook
    }
    return ""  ; Return empty string if no matching workbook is found.
}

GuiControlGetText(Control) {
    GuiControlGet, CurrentValue,, %Control%
    return CurrentValue
}

[Mindfork]

I was able to find some solution, well at leats it looks like its working.

(Information: I have noticed that the script does not work with an Excel .csv file. I would like to make the xls file available, but unfortunately the forum does not allow it. Can I make it available otherwise if required?)

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
#Warn  ; Enable warnings to help identify potential issues.
SetWorkingDir %A_ScriptDir%  ; Sets the working directory to the script's directory.
SendMode Input  ; Sets the method for sending keystrokes to 'Input'.
#SingleInstance force  ; Ensures that only one instance of the script is running.
SetBatchLines -1  ; Maximizes script performance by running it as fast as possible.
ListLines Off  ; Disables the listing of executed lines for performance.

try {
    xl := ComObjActive("Excel.Application")  ; Attempts to get an active instance of Excel.
} catch {
    MsgBox, 48, Error, No active Excel instance found. Please open Excel before running this script.
    return ; Exits if Excel is not open.
}
xl.Visible := true  ; Makes Excel visible  to the user and to track changes.

; Function to retrieve a workbook by part of its name
getWorkbookByName(excel, namePart) {
    Loop, % excel.Workbooks.Count { 
        workbook := excel.Workbooks.Item(A_Index) ; Loops through all open workbooks.
        If (InStr(workbook.Name, namePart) > 0) ; Checks if workbook name contains the specified part.
            return workbook ; Returns the workbook if found.
    }
    return ""  ; Returns an empty string if no matching workbook is found
}

F2::  ; Hotkey to start the script
    ; Attempts to connect to an Excel instance and checks if specific workbooks are open
    try {
        excel := ComObjActive("Excel.Application")
    } catch {
        MsgBox, % "Please open Excel and the Excel files 'BusinessReport' and 'WeeklySales'."
        return
    }
	
	; Checks if specific workbooks are open.
    workbookBR := getWorkbookByName(excel, "BusinessReport")
    workbookVA := getWorkbookByName(excel, "WeeklySales")

	; Generates an error message if the workbooks are missing.

    If (!workbookBR && !workbookVA) {
        MsgBox, % "Please open the Excel files 'BusinessReport' and 'WeeklySales'."
        return
    } else if (!workbookBR) {
        MsgBox, % "Please open the Excel file 'BusinessReport'."
        return
    } else if (!workbookVA) {
        MsgBox, % "Please open the Excel file 'WeeklySales'."
        return
    }

    ; Validate the header in column "O" of the BusinessReport
    worksheetBR := workbookBR.Sheets(1)
    header := worksheetBR.Cells(1, 15).Value
    If (header != "Units Ordered") {
        MsgBox, % "The header in column 'O' of the 'BusinessReport' workbook does not match the expected 'Units Ordered'. Please check the file."
        return
    }
	
   ; GUI definition with customized design
	Gui, New
    Gui, Font, s10 cBlack, Arial  ; Set font and color
    Gui, Color, FFFFFF  ; Set background color
    Gui, Add, Text, x10 y10 w360 h30 +Center, Bitte geben Sie das Land für den 'BusinessReport' ein: ; Show the GUI with the title "Select a Country".
	Gui, Add, DropDownList, x10 y50 w360 h300 vCountryChoice, Deutschland|Frankreich|Kanada ; Add a dropdown list with options.
	Gui, Add, Button, x10 y100 w360 h30 Default, OK  ; Add an OK button to submit the selection.
    Gui, Show, w380 h150,  Wöchentliche Verkaufsstatistik

return

ButtonOK:
    Gui, Submit  ; Retrieve the data from the GUI.
    global selectedCountry := CountryChoice  ; Store the selected country from the dropdown.

    Gui, Destroy  ; Destroy the GUI after the selection.


; Setup GUI for progress bar
	Gui, ProgressGui: New, +AlwaysOnTop  +Owner
	Gui, ProgressGui: Color, White
	Gui, ProgressGui: Font, s12 cBlack, Verdana
	Gui, ProgressGui: Add, Text, x10 y10 w300 h20 +Center, Verarbeitung läuft...
	Gui, ProgressGui: Add, Progress, x10 y40 w280 h20 vProgress cLime ; gProgressChanged (wrong Control element)
	Gui, ProgressGui: Show, w300 h70, Gleich geschafft


    ; Continue with the existing script.
    WeeklySales := getWorkbookByName(xl, "WeeklySales")

; Define workbook and column settings based on country. The ternary operator (?:) is necessary to select between two values based on a condition. I can not omit it, because the code will not work properly. AutoHotkey will not recognize how the conditions should be evaluated and the values assigned.
; Without the ternary operator, I cannot perform the selection directly in a single line. Instead, I would have to use traditional if-else statements to implement the same logic. 
; reportName := (selectedCountry = "Frankreich") ? "Hagebutte" : 
				;(selectedCountry = "NeuesLand1") ? "NeueDatei1" :
				;(selectedCountry = "NeuesLand2") ? "NeueDatei2" :
				;"BusinessReport"
; asinColumn := (selectedCountry = "Frankreich") ? "A" : "B"
; skuColumn := (selectedCountry = "Frankreich") ? "L" : "D"
; unitsOrderedColumn := (selectedCountry = "Frankreich") ? "G" : "O"



; Traditional if-else statements 

; Assume selectedCountry is set somewhere in the GUI
; Start the assignment based on the selected country
if (selectedCountry = "Frankreich") {
    reportName := "Hagebutte"
    asinColumn := "A"
    skuColumn := "L"
    unitsOrderedColumn := "G"
} else if (selectedCountry = "Deutschland") {
    reportName := "BusinessReport"
    asinColumn := "B"
    skuColumn := "D"
    unitsOrderedColumn := "O"
} else if (selectedCountry = "Kanada") {
    reportName := "BusinessReport"
    asinColumn := "B"
    skuColumn := "D"
    unitsOrderedColumn := "O"
} else if (selectedCountry = "NeuesLand2") {
    reportName := "NeueDatei2"
    asinColumn := "D"
    skuColumn := "F"
    unitsOrderedColumn := "Q"
} else {
    MsgBox , % "No Country matches the Reports, please change the Country or open the associated report." ; Standardwert, wenn keine anderen Bedingungen zutreffen
  
}

; Attempt to open the selected report workbook
reportWorkbook := getWorkbookByName(xl, reportName)
if (!reportWorkbook) {
    MsgBox, % "Could not find the workbook '" reportName "'. Please ensure it is open."
    return
}

    ; BusinessReport := getWorkbookByName(xl, "BusinessReport") ; duplicate or necessary?
   ; WeeklySales := getWorkbookByName(xl, "WeeklySales") ; duplicate or necessaryne

	; Loops through all rows in the WeeklySales sheet.
	
	BRSheet := reportWorkbook.Sheets(1)
	;WSSheet := WeeklySales.Sheets(1)
	WSSheet := getWorkbookByName(xl, "WeeklySales").Sheets(1)
	BRRows := BRSheet.UsedRange.Rows.Count
	WSRows := WSSheet.UsedRange.Rows.Count
    countryColumn := { "Deutschland": "D", "Frankreich": "H", "Kanada": "L" } ; Defines country columns for data entry based on user selection.
    ; selectedCountry := "Deutschland"  ; This should be selected dynamically via a GUI.

    ; Data processing loop
    Loop, % WSRows-1 {
        wsrow := A_Index + 1
        ASIN_WS := Trim(WSSheet.Range("B" . wsrow).Value) ; Gets the ASIN from WeeklySales.
        SKU_WS := Trim(WSSheet.Range("A" . wsrow).Value)  ; Gets the SKU from WeeklySales.
        noMatchFound := true ; Flag to check if a match is found.

    if (ASIN_WS = "" && RegExMatch(SKU_WS, "i)-FBA$")) {
        continue  ; Überspringen dieser Zeile, wenn keine ASIN vorhanden ist und die SKU auf "-FBA" endet
    }
        ; Loops through all rows in the Business Report sheet.
        Loop, % BRRows-1 {
            row := A_Index + 1
            ASIN_BR := Trim(BRSheet.Range(asinColumn . row).Value) ; Gets the ASIN based on selected country.
            SKU_BR := Trim(BRSheet.Range(skuColumn . row).Value) ; Gets the SKU based on selected country.
            UnitsOrdered := BRSheet.Range(unitsOrderedColumn . row).Value ; Gets the Units Ordered based on selected country.

            ; if (RegExMatch(SKU_BR, ".*-B$")) {; What was the Differenze between i and .* ...?
			            
			  if (RegExMatch(SKU_BR, "i)-B$")) { ; Skips entries where SKU ends with '-B'.
               ; MsgBox, % "SKU `" SKU_BR "` ends with '-B' and will be skipped."
                continue
            }

            ; if (ASIN_BR = ASIN_WS && ((RegExMatch(SKU_BR, ".*-FBA$") && RegExMatch(SKU_WS, ".*-FBA$")) || (!RegExMatch(SKU_BR, ".*(-FBA|-B)$") && !RegExMatch(SKU_WS, ".*(-FBA|-B)$")))) {  ; Old working Code.
			
			; Checks for matching ASINs and SKUs, respecting the '-FBA' notation.
			 if (ASIN_BR = ASIN_WS && ((RegExMatch(SKU_BR, "i)-FBA$") && RegExMatch(SKU_WS, "i)-FBA$")) || (!RegExMatch(SKU_BR, "i).*(-FBA|-B)$") && !RegExMatch(SKU_WS, "i).*(-FBA|-B)$")))) { ; new one with i instead of .*
				 WSSheet.Range(countryColumn[selectedCountry] . wsrow).Value := UnitsOrdered
                ; MsgBox, % "Orders for ASIN `" ASIN_BR "` and SKU `" SKU_BR "` have been entered in row " wsrow " of 'WeeklySales' in column `" countryColumn[selectedCountry] "`."
                 noMatchFound := false
                 break   ; Stops the loop once a match is found.
            }
        }

       ; if (noMatchFound) { ; old code withouth checken for empty spaces of ASIN in WeeklySales
	   
		;If no match is found, enters '0' for that ASIN in WeeklySales.
		if (noMatchFound && ASIN_WS != "") { 
            WSSheet.Range(countryColumn[selectedCountry] . wsrow).Value := 0
            ;MsgBox, % "No match found for ASIN `" ASIN_WS "` in 'BusinessReport'. A '0' has been entered in 'WeeklySales' row " wsrow "."
        }
		; Update the progress in the GUI
        ProgressUpdate((A_Index * 100) / WSRows)
    }
; Completion message and clean-up
    Gui, ProgressGui: Destroy
    MsgBox, % "Data transfer with ASIN match completed."
    Gui, Destroy
   
return

GuiClose:
    Gui, ProgressGui: Destroy
return

; Function to update the progress bar in the GUI
ProgressUpdate(progress) {
    global
    GuiControl, ProgressGui:, Progress, %progress%
    if (progress = 100) {
        Gui, ProgressGui: Destroy
        MsgBox, % "Datenübertragung abgeschlossen."
    }
}

F10::ExitApp  ; Hotkey to exit the script.
F11::Suspend  ; Hotkey to disable/enable all hotkeys and hotstrings.
F12::Reload  ; Hotkey to reload the script.

Can somebody please be so nice to take a look at the code if it is written cleanly or "dirty" ?



A problem that I know of is that when I accidentally click in an Excel row, the code crashes. It doesn't exit and once I'm out of the excel row I can run the code normally, but I don't know how to solve this.

I get several error messages, all starting with
Error: 0x80010001 Call was rejected by called party.

Screenshot 2024-04-13 162534.png (21.46 KiB) Viewed 98 times

Just one thing out of curiosity. I am also having two more version, basically for performance and Stability - at least what the chatbot told me

ScreenUpdating

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
#Warn  ; Enable warnings to help identify potential issues.
SetWorkingDir %A_ScriptDir%  ; Sets the working directory to the script's directory.
SendMode Input  ; Sets the method for sending keystrokes to 'Input'.
#SingleInstance force  ; Ensures that only one instance of the script is running.
SetBatchLines -1  ; Maximizes script performance by running it as fast as possible.
ListLines Off  ; Disables the listing of executed lines for performance.

try {
    xl := ComObjActive("Excel.Application")
    xl.ScreenUpdating := false  ; Deaktiviere die Bildschirmaktualisierung für die Dauer des Skripts
} catch {
    MsgBox, 48, Error, No active Excel instance found. Please open Excel before running this script.
    return ; Exits if Excel is not open.
}

; Function to retrieve a workbook by part of its name
getWorkbookByName(excel, namePart) {
    Loop, % excel.Workbooks.Count { 
        workbook := excel.Workbooks.Item(A_Index) ; Loops through all open workbooks.
        If (InStr(workbook.Name, namePart) > 0) ; Checks if workbook name contains the specified part.
            return workbook ; Returns the workbook if found.
    }
    return ""  ; Returns an empty string if no matching workbook is found
}

F2::  ; Hotkey to start the script
    ; Attempts to connect to an Excel instance and checks if specific workbooks are open
    try {
        excel := ComObjActive("Excel.Application")
    } catch {
        MsgBox, % "Please open Excel and the Excel files 'BusinessReport' and 'WeeklySales'."
        return
    }
	
	; Checks if specific workbooks are open.
    workbookBR := getWorkbookByName(excel, "BusinessReport")
    workbookVA := getWorkbookByName(excel, "WeeklySales")

	; Generates an error message if the workbooks are missing.

    If (!workbookBR && !workbookVA) {
        MsgBox, % "Please open the Excel files 'BusinessReport' and 'WeeklySales'."
        return
    } else if (!workbookBR) {
        MsgBox, % "Please open the Excel file 'BusinessReport'."
        return
    } else if (!workbookVA) {
        MsgBox, % "Please open the Excel file 'WeeklySales'."
        return
    }

    ; Validate the header in column "O" of the BusinessReport
    worksheetBR := workbookBR.Sheets(1)
    header := worksheetBR.Cells(1, 15).Value
    If (header != "Units Ordered") {
        MsgBox, % "The header in column 'O' of the 'BusinessReport' workbook does not match the expected 'Units Ordered'. Please check the file."
        return
    }
	
   ; GUI definition with customized design
	Gui, New
    Gui, Font, s10 cBlack, Arial  ; Set font and color
    Gui, Color, FFFFFF  ; Set background color
    Gui, Add, Text, x10 y10 w360 h30 +Center, Bitte geben Sie das Land für den 'BusinessReport' ein: ; Show the GUI with the title "Select a Country".
	Gui, Add, DropDownList, x10 y50 w360 h300 vCountryChoice, Deutschland|Frankreich|Kanada ; Add a dropdown list with options.
	Gui, Add, Button, x10 y100 w360 h30 Default, OK  ; Add an OK button to submit the selection.
    Gui, Show, w380 h150,  Wöchentliche Verkaufsstatistik

return

ButtonOK:
    Gui, Submit  ; Retrieve the data from the GUI.
    global selectedCountry := CountryChoice  ; Store the selected country from the dropdown.

    Gui, Destroy  ; Destroy the GUI after the selection.


; Setup GUI for progress bar
	Gui, ProgressGui: New, +AlwaysOnTop  +Owner
	Gui, ProgressGui: Color, White
	Gui, ProgressGui: Font, s12 cBlack, Verdana
	Gui, ProgressGui: Add, Text, x10 y10 w300 h20 +Center, Verarbeitung läuft...
	Gui, ProgressGui: Add, Progress, x10 y40 w280 h20 vProgress cLime ; gProgressChanged (wrong Control element)
	Gui, ProgressGui: Show, w300 h70, Gleich geschafft


    ; Continue with the existing script.
    WeeklySales := getWorkbookByName(xl, "WeeklySales")

; Define workbook and column settings based on country. The ternary operator (?:) is necessary to select between two values based on a condition. I can not omit it, because the code will not work properly. AutoHotkey will not recognize how the conditions should be evaluated and the values assigned.
; Without the ternary operator, I cannot perform the selection directly in a single line. Instead, I would have to use traditional if-else statements to implement the same logic. 
; reportName := (selectedCountry = "Frankreich") ? "Hagebutte" : 
				;(selectedCountry = "NeuesLand1") ? "NeueDatei1" :
				;(selectedCountry = "NeuesLand2") ? "NeueDatei2" :
				;"BusinessReport"
; asinColumn := (selectedCountry = "Frankreich") ? "A" : "B"
; skuColumn := (selectedCountry = "Frankreich") ? "L" : "D"
; unitsOrderedColumn := (selectedCountry = "Frankreich") ? "G" : "O"



; Traditional if-else statements 

; Assume selectedCountry is set somewhere in the GUI
; Start the assignment based on the selected country
if (selectedCountry = "Frankreich") {
    reportName := "Hagebutte"
    asinColumn := "A"
    skuColumn := "L"
    unitsOrderedColumn := "G"
} else if (selectedCountry = "Deutschland") {
    reportName := "BusinessReport"
    asinColumn := "B"
    skuColumn := "D"
    unitsOrderedColumn := "O"
} else if (selectedCountry = "Kanada") {
    reportName := "BusinessReport"
    asinColumn := "B"
    skuColumn := "D"
    unitsOrderedColumn := "O"
} else if (selectedCountry = "NeuesLand2") {
    reportName := "NeueDatei2"
    asinColumn := "D"
    skuColumn := "F"
    unitsOrderedColumn := "Q"
} else {
    MsgBox , % "No Country matches the Reports, please change the Country or open the associated report." ; Standardwert, wenn keine anderen Bedingungen zutreffen
  
}

; Attempt to open the selected report workbook
reportWorkbook := getWorkbookByName(xl, reportName)
if (!reportWorkbook) {
    MsgBox, % "Could not find the workbook '" reportName "'. Please ensure it is open."
    return
}

    ; BusinessReport := getWorkbookByName(xl, "BusinessReport") ; duplicate or necessary?
   ; WeeklySales := getWorkbookByName(xl, "WeeklySales") ; duplicate or necessaryne

	; Loops through all rows in the WeeklySales sheet.
	
	BRSheet := reportWorkbook.Sheets(1)
	;WSSheet := WeeklySales.Sheets(1)
	WSSheet := getWorkbookByName(xl, "WeeklySales").Sheets(1)
	BRRows := BRSheet.UsedRange.Rows.Count
	WSRows := WSSheet.UsedRange.Rows.Count
    countryColumn := { "Deutschland": "D", "Frankreich": "H", "Kanada": "L" } ; Defines country columns for data entry based on user selection.
    ; selectedCountry := "Deutschland"  ; This should be selected dynamically via a GUI.

    ; Data processing loop
    Loop, % WSRows-1 {
        wsrow := A_Index + 1
        ASIN_WS := Trim(WSSheet.Range("B" . wsrow).Value) ; Gets the ASIN from WeeklySales.
        SKU_WS := Trim(WSSheet.Range("A" . wsrow).Value)  ; Gets the SKU from WeeklySales.
        noMatchFound := true ; Flag to check if a match is found.

    if (ASIN_WS = "" && RegExMatch(SKU_WS, "i)-FBA$")) {
        continue  ; Überspringen dieser Zeile, wenn keine ASIN vorhanden ist und die SKU auf "-FBA" endet
    }
        ; Loops through all rows in the Business Report sheet.
        Loop, % BRRows-1 {
            row := A_Index + 1
            ASIN_BR := Trim(BRSheet.Range(asinColumn . row).Value) ; Gets the ASIN based on selected country.
            SKU_BR := Trim(BRSheet.Range(skuColumn . row).Value) ; Gets the SKU based on selected country.
            UnitsOrdered := BRSheet.Range(unitsOrderedColumn . row).Value ; Gets the Units Ordered based on selected country.

            ; if (RegExMatch(SKU_BR, ".*-B$")) {; What was the Differenze between i and .* ...?
			            
			  if (RegExMatch(SKU_BR, "i)-B$")) { ; Skips entries where SKU ends with '-B'.
               ; MsgBox, % "SKU `" SKU_BR "` ends with '-B' and will be skipped."
                continue
            }

            ; if (ASIN_BR = ASIN_WS && ((RegExMatch(SKU_BR, ".*-FBA$") && RegExMatch(SKU_WS, ".*-FBA$")) || (!RegExMatch(SKU_BR, ".*(-FBA|-B)$") && !RegExMatch(SKU_WS, ".*(-FBA|-B)$")))) {  ; Old working Code.
			
			; Checks for matching ASINs and SKUs, respecting the '-FBA' notation.
			 if (ASIN_BR = ASIN_WS && ((RegExMatch(SKU_BR, "i)-FBA$") && RegExMatch(SKU_WS, "i)-FBA$")) || (!RegExMatch(SKU_BR, "i).*(-FBA|-B)$") && !RegExMatch(SKU_WS, "i).*(-FBA|-B)$")))) { ; new one with i instead of .*
				 WSSheet.Range(countryColumn[selectedCountry] . wsrow).Value := UnitsOrdered
                ; MsgBox, % "Orders for ASIN `" ASIN_BR "` and SKU `" SKU_BR "` have been entered in row " wsrow " of 'WeeklySales' in column `" countryColumn[selectedCountry] "`."
                 noMatchFound := false
                 break   ; Stops the loop once a match is found.
            }
        }

       ; if (noMatchFound) { ; old code withouth checken for empty spaces of ASIN in WeeklySales
	   
		;If no match is found, enters '0' for that ASIN in WeeklySales.
		if (noMatchFound && ASIN_WS != "") { 
            WSSheet.Range(countryColumn[selectedCountry] . wsrow).Value := 0
            ;MsgBox, % "No match found for ASIN `" ASIN_WS "` in 'BusinessReport'. A '0' has been entered in 'WeeklySales' row " wsrow "."
        }
		; Update the progress in the GUI
        ProgressUpdate((A_Index * 100) / WSRows)
    }
; Completion message and clean-up
    Gui, ProgressGui: Destroy
    MsgBox, % "Data transfer with ASIN match completed."
    Gui, Destroy
   
return

GuiClose:
    Gui, ProgressGui: Destroy
return

; Function to update the progress bar in the GUI
ProgressUpdate(progress) {
    global
    GuiControl, ProgressGui:, Progress, %progress%
    if (progress = 100) {
        Gui, ProgressGui: Destroy
        MsgBox, % "Datenübertragung abgeschlossen."
    }
	xl.ScreenUpdating := true  ; Stelle die Bildschirmaktualisierung wieder her
}

F10::ExitApp  ; Hotkey to exit the script.
F11::Suspend  ; Hotkey to disable/enable all hotkeys and hotstrings.
F12::Reload  ; Hotkey to reload the script.

Loop

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
#Warn  ; Enable warnings to help identify potential issues.
SetWorkingDir %A_ScriptDir%  ; Sets the working directory to the script's directory.
SendMode Input  ; Sets the method for sending keystrokes to 'Input'.
#SingleInstance force  ; Ensures that only one instance of the script is running.
SetBatchLines -1  ; Maximizes script performance by running it as fast as possible.
ListLines Off  ; Disables the listing of executed lines for performance.

try {
    xl := ComObjActive("Excel.Application")  ; Attempts to get an active instance of Excel.
} catch {
    MsgBox, 48, Error, No active Excel instance found. Please open Excel before running this script.
    return ; Exits if Excel is not open.
}
xl.Visible := true  ; Makes Excel visible  to the user and to track changes.

; Function to retrieve a workbook by part of its name
getWorkbookByName(excel, namePart) {
    Loop, % excel.Workbooks.Count { 
        workbook := excel.Workbooks.Item(A_Index) ; Loops through all open workbooks.
        If (InStr(workbook.Name, namePart) > 0) ; Checks if workbook name contains the specified part.
            return workbook ; Returns the workbook if found.
    }
    return ""  ; Returns an empty string if no matching workbook is found
}

F2::  ; Hotkey to start the script
    ; Attempts to connect to an Excel instance and checks if specific workbooks are open
    try {
        excel := ComObjActive("Excel.Application")
    } catch {
        MsgBox, % "Please open Excel and the Excel files 'BusinessReport' and 'WeeklySales'."
        return
    }
	
	; Checks if specific workbooks are open.
    workbookBR := getWorkbookByName(excel, "BusinessReport")
    workbookVA := getWorkbookByName(excel, "WeeklySales")

	; Generates an error message if the workbooks are missing.

    If (!workbookBR && !workbookVA) {
        MsgBox, % "Please open the Excel files 'BusinessReport' and 'WeeklySales'."
        return
    } else if (!workbookBR) {
        MsgBox, % "Please open the Excel file 'BusinessReport'."
        return
    } else if (!workbookVA) {
        MsgBox, % "Please open the Excel file 'WeeklySales'."
        return
    }

    ; Validate the header in column "O" of the BusinessReport
    worksheetBR := workbookBR.Sheets(1)
    header := worksheetBR.Cells(1, 15).Value
    If (header != "Units Ordered") {
        MsgBox, % "The header in column 'O' of the 'BusinessReport' workbook does not match the expected 'Units Ordered'. Please check the file."
        return
    }
	
   ; GUI definition with customized design
	Gui, New
    Gui, Font, s10 cBlack, Arial  ; Set font and color
    Gui, Color, FFFFFF  ; Set background color
    Gui, Add, Text, x10 y10 w360 h30 +Center, Bitte geben Sie das Land für den 'BusinessReport' ein: ; Show the GUI with the title "Select a Country".
	Gui, Add, DropDownList, x10 y50 w360 h300 vCountryChoice, Deutschland|Frankreich|Kanada ; Add a dropdown list with options.
	Gui, Add, Button, x10 y100 w360 h30 Default, OK  ; Add an OK button to submit the selection.
    Gui, Show, w380 h150,  Wöchentliche Verkaufsstatistik

return

ButtonOK:
    Gui, Submit  ; Retrieve the data from the GUI.
    global selectedCountry := CountryChoice  ; Store the selected country from the dropdown.

    Gui, Destroy  ; Destroy the GUI after the selection.


; Setup GUI for progress bar
	Gui, ProgressGui: New, +AlwaysOnTop  +Owner
	Gui, ProgressGui: Color, White
	Gui, ProgressGui: Font, s12 cBlack, Verdana
	Gui, ProgressGui: Add, Text, x10 y10 w300 h20 +Center, Verarbeitung läuft...
	Gui, ProgressGui: Add, Progress, x10 y40 w280 h20 vProgress cLime ; gProgressChanged (wrong Control element)
	Gui, ProgressGui: Show, w300 h70, Gleich geschafft


    ; Continue with the existing script.
    WeeklySales := getWorkbookByName(xl, "WeeklySales")

; Define workbook and column settings based on country. The ternary operator (?:) is necessary to select between two values based on a condition. I can not omit it, because the code will not work properly. AutoHotkey will not recognize how the conditions should be evaluated and the values assigned.
; Without the ternary operator, I cannot perform the selection directly in a single line. Instead, I would have to use traditional if-else statements to implement the same logic. 
; reportName := (selectedCountry = "Frankreich") ? "Hagebutte" : 
				;(selectedCountry = "NeuesLand1") ? "NeueDatei1" :
				;(selectedCountry = "NeuesLand2") ? "NeueDatei2" :
				;"BusinessReport"
; asinColumn := (selectedCountry = "Frankreich") ? "A" : "B"
; skuColumn := (selectedCountry = "Frankreich") ? "L" : "D"
; unitsOrderedColumn := (selectedCountry = "Frankreich") ? "G" : "O"



; Traditional if-else statements 

; Assume selectedCountry is set somewhere in the GUI
; Start the assignment based on the selected country
if (selectedCountry = "Frankreich") {
    reportName := "Hagebutte"
    asinColumn := "A"
    skuColumn := "L"
    unitsOrderedColumn := "G"
} else if (selectedCountry = "Deutschland") {
    reportName := "BusinessReport"
    asinColumn := "B"
    skuColumn := "D"
    unitsOrderedColumn := "O"
} else if (selectedCountry = "Kanada") {
    reportName := "BusinessReport"
    asinColumn := "B"
    skuColumn := "D"
    unitsOrderedColumn := "O"
} else if (selectedCountry = "NeuesLand2") {
    reportName := "NeueDatei2"
    asinColumn := "D"
    skuColumn := "F"
    unitsOrderedColumn := "Q"
} else {
    MsgBox , % "No Country matches the Reports, please change the Country or open the associated report." ; Standardwert, wenn keine anderen Bedingungen zutreffen
  
}

; Attempt to open the selected report workbook
reportWorkbook := getWorkbookByName(xl, reportName)
if (!reportWorkbook) {
    MsgBox, % "Could not find the workbook '" reportName "'. Please ensure it is open."
    return
}

    ; BusinessReport := getWorkbookByName(xl, "BusinessReport") ; duplicate or necessary?
   ; WeeklySales := getWorkbookByName(xl, "WeeklySales") ; duplicate or necessaryne

	; Loops through all rows in the WeeklySales sheet.
	
	BRSheet := reportWorkbook.Sheets(1)
	;WSSheet := WeeklySales.Sheets(1)
	WSSheet := getWorkbookByName(xl, "WeeklySales").Sheets(1)
	BRRows := BRSheet.UsedRange.Rows.Count
	WSRows := WSSheet.UsedRange.Rows.Count
    countryColumn := { "Deutschland": "D", "Frankreich": "H", "Kanada": "L" } ; Defines country columns for data entry based on user selection.
    ; selectedCountry := "Deutschland"  ; This should be selected dynamically via a GUI.

    ; Data processing loop
    Loop, % WSRows-1 {
        wsrow := A_Index + 1
        ASIN_WS := Trim(WSSheet.Range("B" . wsrow).Value) ; Gets the ASIN from WeeklySales.
        SKU_WS := Trim(WSSheet.Range("A" . wsrow).Value)  ; Gets the SKU from WeeklySales.
        noMatchFound := true ; Flag to check if a match is found.

    if (ASIN_WS = "" && RegExMatch(SKU_WS, "i)-FBA$")) {
        continue  ; Überspringen dieser Zeile, wenn keine ASIN vorhanden ist und die SKU auf "-FBA" endet
    }
        ; Loops through all rows in the Business Report sheet.
        Loop, % BRRows-1 {
            row := A_Index + 1
            ASIN_BR := Trim(BRSheet.Range(asinColumn . row).Value) ; Gets the ASIN based on selected country.
            SKU_BR := Trim(BRSheet.Range(skuColumn . row).Value) ; Gets the SKU based on selected country.
            UnitsOrdered := BRSheet.Range(unitsOrderedColumn . row).Value ; Gets the Units Ordered based on selected country.

            ; if (RegExMatch(SKU_BR, ".*-B$")) {; What was the Differenze between i and .* ...?
			            
			  if (RegExMatch(SKU_BR, "i)-B$")) { ; Skips entries where SKU ends with '-B'.
               ; MsgBox, % "SKU `" SKU_BR "` ends with '-B' and will be skipped."
                continue
            }

            ; if (ASIN_BR = ASIN_WS && ((RegExMatch(SKU_BR, ".*-FBA$") && RegExMatch(SKU_WS, ".*-FBA$")) || (!RegExMatch(SKU_BR, ".*(-FBA|-B)$") && !RegExMatch(SKU_WS, ".*(-FBA|-B)$")))) {  ; Old working Code.
			
			; Checks for matching ASINs and SKUs, respecting the '-FBA' notation.
			 if (ASIN_BR = ASIN_WS && ((RegExMatch(SKU_BR, "i)-FBA$") && RegExMatch(SKU_WS, "i)-FBA$")) || (!RegExMatch(SKU_BR, "i).*(-FBA|-B)$") && !RegExMatch(SKU_WS, "i).*(-FBA|-B)$")))) { ; new one with i instead of .*
				 WSSheet.Range(countryColumn[selectedCountry] . wsrow).Value := UnitsOrdered
                ; MsgBox, % "Orders for ASIN `" ASIN_BR "` and SKU `" SKU_BR "` have been entered in row " wsrow " of 'WeeklySales' in column `" countryColumn[selectedCountry] "`."
                 noMatchFound := false
				 Sleep, 10
                 break   ; Stops the loop once a match is found.
            }
			Sleep, 10
        }

       ; if (noMatchFound) { ; old code withouth checken for empty spaces of ASIN in WeeklySales
	   
		;If no match is found, enters '0' for that ASIN in WeeklySales.
		if (noMatchFound && ASIN_WS != "") { 
            WSSheet.Range(countryColumn[selectedCountry] . wsrow).Value := 0
            ;MsgBox, % "No match found for ASIN `" ASIN_WS "` in 'BusinessReport'. A '0' has been entered in 'WeeklySales' row " wsrow "."
        }
		Sleep, 10 ; Update the progress in the GUI
        ProgressUpdate((A_Index * 100) / WSRows)
    }
; Completion message and clean-up
    Gui, ProgressGui: Destroy
    MsgBox, % "Data transfer with ASIN match completed."
    Gui, Destroy
   
return

GuiClose:
    Gui, ProgressGui: Destroy
	Sleep, 10 
return

; Function to update the progress bar in the GUI
ProgressUpdate(progress) {
    global
    GuiControl, ProgressGui:, Progress, %progress%
    if (progress = 100) {
        Gui, ProgressGui: Destroy
        MsgBox, % "Datenübertragung abgeschlossen."
    }
}

F10::ExitApp  ; Hotkey to exit the script.
F11::Suspend  ; Hotkey to disable/enable all hotkeys and hotstrings.
F12::Reload  ; Hotkey to reload the script.

Any idea which one would be better xl.Visible := true vs ScreenUpdating vs Loop?

[Mindfork]

Edit: The xl.visible script

[Mindfork]

I have fixed most of the problems, I am just stuck at the

Error: 0x80010001 Call was rejected by called party.

Screenshot 2024-04-13 162534.png

(21.46 KiB) Downloaded 51 times

This error can occur under several conditions, in particular:

This error can occur under several conditions, in particular, usually when a user is actively editing a cell in Excel (e.g. by entering text or numbers or he has simply forgotten to exit the cell), the script is in a state that does not allow data processing. My guess: the script in this state to perform operations such as reading cell values or executing functions, Excel may reject the requests, resulting in the above error.

Currently I am trying to make it work with a workaround, this code:

Code: Select all

IsExcelEditing(excel) {
    try {
        excel.Selection.Copy
        return false  ; No error, not in edit mode
    } catch {
        return true  ; Error occurred, probably in edit mode
    }
}
;global userCancelled := false  ; Global variable to track whether the user has canceled the script
;userCancelled := true 
AskUserToContinue() {
    MsgBox, 4, Bearbeitungsmodus, Edit mode, It appears that you are currently making an entry in Excel. Would you like to continue the input and pause the script, or should we stop editing (ATTENTION: entries already written in the row will be lost) and start transferring data?
    IfMsgBox, No
        return false
    return true
}

HandleExcelEditing() {
    global excel  ; Use global variable 'excel'

    if (WinExist("ahk_exe excel.exe", "WeeklySales")) { 
        WinGet, excelID, ID, ahk_exe excel.exe, WeeklySales
        excel := ComObjActive("Excel.Application")  ; Ensures that the 'excel' object is referenced.

        if (IsExcelEditing(excel)) {
            if (!AskUserToContinue()) {
                return false  ; Terminates the script if the user selects 'No'
            }
            WinActivate, ahk_id %excelID%
            Sleep 100
            ControlSend,, {ESC}, ahk_id %excelID%
            Sleep, 500
            if (IsExcelEditing(excel)) {
                MsgBox, 48, Error, Bitte schließen Sie manuell die Bearbeitung in Excel ab.
                return false
            }
        }
    } else {
        MsgBox, 48, Error, Weekly Sales ist nicht geöffnet.
        return false
    }
    return true
}

And wherever Autohotkey gives me the information that a line contains an error, because a row in Excel is waiting for user input, I add it:

Code: Select all

}

    if (!HandleExcelEditing()) {
        return  ; Stop if back in editing mode
    }

It works very well for the yes query but for the no query it has some strange outputs for me. Either the query comes twice or it jumps to the next function and there I get the messagebox. For example it jumps to the messagebox from:

Code: Select all

try {
    xl := ComObjActive("Excel.Application")  ; Attempts to get an active instance of Excel.
} catch {
    MsgBox, 48, Error, No active Excel instance found. Please open Excel before running this script.
    return ; Exits if Excel is not open.
}
xl.Visible := true  ; Makes Excel visible  to the user and to track changes.

Or has some other strange behavior that I am not able to understand.

Somebody an Idea how to fix Error: 0x80010001 Call was rejected by called party. ?

Btw this is my current code:

WeeklySales Version 1.1.ahk

(17.76 KiB) Downloaded 1 time

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
#Warn  ; Enable warnings to help identify potential issues.
SetWorkingDir %A_ScriptDir%  ; Sets the working directory to the script's directory.
SendMode Input  ; Sets the method for sending keystrokes to 'Input'.
#SingleInstance force  ; Ensures that only one instance of the script is running.
SetBatchLines -1  ; Maximizes script performance by running it as fast as possible.
ListLines Off  ; Disables the listing of executed lines for performance.


;-----------------------------------------------------------------------------------
;Changelog: 

; 13.04.2024 - Version 1.0

; Graphical interface: Country selection

; Security functions Check whether Excel is open. Header check in "BusinessReport" with in-depth validation of the "O" column for "Units ordered".

; Workbook check: Identification of open workbooks "BusinessReport" and necessary other workbooks depending on the country.

; Data transfer: Automatic transfer of data between the specific workbooks.

;-----------------------------------------------------------------------------------

; Known Issues:
; If you click in excel while the script is running you will receive an error. Just leave the row that you are in excel and continue the script until finished.


;-----------------------------------------------------------------------------------

;Keybindings

; F2 = Start the script
; F10 = Exit the script
; F11 = Disables / enables all hotkeys and hotstrings
; F12 = Reload the script

;--------------------------------
;getWorkbookByName(excel, namePart) { ; Funktion für eine Teilübereinstimmung der Suche der Arbeitsmappen
    ;Loop, % excel.Workbooks.Count {
     ;   workbook := excel.Workbooks.Item(A_Index)  ; Durchläuft alle offenen Arbeitsmappen.
    ;    If InStr(workbook.Name, namePart)  ; Überprüft, ob der Teil des Namens im Arbeitsmappennamen enthalten ist.
   ;         return workbook  ; Gibt die Arbeitsmappe zurück, wenn gefunden.
  ;  }
 ;   return ""  ; Gibt einen leeren String zurück, wenn keine passende Arbeitsmappe gefunden wurde.
;}

; global countryColumn := {"Deutschland": "D", "Frankreich": "H", "Kanada": "L"} ; globale version für das vollständige Script



IsExcelEditing(excel) {
    try {
        excel.Selection.Copy
        return false  ; No error, not in edit mode
    } catch {
        return true  ; Error occurred, probably in edit mode
    }
}
;global userCancelled := false  ; Global variable to track whether the user has canceled the script
;userCancelled := true 
AskUserToContinue() {
    MsgBox, 4, Bearbeitungsmodus, Edit mode, It appears that you are currently making an entry in Excel. Would you like to continue the input and pause the script, or should we stop editing (ATTENTION: entries already written in the row will be lost) and start transferring data?
    IfMsgBox, No
        return false
    return true
}

HandleExcelEditing() {
    global excel  ; Use global variable 'excel'

    if (WinExist("ahk_exe excel.exe", "WeeklySales")) { 
        WinGet, excelID, ID, ahk_exe excel.exe, WeeklySales
        excel := ComObjActive("Excel.Application")  ; Ensures that the 'excel' object is referenced.

        if (IsExcelEditing(excel)) {
            if (!AskUserToContinue()) {
                return false  ; Terminates the script if the user selects 'No'
            }
            WinActivate, ahk_id %excelID%
            Sleep 100
            ControlSend,, {ESC}, ahk_id %excelID%
            Sleep, 500
            if (IsExcelEditing(excel)) {
                MsgBox, 48, Error, Bitte schließen Sie manuell die Bearbeitung in Excel ab.
                return false
            }
        }
    } else {
        MsgBox, 48, Error, Weekly Sales ist nicht geöffnet.
        return false
    }
    return true
}

    if (!HandleExcelEditing()) {
        return  ; Stop if back in editing mode
    }

try {
    xl := ComObjActive("Excel.Application")  ; Attempts to get an active instance of Excel.
} catch {
    MsgBox, 48, Error, No active Excel instance found. Please open Excel before running this script.
    return ; Exits if Excel is not open.
}
xl.Visible := true  ; Makes Excel visible  to the user and to track changes.

; Function to retrieve a workbook by part of its name
getWorkbookByName(excel, namePart) {
    if (!HandleExcelEditing()) {
        return  ; Stop if back in editing mode
    }
	
    Loop, % excel.Workbooks.Count { 
        workbook := excel.Workbooks.Item(A_Index) ; Loops through all open workbooks.
        If (InStr(workbook.Name, namePart) > 0) ; Checks if workbook name contains the specified part.
            return workbook ; Returns the workbook if found.
    }
    return ""  ; Returns an empty string if no matching workbook is found
}

global guiOpen := false  ; Variable to track if the GUI is open

F2::  ; Hotkey to start the script
    ; Attempts to connect to an Excel instance and checks if specific workbooks are open
    try {
        excel := ComObjActive("Excel.Application")
    } catch {
        MsgBox, % "Please open Excel and the Excel files 'BusinessReport' and 'WeeklySales'."
        return
    }
	
	; Checks if specific workbooks are open.
    workbookBR := getWorkbookByName(excel, "BusinessReport")
    workbookVA := getWorkbookByName(excel, "WeeklySales")

	; Generates an error message if the workbooks are missing.
    If (!workbookBR && !workbookVA) {
        MsgBox, % "Please open the Excel files 'BusinessReport' and 'WeeklySales'."
        return
    } else if (!workbookBR) {
        MsgBox, % "Please open the Excel file 'BusinessReport'."
        return
    } else if (!workbookVA) {
        MsgBox, % "Please open the Excel file 'WeeklySales'."
        return
    }

    ; Validate the header in column "O" of the BusinessReport
    worksheetBR := workbookBR.Sheets(1)
    header := worksheetBR.Cells(1, 15).Value
    If (header != "Units Ordered") {
        MsgBox, % "The header in column 'O' of the 'BusinessReport' workbook does not match the expected 'Units Ordered'. Please check the file."
        return
    }

    if (guiOpen)  ; Check if GUI is already open
    {
        return  ; If GUI is open, do nothing
    }
    guiOpen := true  ; Set GUI as open

	
   ; GUI definition with customized design
	Gui, New
    Gui, Font, s10 cBlack, Arial  ; Set font and color
    Gui, Color, FFFFFF  ; Set background color
    Gui, Add, Text, x10 y10 w360 h30 +Center, Bitte geben Sie das Land für den 'BusinessReport' ein: ; Show the GUI with the title "Select a Country".
	Gui, Add, DropDownList, x10 y50 w360 h300 vCountryChoice, Deutschland|Frankreich|Kanada ; Add a dropdown list with options.
	Gui, Add, Button, x10 y100 w360 h30 Default, OK  ; Add an OK button to submit the selection.
    Gui, Show, w380 h150,  Wöchentliche Verkaufsstatistik
	Gui +LastFound
return

ButtonOK:
    Gui, Submit  ; Retrieve the data from the GUI.
    global selectedCountry := CountryChoice  ; Store the selected country from the dropdown.
MsgBox, 36, Bestätigung, Sie haben "%selectedCountry%" ausgewählt. Möchten Sie fortfahren?
    IfMsgBox, No
    {
        Gui, Show  ; Zeigt das GUI erneut an, falls der Benutzer 'Nein' wählt.
        return  ; Verhindert das Schließen des GUIs und weiteren Codeausführung.
    }
    Gui, Destroy  ; Destroy the GUI after the selection.
	guiOpen := false  ; Make sure to reset the flag when GUI is properly used

; Setup GUI for progress bar
GuiWidth := 300
GuiHeight := 280
ImageWidth :=  GuiWidth * 0.35 ; Gleiche Breite wie der Ladebalken, anderer code: GuiWidth * 0.3 
ImageHeight := GuiHeight * 0.5   ; Verhältnis beibehalten

; Bild mittig unter dem Ladebalken positionieren
ImageX := GuiWidth * 0.5 ; Zentriert das Bild horizontal
ImageY := 80  ; Positioniert das Bild etwas unterhalb des Ladebalkens

	Gui, ProgressGui: New, +AlwaysOnTop  +Owner
	Gui, ProgressGui: Color, White
	Gui, ProgressGui: Font, s12 cBlack, Verdana
	Gui, ProgressGui: Add, Text, x10 y10 w300 h20 +Center, Verarbeitung läuft...
	Gui, ProgressGui: Add, Progress, x10 y40 w280 h20 vProgress cLime ; gProgressChanged (wrong Control element)
	;ImageWidth := 280  ; Gleiche Breite wie der Ladebalken
	;ImageHeight := ImageWidth * 2/3  ; Verhältnis beibehalten
	;Gui, ProgressGui: Add, Picture, x%ImageX% y%ImageY% w%ImageWidth% h%ImageHeight%, C:\Users\mindf\Pictures\Screenshots\Screenshot 2024-03-11 032951.png
	;Gui, ProgressGui: Show, w%ImageHeight% h%GuiHeight%, Gleich geschafft
	Gui, ProgressGui: Add, Picture, x%ImageWidth% + 10 y70 w%ImageWidth% h%ImageHeight%, C:\Users\mindf\Pictures\Screenshots\Screenshot 2024-03-11 032951.png  ; Pfad zum Bild anpassen
	Gui, ProgressGui: Show, x%ImageHeight% , Gleich geschafft
	;Gui, ProgressGui: Add, Picture, x10 y70 w%ImageWidth% h%ImageHeight%, C:\Users\mindf\Pictures\Screenshots\Screenshot 2024-03-11 032951.png  ; Hier ist eine andere Formatierung der Koordinaten
	;Gui, ProgressGui: Show, w300 h70, Gleich geschafft ; Hier ist eine andere Formatierung der Koordinaten

    ; Continue with the existing script.
    WeeklySales := getWorkbookByName(xl, "WeeklySales")

  ; Manuelle Datenverschiebung basierend auf der Länderauswahl
	countryColumn := { "Deutschland": "E", "Frankreich": "I", "Kanada": "L" }[selectedCountry] ; Defines country columns for data entry based on user selection.
    columnLeft := Chr(Asc(countryColumn) - 1)  ; Calculate the left column based on the current column
    leftMostColumn := Chr(Asc(columnLeft) - 1) ; Further left column

	WSSheet := workbookVA.Sheets(1)
    startRow := 8
    endRow := 76
	
	; Move data from the left column to the further left column
    Loop, % endRow-startRow+1 {
        currentRow := startRow + A_Index - 1
        valueInLeft := WSSheet.Range(columnLeft . currentRow).Value
        WSSheet.Range(leftMostColumn . currentRow).Value := valueInLeft
    }

    ; Move data from the current column to the left column
    Loop, % endRow-startRow+1 {
        currentRow := startRow + A_Index - 1
        valueInCurrent := WSSheet.Range(countryColumn . currentRow).Value
        WSSheet.Range(columnLeft . currentRow).Value := valueInCurrent
    }

;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
; Define workbook and column settings based on country. 
;The ternary operator (?:) is necessary to select between two values based on a 
;condition. I can not omit it, because the code will not work properly. 
;AutoHotkey will not recognize how the conditions should be evaluated and the values assigned.
; Without the ternary operator, I cannot perform the selection directly in a single line. Instead, I would have to use traditional if-else statements to implement the same logic. 
; reportName := (selectedCountry = "Frankreich") ? "Hagebutte" : 
				;(selectedCountry = "NeuesLand1") ? "NeueDatei1" :
				;(selectedCountry = "NeuesLand2") ? "NeueDatei2" :
				;"BusinessReport"
; asinColumn := (selectedCountry = "Frankreich") ? "A" : "B"
; skuColumn := (selectedCountry = "Frankreich") ? "L" : "D"
; unitsOrderedColumn := (selectedCountry = "Frankreich") ? "G" : "O"
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

; Traditional if-else statements 

; Assume selectedCountry is set somewhere in the GUI
; Start the assignment based on the selected country
if (selectedCountry = "Frankreich") {
    reportName := "Hagebutte"
    asinColumn := "A"
    skuColumn := "L"
    unitsOrderedColumn := "G"
} else if (selectedCountry = "Deutschland") {
    reportName := "BusinessReport"
    asinColumn := "B"
    skuColumn := "D"
    unitsOrderedColumn := "O"
} else if (selectedCountry = "Kanada") {
    reportName := "BusinessReport"
    asinColumn := "B"
    skuColumn := "D"
    unitsOrderedColumn := "O"
} else if (selectedCountry = "NeuesLand2") {
    reportName := "NeueDatei2"
    asinColumn := "D"
    skuColumn := "F"
    unitsOrderedColumn := "Q"
} else {
    MsgBox , % "No Country matches the Reports, please change the Country or open the associated report." ; Standardwert, wenn keine anderen Bedingungen zutreffen
  
}

	; Attempt to open the selected report workbook
	reportWorkbook := getWorkbookByName(xl, reportName)
	if (!reportWorkbook) {
		MsgBox, % "Could not find the workbook '" reportName "'. Please ensure it is open."
		return
}

   ; BusinessReport := getWorkbookByName(xl, "BusinessReport") ; duplicate or necessary?
   ; WeeklySales := getWorkbookByName(xl, "WeeklySales") ; duplicate or necessaryne

	; Loops through all rows in the Weekly Sales sheet.
	
	BRSheet := reportWorkbook.Sheets(1)
	;WSSheet := WeeklySales.Sheets(1)
	WSSheet := getWorkbookByName(xl, "WeeklySales").Sheets(1)
	BRRows := BRSheet.UsedRange.Rows.Count
	WSRows := WSSheet.UsedRange.Rows.Count ; Kein bedarf mehr weil die Zeilen Festgelegt sind. 
    countryColumn := { "Deutschland": "E", "Frankreich": "H", "Kanada": "L" } ; Defines country columns for data entry based on user selection.
    ;selectedCountry := "Deutschland"  ; This should be selected dynamically via a GUI.

    ; Data processing loop
	;-----------------------------------------------------------
	; Diese Zeile ist für WSRows := WSSheet.UsedRange.Rows.Count wenn wieder bedarf ist
    ;Loop, % WSRows-1 {
    ;    wsrow := A_Index + 1
    ;    ASIN_WS := Trim(WSSheet.Range("B" . wsrow).Value) ; Gets the ASIN from Weekly Sales.
    ;    SKU_WS := Trim(WSSheet.Range("A" . wsrow).Value)  ; Gets the SKU from Weekly Sales.
    ;    noMatchFound := true ; Flag to check if a match is found.
	;-----------------------------------------------------------

	; Bestimme das Arbeitsblatt und die Spalte für ASINs basierend auf dem ausgewählten Land
	asinSheet := workbookVA.Sheets("ASINs")  ; Angenommen, das zweite Arbeitsblatt heißt "ASINs"
	countryAsinColumn := {"Deutschland": "B", "Frankreich": "D", "Kanada": "F"}[selectedCountry]
	startRow := 2
	endRow := 76


	; Zeile für festgelegten Bereich
	Loop, 76 { ; Loop durch die Zeilen von 8 bis 76
        wsrow := A_Index + 7  ; Startet bei Zeile 8 (A_Index startet bei 1)
		if (wsrow > endRow)  ; Verhindert, dass die Schleife über Zeile 76 hinausgeht
			break
			
        ASIN_WS := Trim(asinSheet.Range(countryAsinColumn . wsrow).Value) ; Gets the ASIN from Weekly Sales.
        SKU_WS := Trim(WSSheet.Range("A" . wsrow).Value)  ; Gets the SKU from Weekly Sales.
        noMatchFound := true ; Flag to check if a match is found.

    if (ASIN_WS = "" && RegExMatch(SKU_WS, "i)-FBA$")) {
        continue  ; Überspringen dieser Zeile, wenn keine ASIN vorhanden ist und die SKU auf "-FBA" endet
    }
        ; Loops through all rows in the Business Report sheet.
        Loop, % BRRows-1 {
            row := A_Index + 1
            ASIN_BR := Trim(BRSheet.Range(asinColumn . row).Value) ; Gets the ASIN based on selected country.
            SKU_BR := Trim(BRSheet.Range(skuColumn . row).Value) ; Gets the SKU based on selected country.
            UnitsOrdered := BRSheet.Range(unitsOrderedColumn . row).Value ; Gets the Units Ordered based on selected country.

            ; if (RegExMatch(SKU_BR, ".*-B$")) {; What was the Differenze between i and .* ...?
			            
			  if (RegExMatch(SKU_BR, "i)-B$")) { ; Skips entries where SKU ends with '-B'.
               ; MsgBox, % "SKU `" SKU_BR "` ends with '-B' and will be skipped."
                continue
            }

            ; if (ASIN_BR = ASIN_WS && ((RegExMatch(SKU_BR, ".*-FBA$") && RegExMatch(SKU_WS, ".*-FBA$")) || (!RegExMatch(SKU_BR, ".*(-FBA|-B)$") && !RegExMatch(SKU_WS, ".*(-FBA|-B)$")))) {  ; Old working Code.
			
			; Checks for matching ASINs and SKUs, respecting the '-FBA' notation.
			 if (ASIN_BR = ASIN_WS && ((RegExMatch(SKU_BR, "i)-FBA$") && RegExMatch(SKU_WS, "i)-FBA$")) || (!RegExMatch(SKU_BR, "i).*(-FBA|-B)$") && !RegExMatch(SKU_WS, "i).*(-FBA|-B)$")))) { ; new one with i instead of .*
				 WSSheet.Range(countryColumn[selectedCountry] . wsrow).Value := UnitsOrdered
                ; MsgBox, % "Orders for ASIN `" ASIN_BR "` and SKU `" SKU_BR "` have been entered in row " wsrow " of 'Weekly Sales' in column `" countryColumn[selectedCountry] "`."
                 noMatchFound := false
                 break   ; Stops the loop once a match is found.
            }
        }

       ; if (noMatchFound) { ; old code withouth checken for empty spaces of ASIN in WeeklySales
	   
		;If no match is found, enters '0' for that ASIN in Weekly Sales.
		if (noMatchFound && ASIN_WS != "") { 
            WSSheet.Range(countryColumn[selectedCountry] . wsrow).Value := 0
            ;MsgBox, % "No match found for ASIN `" ASIN_WS "` in 'BusinessReport'. A '0' has been entered in 'Weekly Sales' row " wsrow "."
        }
		; Update the progress in the GUI
        ProgressUpdate((A_Index * 100) / WSRows)
    }
; Completion message and clean-up
    Gui, ProgressGui: Destroy
    MsgBox, % "Data transfer with ASIN match completed."
    Gui, Destroy
   
return

GuiClose:
    Gui, Destroy  ; Destroy the main GUI if open
    Gui, ProgressGui:Destroy  ; Destroy the progress GUI if open
    guiOpen := false  ; Set GUI as not open
    return



; Function to update the progress bar in the GUI
ProgressUpdate(progress) {
    global
    GuiControl, ProgressGui:, Progress, %progress%
    if (progress = 100) {
        Gui, ProgressGui: Destroy
        MsgBox, % "Datenübertragung abgeschlossen."
    }
}

F10::ExitApp  ; Hotkey to exit the script.
F11::Suspend  ; Hotkey to disable/enable all hotkeys and hotstrings.
F12::Reload  ; Hotkey to reload the script.

[ShatterCoder]

It looks like you are on the right track using the try/catch. In the past I have employed a similar function. The basic Idea is to try to do a simple COM operation and see if excel throws an error. If it does I found the most effective thing to do was to simply notify the user that they need to make sure they are not not currently editing a cell and ask them to press ok once they have exited edit mode. then continue on with your code. You can try to send the escape key to the excel window, but there are cases where that will cause problems for the user if they are in the middle of editing the cell.

Code: Select all

xl := ComObjActive("Excel.Application")

rdychk()

return

rdychk()
{
   global
   try
      test := xl.ActiveSheet.Range("A1").Value
   catch e {
      if (e.Message = "0x80010001 - Call was rejected by callee.")
         MsgBox, 262192, , Please check to see if you are currently editing a cell. If you are`, please exit edit mode (press escape`, or enter in excel). Press OK once you have verified that you are not currently editing a cell.
      else
         MsgBox, 262192, , Uknown Error occured
   }
}

this was the approach I took to solve this same issue. You will want to change the if statement to match you language locality though.