skip to Main Content

I have two excel files, one is an export from Shopify with all the data of my products. The other is a list of barcodes and quantities that are available from the supplier.

I want to be able to modify the quantities in the first file to reflect the real inventory quantity available in the second file.

Example:

File1.xlsx screenshot:

File1.xlsx

File2.xlsx screenshot:

File2.xlsx

The end file Result.xlsx, with the updated quantities, should be:

Result.xlsx

It is probably a for loop that do a copy paste. A Vba macro solution would be the best solution but I am open to other technologies that can anyway get to the desired result.

Few points to note:

  1. There are products that are in the file 1 and not in the file 2, those should not be changed
  2. There are products that are in the file 2 and not in the file 1, those should not be changed
  3. File 1 and file 2 are not in order (and should not be sorted for any reason), and the number of rows is different from file 1 and file 2
  4. The product could be in both files, therefore the change HAS to occur and the quantity has to be updated, but the row number in the two files is probably different (the same barcode can be in row 1 in one file, and in row 3 in the second file)
  5. The real files have thousands of rows, so the code has to be designed to go through a large number of entries

It is simply to update the quantities of file 1 with the quantities of file 2, if and only if the barcode is present in both files.

Any solution for a for loop that can do this quantity update on a large scale?

Thank you a lot!!

2

Answers


  1. The following code assumes that File1.xlsx and File2.xlsx are the same folder as the workbook containing the code. It will produce Result.xlsx in that folder as well.

    Sub update_quantites()
    
        Dim wbShopify As Workbook
        Dim wbSupplier As Workbook
        Dim wbResult As Workbook
        Dim filePath As String
        
        Dim foundcell As Range
        Dim row As Long
        Dim barcode As Long
        Dim supplierQty As Integer
        
        
        filePath = ThisWorkbook.Path & Application.PathSeparator
      
        Set wbShopify = Workbooks.Open(filePath & "File1.xlsx")
        Set wbSupplier = Workbooks.Open(filePath & "File2.xlsx")
        Set wbResult = Workbooks.Add
        wbShopify.Worksheets(1).Copy after:=wbResult.Worksheets(1)
        Application.DisplayAlerts = False
        wbResult.Worksheets(1).Delete
        wbShopify.Close False
        
        
        ' iterate across the data in the supplier workbook
        row = 2
        Do Until wbSupplier.Worksheets(1).Cells(row, 1).Value = ""
            barcode = wbSupplier.Worksheets(1).Cells(row, 1).Value
            supplierQty = wbSupplier.Worksheets(1).Cells(row, 2).Value
            ' find the barcode on the shopify sheet
            Set foundcell = wbResult.Worksheets(1).Columns(2).Find(barcode, , , xlWhole)
            If foundcell Is Nothing Then
                Debug.Print "Barcode " & barcode & " not found on shopify sheet"
            Else
                foundcell.Offset(0, -1).Value = supplierQty
            End If
            row = row + 1
        Loop
        
        wbResult.SaveAs filePath & "Result.xlsx"
        wbResult.Close
        wbSupplier.Close
    
    End Sub
    
    Login or Signup to reply.
  2. You may use Pandas for this.

    Note: first select File1 then File2

    import tkinter as tk
    from tkinter import filedialog
    import pandas as pd
    import time
    
    start = time.process_time()
    
    root= tk.Tk()
    canvas1 = tk.Canvas(root, width = 300, height = 300, bg = 'lightsteelblue')
    canvas1.pack()
    
    def getExcel ():
        x1 = filedialog.askopenfilename()
        File1 = pd.ExcelFile (x1)
        print('File is selected')
        x2 = filedialog.askopenfilename()
        File2 = pd.ExcelFile (x2)
        print(time.perf_counter() - start)
        print('File is selected')
        print('Still Working on the file...')
        df1 = pd.read_excel(File1, 'Sheet1')
        df2 = pd.read_excel(File2, 'Sheet1')
        result = pd.merge(df2, df1, how="outer", on="Barcode")
        result.Quantity_x.fillna(result.Quantity_y, inplace=True)
        result.drop('Quantity_y', axis=1, inplace=True)
        result.rename(columns={'Quantity_x':'Quantity'}, inplace=True)
        result.to_csv("result.csv", encoding='utf-8', index=False)
        print(time.perf_counter() - start)
        print('Finished!')
    
        
    browseButton_Excel = tk.Button(text='Import Excel File', command=getExcel, bg='green', fg='white', font=('helvetica', 12, 'bold'))
    canvas1.create_window(150, 150, window=browseButton_Excel)
    
    root.mainloop()
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search