Need help with Excel data transfer

[Mindfork]

Hi guys.
I am currently trying to automate data transfer from one excel sheet to another excel sheet (as far as possible)

One Excel file is called "BusinessReport" and the other "SalesData". I'm trying to work through the code step by step, but I'm having trouble with the following:

From the Excel file "BusinessRepor", column "B" "(Untergeordnete) ASIN", column "D" "SKU" and column O "Bestellte Einheiten" are important for the data extraction. I want to summarize the number of units ordered that match the ASIN and SKU, but ignore the SKU ending in "-B".

The script should do the following:

Basically > Check the ASIN(s) with their SKU to see if it does not end in "-B" and summarize the value of the units ordered from column "O" .(My SKU ends with "-B", "-FBA" or none of the two.The text highlighted in red on the screen in the Excel list should be processed, the text highlighted in blue should be ignored.)

grafik.png (47.39 KiB) Viewed 217 times

1. If I have the same ASIN with multiple SKU's and one SKU ends with "-B" > Add up the units from column "O" "Bestellte Einheiten", of both the "-FBA" SKU and the SKU that does not end with "-B".

2. If I have one ASIN with one SKU's which ends with "-B" > ignore that line.

3. If I have one ASIN and the SKU does not end with "-B" > Take the units from column "O" "Bestellte Einheiten".


Because my knowledge is not really worth mentioning, I tried my hand at Chatgpt and have two possible ways to approche that method, which I dont know would be better:

First:

Code: Select all

F3::
CalculateSumOfOrderedUnits()
return

CalculateSumOfOrderedUnits() {
    ; Access to the Excel object
    excel := ComObjActive("Excel.Application")
    ; Assumption: The BusinessReport file is already open and is the first workbook in Excel
    workbook := excel.Workbooks(1)  
    worksheet := workbook.Sheets(1)  ; Assuming it is the first sheet

    rowCount := worksheet.UsedRange.Rows.Count
    ; An object (map) for storing the total number of units ordered per ASIN
    ASINtoUnits := {}

    ; Search the lines and total the "Bestellte Einheiten"
    Loop, %rowCount% {
        zeile := A_Index + 1
        If (zeile > 1) {  ; Skip the header
            ASIN := worksheet.Cells(zeile, 2).Value
            SKU := worksheet.Cells(zeile, 4).Value
            bestellteEinheiten := worksheet.Cells(zeile, 15).Value

            ; Debugging output for checking the read values
            MsgBox, % "Zeile: " zeile " - ASIN: " ASIN " - SKU: " SKU " - Einheiten: " bestellteEinheiten

            ; Check that the SKU does not end with "-B"
            If (StrLen(SKU) > 0 and !InStr(SKU, "-B")) {
                ; Check whether the ASIN already exists and add up the units
                If !ASINtoUnits.HasKey(ASIN) {
                    ASINtoUnits[ASIN] := 0  ; Initialize the counter for this ASIN
                }
                ASINtoUnits[ASIN] += bestellteEinheiten
            }
        }
    }

    ; Run through the collected ASINs and display the totaled units
    For ASIN, Units in ASINtoUnits {
        MsgBox, % "Endgültige Summe - ASIN: " ASIN " - Summe der bestellten Einheiten: " Units
    }
}

Second:

Code: Select all

F2::
    CalculateSumOfOrderedUnits2()
return

CalculateSumOfOrderedUnits2() {
    excel := ComObjActive("Excel.Application")
    workbook := excel.Workbooks(1)
    worksheet := workbook.Sheets(1)
    
    rowCount := worksheet.UsedRange.Rows.Count
    ASINtoUnits := {}
    
    ; Search the rows and collect units based on SKU conditions
    Loop, %rowCount% {
        zeile := A_Index + 1  ; Start at 2, because A_Index starts at 1 and we skip the header line
        If (zeile > 1) {
            ASIN := worksheet.Cells(zeile, 2).Value
            SKU := worksheet.Cells(zeile, 4).Value
            bestellteEinheiten := worksheet.Cells(zeile, 15).Value
            
            If (!ASINtoUnits.HasKey(ASIN)) {
                ASINtoUnits[ASIN] := {"FBA": 0, "Standard": 0, "HasFBA": false}
            }
            
            If (StrLen(SKU) > 0) {
                If (InStr(SKU, "-FBA") and !InStr(SKU, "-B")) {
                    ASINtoUnits[ASIN]["FBA"] += bestellteEinheiten
                    ASINtoUnits[ASIN]["HasFBA"] := true
                } Else If (!InStr(SKU, "-B")) {
                    ASINtoUnits[ASIN]["Standard"] += bestellteEinheiten
                }
            }
        }
    }
    
    ; Calculate and show the total amount of units ordered per ASIN
    For ASIN, Units in ASINtoUnits {
        totalUnits := Units["HasFBA"] ? (Units["FBA"] + Units["Standard"]) : Units["Standard"]
        MsgBox, % "ASIN: " ASIN " - Summe der bestellten Einheiten: " totalUnits
    }
}

With both I have the problem that the ending on "-B" is not processed correctly. Either the ASIN which contains a SKU ending in "-B" is not evaluated at all.

Or if I insert

Code: Select all

 = 0

in

First Code:

Code: Select all

If (StrLen(SKU) > 0 and !InStr(SKU, "-B") =0)

Second code:

Code: Select all

If (InStr(SKU, "-FBA") and !InStr(SKU, "-B") = 0)

then the the units from column "O" "Bestellte Einheiten" add up fully, with the SKU that ends in "-B", "-FBA" and without an ending.

I hope that I have been able to express myself clearly, as a non native speaker. Can anyone please tell me where I went wrong.

[flyingDman]

Use substr(SKU,-1) != "-B" instead of !InStr(SKU, "-B")

[Mindfork]

Thank you very much, it was of huge help.

I did the next step and transfered the data from my excel list "BusinessReport" into "SalesData" (currently named "Verkaufsliste Autohotkey" for testing).

Code: Select all

   #NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
F2::
; Referencing an Excel application
    excel := ComObjActive("Excel.Application")
    
    ; Checking and referencing the required workbooks
    workbookBR := getWorkbookByName(excel, "BusinessReport")
    workbookVA := getWorkbookByName(excel, "Verkaufsliste Autohotkey")

    ; Check whether the workbooks have been found
    If (workbookBR = "" or workbookVA = "") {
        MsgBox, % "One or both of the required workbooks were not found."
        return
    }

    worksheetBR := workbookBR.Sheets(1)
    worksheetVA := workbookVA.Sheets(1)

    CalculateSumOfOrderedUnits6(excel, worksheetBR, worksheetVA)
return

getWorkbookByName(excel, nameStart) {
    Loop, % excel.Workbooks.Count {
        workbook := excel.Workbooks(A_Index)
        If (InStr(workbook.Name, nameStart) = 1)
            return workbook
    }
    return ""  ; Return empty if no suitable workbook was found
}

CalculateSumOfOrderedUnits6(excel, worksheetBR, worksheetVA) {
    rowCountBR := worksheetBR.UsedRange.Rows.Count
    ASINtoUnits := {}
    
    ; Collecting units from BusinessReport based on SKU conditions
    Loop, %rowCountBR% {
        rowBR := A_Index + 1
        ASIN := worksheetBR.Cells(rowBR, 2).Value
        SKU := worksheetBR.Cells(rowBR, 4).Value
        bestellteEinheiten := worksheetBR.Cells(rowBR, 15).Value

        If (!ASINtoUnits.HasKey(ASIN)) {
            ASINtoUnits[ASIN] := {"FBA": 0, "Standard": 0, "HasFBA": false}
        }
        
        If (StrLen(SKU) > 0) {
            If (InStr(SKU, "-FBA") and substr(SKU,-1) != "-B") {
                ASINtoUnits[ASIN]["FBA"] += bestellteEinheiten
                ASINtoUnits[ASIN]["HasFBA"] := true
            } Else If (substr(SKU,-1) != "-B") {
                ASINtoUnits[ASIN]["Standard"] += bestellteEinheiten
            }
        }
    }

    ; Transfer of the calculated units to the "Verkaufsliste Autohotkey" list
    Loop, 9 {
        rowVA := A_Index + 3
        ASIN := worksheetVA.Cells(rowVA, 2).Value
        If (ASINtoUnits.HasKey(ASIN)) {
            totalUnits := ASINtoUnits[ASIN]["HasFBA"] ? (ASINtoUnits[ASIN]["FBA"] + ASINtoUnits[ASIN]["Standard"]) : ASINtoUnits[ASIN]["Standard"]
            worksheetVA.Cells(rowVA, 3).Value := totalUnits
        } Else {
            worksheetVA.Cells(rowVA, 3).Value := 0  ; Set 0 if no units were found
        }
    }

    MsgBox, % "The transfer of the sums has been completed."
}

Everything is working fine, in the "SalesData" file it is checking column "B", line 4 to 12, for the matching ASIN from my "BusinessReport" file and is writing the correkt amount of totalUnits in column "D" in the "SalesData" file.

Now I wanted to enhance the script, by dynamically determining the target column "C,D,E,F..." in in the "SalesData" file in line 1 where the Month is written ( January '24, February '24 and so on...), the script automatically adapts to the right column by checking on the current month from my system time without the need for manual adjustments. If we have February, the values should be written in column "D", rows 4-12, if we have March in column "E" and so on.

This is my current code for that:

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

F2::
    ; Referencing an Excel application
    excel := ComObjActive("Excel.Application")
    
    ; Checking and referencing the required workbooks
    workbookBR := getWorkbookByName(excel, "BusinessReport")
    workbookVA := getWorkbookByName(excel, "Verkaufsliste Autohotkey")

    ; Check whether the workbooks have been found
    If (workbookBR = "" or workbookVA = "") {
        MsgBox, % "One or both of the required workbooks were not found."
        return
    }

    worksheetBR := workbookBR.Sheets(1)
    worksheetVA := workbookVA.Sheets(1)

    CalculateSumOfOrderedUnits(excel, worksheetBR, worksheetVA)
return

getWorkbookByName(excel, nameStart) {
    Loop, % excel.Workbooks.Count {
        workbook := excel.Workbooks(A_Index)
        If (InStr(workbook.Name, nameStart) = 1)
            return workbook
    }
    return ""  ; Return empty if no suitable workbook was found
}

CalculateSumOfOrderedUnits(excel, worksheetBR, worksheetVA) {
    month := A_Month  ; The current month as a number
    colVA := Chr(67 + month - 1)  ; Calculation of the corresponding column, where "C" stands for January
    
    rowCountBR := worksheetBR.UsedRange.Rows.Count
    ASINtoUnits := {}
    
    ; Collecting units from BusinessReport based on SKU conditions
    Loop, %rowCountBR% {
        rowBR := A_Index + 1
        ASIN := worksheetBR.Cells(rowBR, 2).Value
        SKU := worksheetBR.Cells(rowBR, 4).Value
        bestellteEinheiten := worksheetBR.Cells(rowBR, 15).Value

        If (!ASINtoUnits.HasKey(ASIN)) {
            ASINtoUnits[ASIN] := {"FBA": 0, "Standard": 0, "HasFBA": false}
        }
        
        If (StrLen(SKU) > 0) {
            If (InStr(SKU, "-FBA") and substr(SKU,-1) != "-B") {
                ASINtoUnits[ASIN]["FBA"] += bestellteEinheiten
                ASINtoUnits[ASIN]["HasFBA"] := true
            } Else If (substr(SKU,-1) != "-B")) {
                ASINtoUnits[ASIN]["Standard"] += bestellteEinheiten
            }
        }
    }

    ; Transfer of the calculated units to the "Verkaufsliste Autohotkey" list 
    Loop, 9 {
        rowVA := A_Index + 3
        ASIN := worksheetVA.Cells(rowVA, 2).Value
        If (ASINtoUnits.HasKey(ASIN)) {
            totalUnits := ASINtoUnits[ASIN]["HasFBA"] ? (ASINtoUnits[ASIN]["FBA"] + ASINtoUnits[ASIN]["Standard"]) : ASINtoUnits[ASIN]["Standard"]
            worksheetVA.Cells(rowVA, colVA).Value := totalUnits
        } Else {
            worksheetVA.Cells(rowVA, colVA).Value := 0  ; Set 0 if no units were found
        }
    }

    MsgBox, % "The transfer of the totals is complete."
}

For line 68

Code: Select all

worksheetVA.Cells(rowVA, colVA).Value := totalUnits

I am receiving the following error:

grafik.png (18.85 KiB) Viewed 184 times

Error: 0x80020005 - Typenkonflikt (Type conflict)
I can't see any further here at the moment and how this error can be rectified, or where it actually lies. Would someone take pity on me and help me again, please ?

[flyingDman]

What is A_month ? A_MM or A_Mon perhaps?

[Mindfork]

What is A_month ? A_MM or A_Mon perhaps?

That "A_month" part was the problem,

Code: Select all

A_MM

or

Code: Select all

A_Mon

both are working, again thank you very much.

Just out of curiosity, is my final code written cleanly or is that a "dirty code" ?

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

F2::
    ; Referencing an Excel application
    excel := ComObjActive("Excel.Application")
    
    ; Checking and referencing the required workbooks
    workbookBR := getWorkbookByName(excel, "BusinessReport")
    workbookVA := getWorkbookByName(excel, "Verkaufsliste Autohotkey")

    ; Check whether the workbooks have been found
    If (workbookBR = "" or workbookVA = "") {
        MsgBox, % "One or both of the required workbooks were not found."
        return
    }

    worksheetBR := workbookBR.Sheets(1)
    worksheetVA := workbookVA.Sheets(1)

    CalculateSumOfOrderedUnits(excel, worksheetBR, worksheetVA)
return

getWorkbookByName(excel, nameStart) {
    Loop, % excel.Workbooks.Count {
        workbook := excel.Workbooks(A_Index)
        If (InStr(workbook.Name, nameStart) = 1)
            return workbook
    }
    return ""  ; Return empty if no suitable workbook was found
}

CalculateSumOfOrderedUnits(excel, worksheetBR, worksheetVA) {
    month := A_MM  ; The current month as a number
    colVA := Chr(67 + month - 1)  ; Calculation of the corresponding column, where "C" stands for January
    
    rowCountBR := worksheetBR.UsedRange.Rows.Count
    ASINtoUnits := {}
    
    ; Collecting units from BusinessReport based on SKU conditions
    Loop, %rowCountBR% {
        rowBR := A_Index + 1
        ASIN := worksheetBR.Cells(rowBR, 2).Value
        SKU := worksheetBR.Cells(rowBR, 4).Value
        bestellteEinheiten := worksheetBR.Cells(rowBR, 15).Value

        If (!ASINtoUnits.HasKey(ASIN)) {
            ASINtoUnits[ASIN] := {"FBA": 0, "Standard": 0, "HasFBA": false}
        }
        
        If (StrLen(SKU) > 0) {
            If (InStr(SKU, "-FBA") and substr(SKU,-1) != "-B") {
                ASINtoUnits[ASIN]["FBA"] += bestellteEinheiten
                ASINtoUnits[ASIN]["HasFBA"] := true
            } Else If (substr(SKU,-1) != "-B") {
                ASINtoUnits[ASIN]["Standard"] += bestellteEinheiten
            }
        }
    }

    ; Transfer of the calculated units to the "Verkaufsliste Autohotkey" list 
    Loop, 9 {
        rowVA := A_Index + 3
        ASIN := worksheetVA.Cells(rowVA, 2).Value
        If (ASINtoUnits.HasKey(ASIN)) {
            totalUnits := ASINtoUnits[ASIN]["HasFBA"] ? (ASINtoUnits[ASIN]["FBA"] + ASINtoUnits[ASIN]["Standard"]) : ASINtoUnits[ASIN]["Standard"]
            worksheetVA.Cells(rowVA, colVA).Value := totalUnits
        } Else {
            worksheetVA.Cells(rowVA, colVA).Value := 0  ; Set 0 if no units were found
        }
    }

    MsgBox, % "The transfer of the totals is complete."
}

[flyingDman]

I do not see blatant errors or inefficiencies. Now if you have many rows in your spreadsheet (and I mean say > 1000), this code may be slow. This is because you read each cell using a COM call. In such cases I prefer to read all data into a safearray and extract data from that array. But if your speed is OK I would not change it.

[Mindfork]

Thanks for taking a look, I really appreciate it. I've also added a GUI and, I know I'm being annoying but, if it is possible could you please take another look at my "final" final code in case I've missed some sneaky bugs.

At the moment I'm happy with the speed, and, well, at least, I hope this will be useful for some others as well.

Code: Select all

SendMode Input  ; Sets the script to use the input method for sending keystrokes.
SetWorkingDir %A_ScriptDir%  ; Sets the script's working directory to where the script is located.

#SingleInstance force ; only run one instance of this script
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
SetBatchLines -1 ;max performance
ListLines Off ;max performance
;SetKeyDelay, 0 ;max key hits
#MaxThreadsPerHotkey 1

; Define mappings of countries to their row ranges in sheets 1 and 2.
countryRowMappings := {}
countryRowMappings["Germany"] := {1: [4, 12], 2: [4, 18]}
countryRowMappings["Spain"] := {1: [16, 24], 2: [22, 35]}
countryRowMappings["Baguette(France)"] := {1: [28, 36], 2: [39, 52]}

F2::  ; Hotkey to start the script.
    excel := ComObjActive("Excel.Application")  ; Get the active Excel application.
    workbookBR := getWorkbookByName(excel, "BusinessReport")  ; Get the BusinessReport workbook.
    workbookVA := getWorkbookByName(excel, "Verkaufsliste Autohotkey")  ; Get the Verkaufsliste Autohotkey workbook.

    ; Check if the required workbooks are open.
    If (workbookBR = "" or workbookVA = "") {
        MsgBox, % "One or both of the required workbooks were not found."
        return
    }

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

   ; GUI-Definition mit angepasstem Design
    Gui, New
    Gui, Font, s10 cBlack, Arial  ; Set font and color
    Gui, Color, FFFFFF  ; Set background color
    Gui, Add, Text, x10 y10 w300 h30 +Center, Bitte geben Sie das Land für den 'BusinessReport' ein:
	Gui, Add, DropDownList, x10 y50 w300 h300 vCountryChoice, Germany|Spain|Baguette(France)
	Gui, Add, Button, x10 y100 w300 h30 Default, OK
    Gui, Show, w320 h150, Work Work GUI
return

ButtonOK:  ; Action when the OK button is pressed.
    Gui, Submit  ; Get the user input from the GUI.
    global country := CountryChoice  ; Store the selected country.
    Gui, Destroy  ; Close the GUI.

    ; Data transfer logic based on the selected country.
    For index, sheetIndex in [1, 2]
    {
        rowRange := countryRowMappings[country][sheetIndex]
        startRow := rowRange[1]
        endRow := rowRange[2]
        worksheetVA := workbookVA.Sheets(sheetIndex)
        CalculateSumOfOrderedUnits(excel, workbookBR.Sheets(1), worksheetVA, startRow, endRow)
    }

    MsgBox, % "Data transfer for " country " completed. Press F2 to continue with a new country."
return

GuiClose:  ; Action when the GUI is closed.
    ExitApp

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

CalculateSumOfOrderedUnits(excel, worksheetBR, worksheetVA, startRow, endRow) {  ; Function to calculate and transfer total units.
    month := A_MM  ; Get the current month as a number.
    colVA := Chr(67 + month - 1)  ; Determine the target column based on the month.

    rowCountBR := worksheetBR.UsedRange.Rows.Count
    ASINtoUnits := {}

    ; Collect data from BusinessReport and calculate total units.
    Loop, %rowCountBR% {
        rowBR := A_Index + 1
        ASIN := worksheetBR.Cells(rowBR, 2).Value
        SKU := worksheetBR.Cells(rowBR, 4).Value
        bestellteEinheiten := worksheetBR.Cells(rowBR, 15).Value

        If (!ASINtoUnits.HasKey(ASIN)) {
            ASINtoUnits[ASIN] := {"FBA": 0, "Standard": 0, "HasFBA": false}
        }

        If (StrLen(SKU) > 0) {
            If (InStr(SKU, "-FBA") and substr(SKU,-1) != "B") {
                ASINtoUnits[ASIN]["FBA"] += bestellteEinheiten
                ASINtoUnits[ASIN]["HasFBA"] := true
            } Else If (substr(SKU,-1) != "B") {
                ASINtoUnits[ASIN]["Standard"] += bestellteEinheiten
            }
        }
    }

    ; Write the total units to the Verkaufsliste Autohotkey workbook.
    Loop, % endRow - startRow + 1 {
        rowVA := A_Index + startRow - 1
        ASIN := worksheetVA.Cells(rowVA, 2).Value
        If (ASINtoUnits.HasKey(ASIN)) {
            totalUnits := ASINtoUnits[ASIN]["HasFBA"] ? (ASINtoUnits[ASIN]["FBA"] + ASINtoUnits[ASIN]["Standard"]) : ASINtoUnits[ASIN]["Standard"]
            worksheetVA.Cells(rowVA, colVA).Value := totalUnits
        } Else {
            worksheetVA.Cells(rowVA, colVA).Value := 0
        }
    }
}

[flyingDman]

It's obviously hard to say without having the excel spreadsheets themselves but there is no obvious error or obvious potential improvement that I see.