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.) 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
}
}
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
}
}
Or if I insert
Code: Select all
= 0
First Code:
Code: Select all
If (StrLen(SKU) > 0 and !InStr(SKU, "-B") =0)
Code: Select all
If (InStr(SKU, "-FBA") and !InStr(SKU, "-B") = 0)
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.