Sub ShopifyToXeroConversion5()
Dim SalesFile As Variant, SalesReport As Workbook, Xero As Workbook, TypeFile As Variant, TypeReport As Workbook, InvoiceRows As Long, tmp As String
Dim PaymentFile As Variant, FirstOrder As String, LastOrder As String, PaymentReport As Workbook, StartReportDate As Long, EndReportDate As Long
Dim LastPayRow As Long, XeroRow As Long, ReportDate As Date, strReportDate As String, EndReportTime As Double, StartReportTime As Double, XeroWETRetail As Double
Dim TotalFees As Double, DUP As Double, TotalSales As Double, QtyZero As Integer
Application.ScreenUpdating = False
'SELECT AND OPEN RELEVANT FILES
Set Xero = ThisWorkbook
Xero.Sheets("Temp").UsedRange.Clear
SalesFile = Application.GetOpenFilename(FileFilter:="CSV File (*.csv),*.csv", Title:="Choose a Shopify SALES report file to open")
If SalesFile = False Then
Exit Sub
End If
PaymentFile = Application.GetOpenFilename(FileFilter:="CSV File (*.csv),*.csv", Title:="Choose a SHOPIFY PAYMENT TRANSACTIONS report file to open")
If PaymentFile = False Then
Exit Sub
End If
InvoiceRows = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
Xero.Sheets("Xero Sales Invoice").Range("A2", "AA" & InvoiceRows).Delete
Set SalesReport = Application.Workbooks.Open(SalesFile)
Set PaymentReport = Application.Workbooks.Open(PaymentFile) 'List of card transactions that constitute the payment (does not show cash or $0 sales)
Dim LastSalesRow As Long, LastSalesCol As Long, TempRowsA As Long, TempRowsB As Long, SKU As String, c As Range, d As Range, TempListA As Range, OutOfBalance As Currency
Dim TempListB As Range, Qty As Single, Paid As String, sFormula As String, sDiscFormula As String, WineDiscFormula As String, TotalInvoiceValue As Currency
Dim AccCode As String, DataRows As Long, Desc As String, TaxType As String, e As Range, rReportTime As Range, cell As Range, PayoutAmount As Currency
Dim FirstInvRow As Long, LastInvRow As Long, Cash As Currency, LastPbTrow As Long, LastTypeRow As Long
'DETERMINE OR ENTER THE DATE THE INVOICE WILL BE WRITTEN
ReportDate = Mid(SalesFile, (InStr(10, SalesFile, "_")) + 1, 10)
ReportDate = Format(ReportDate, "dd/mm/yy")
strReportDate = Format(ReportDate, "dd mmmm yyyy")
'FIND COORDINATES AND CONSTANTS TO BE USED LATER
LastSalesRow = SalesReport.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LastSalesCol = SalesReport.ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, searchdirection:=xlPrevious).Column
LastPayRow = PaymentReport.ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
SalesReport.ActiveSheet.Range("C2:C" & LastSalesRow).SpecialCells(xlCellTypeVisible).Copy 'Copy Product Codes
Xero.Sheets("Temp").Range("A1").PasteSpecial Paste:=xlPasteValues
Xero.Sheets("Temp").Range("$A$1:A" & Cells(Rows.Count, "A").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
TempRowsA = Xero.Sheets("Temp").Range("A" & Rows.Count).End(xlUp).Row
DataRows = Xero.Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
Set TempListA = Xero.Sheets("Temp").Range("A1", "A" & TempRowsA)
For Each c In TempListA
SKU = c.Value
Desc = Application.WorksheetFunction.VLookup(SKU, Xero.Sheets("Data").Range("A4", "D" & DataRows), 2, False)
AccCode = Application.WorksheetFunction.VLookup(SKU, Xero.Sheets("Data").Range("A4", "D" & DataRows), 3, False)
TaxType = Application.WorksheetFunction.VLookup(SKU, Xero.Sheets("Data").Range("A4", "D" & DataRows), 4, False)
Qty = Application.WorksheetFunction.SumIfs(SalesReport.ActiveSheet.Range("D2 : D" & LastSalesRow), _
SalesReport.ActiveSheet.Range("C2:C" & LastSalesRow), SKU, _
SalesReport.ActiveSheet.Range("E2:E" & LastSalesRow), ">0")
QtyZero = Application.WorksheetFunction.SumIfs(SalesReport.ActiveSheet.Range("D2 : D" & LastSalesRow), _
SalesReport.ActiveSheet.Range("C2:C" & LastSalesRow), SKU, _
SalesReport.ActiveSheet.Range("E2:E" & LastSalesRow), "=0")
TotalSales = Application.WorksheetFunction.SumIf(SalesReport.ActiveSheet.Range("C2:C" & LastSalesRow), SKU, _
SalesReport.ActiveSheet.Range("J2 :J" & LastSalesRow))
DUP = TotalSales / Qty
XeroRow = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
If Qty > 0 Then
Xero.Sheets("Xero Sales Invoice").Range("A" & XeroRow + 1) = "Daily Sales Shopify"
Xero.Sheets("Xero Sales Invoice").Range("K" & XeroRow + 1) = "S" & Format(ReportDate, "yyyymmdd")
Xero.Sheets("Xero Sales Invoice").Range("M" & XeroRow + 1) = strReportDate
Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1) = "'" & strReportDate
Xero.Sheets("Xero Sales Invoice").Range("O" & XeroRow + 1) = SKU
Xero.Sheets("Xero Sales Invoice").Range("Q" & XeroRow + 1) = Qty
Xero.Sheets("Xero Sales Invoice").Range("R" & XeroRow + 1) = Round(DUP, 2)
Xero.Sheets("Xero Sales Invoice").Range("P" & XeroRow + 1) = Desc
Xero.Sheets("Xero Sales Invoice").Range("T" & XeroRow + 1) = AccCode
Xero.Sheets("Xero Sales Invoice").Range("U" & XeroRow + 1) = TaxType
End If
If QtyZero > 0 Then
XeroRow = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
Xero.Sheets("Xero Sales Invoice").Range("A" & XeroRow + 1) = "Daily Sales Shopify"
Xero.Sheets("Xero Sales Invoice").Range("K" & XeroRow + 1) = "S" & Format(ReportDate, "yyyymmdd")
Xero.Sheets("Xero Sales Invoice").Range("M" & XeroRow + 1) = strReportDate
Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1) = "'" & strReportDate
Xero.Sheets("Xero Sales Invoice").Range("O" & XeroRow + 1) = SKU
Xero.Sheets("Xero Sales Invoice").Range("Q" & XeroRow + 1) = QtyZero
Xero.Sheets("Xero Sales Invoice").Range("R" & XeroRow + 1) = 0
Xero.Sheets("Xero Sales Invoice").Range("P" & XeroRow + 1) = Desc
Xero.Sheets("Xero Sales Invoice").Range("T" & XeroRow + 1) = AccCode
Xero.Sheets("Xero Sales Invoice").Range("U" & XeroRow + 1) = TaxType
End If
Next c
'Apply Fees to Xero Invoice
TotalFees = Application.WorksheetFunction.Sum(PaymentReport.ActiveSheet.Range("J2:J" & LastPayRow))
XeroRow = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
Xero.Sheets("Xero Sales Invoice").Range("A" & XeroRow + 1) = "Daily Sales Shopify"
Xero.Sheets("Xero Sales Invoice").Range("K" & XeroRow + 1) = "S" & Format(ReportDate, "yyyymmdd")
Xero.Sheets("Xero Sales Invoice").Range("M" & XeroRow + 1) = strReportDate
Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1) = "'" & strReportDate
Xero.Sheets("Xero Sales Invoice").Range("O" & XeroRow + 1) = "SHOPIFYFEES"
Xero.Sheets("Xero Sales Invoice").Range("P" & XeroRow + 1) = "Shopify Credit Card Commission Fees"
Xero.Sheets("Xero Sales Invoice").Range("Q" & XeroRow + 1) = 1
Xero.Sheets("Xero Sales Invoice").Range("R" & XeroRow + 1) = -TotalFees
Xero.Sheets("Xero Sales Invoice").Range("T" & XeroRow + 1) = "SH-29999"
Xero.Sheets("Xero Sales Invoice").Range("U" & XeroRow + 1) = "GST on Expenses"
XeroRow = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row 'Update last Xero Invoice row
'XeroWETRetail = Application.WorksheetFunction.SumProduct(--(Xero.Sheets("Xero Sales Invoice").Range("T2:T" & XeroRow) = ""200-002""), _
Xero.Sheets("Xero Sales Invoice").Range("Q2:Q" & XeroRow), Xero.Sheets("Xero Sales Invoice").Range("R2:R" & XeroRow))
XeroWETRetail = Evaluate("SUMPRODUCT(--('Xero Sales Invoice'!T2:T" & XeroRow & "=""200-002"")," & _
"'Xero Sales Invoice'!Q2:Q" & XeroRow & ",'Xero Sales Invoice'!R2:R" & XeroRow & ")")