XERO的Quote和Invoice导入DEC (dhlexpresscommerce)

查看相关主题:

需求:

朋友的公司使用XERO创建订单，要通过DHL的DEC (dhlexpresscommerce)系统发货。

XERO的Quote和Invoice长这样



DEC的csv文件模板长这样

https://dhlexpresscommerce.com/ClientBin/dhl_csv_template.csv


XERO的Quote没有导出csv格式的功能；INVOICE有导出csv的功能，但是导出来的文件里面没有收件人的地址信息，反而有寄件人的信息。

那只能从页面上手工复制这些信息了。用Excel做转换。

参考：

通过索引号引用工作表
https://docs.microsoft.com/zh-cn/office/vba/excel/concepts/workbooks-and-worksheets/refer-to-sheets-by-index-number

使用 A1 表示法引用单元格和区域
https://docs.microsoft.com/zh-cn/office/vba/excel/concepts/cells-and-ranges/refer-to-cells-and-ranges-by-using-a1-notation

使用索引编号来引用单元格
https://docs.microsoft.com/zh-cn/office/vba/excel/concepts/cells-and-ranges/refer-to-cells-by-using-index-numbers

使用 Do...Loop 语句
https://docs.microsoft.com/zh-cn/office/vba/language/concepts/getting-started/using-doloop-statements

实施：

Sub button1_Click()
    'Load param
    input_To_Name = Worksheets(2).Range("B1").Value
    input_Destination_Email = Worksheets(2).Range("B2").Value
    input_Destination_Phone = Worksheets(2).Range("B3").Value
    
    input_Destination_Street = Worksheets(2).Range("B5").Value
    input_Destination_City = Worksheets(2).Range("B6").Value
    input_Destination_Postcode = Worksheets(2).Range("B7").Value
    input_Destination_State = Worksheets(2).Range("B8").Value
    input_Destination_Country = Worksheets(2).Range("B9").Value
    
    input_Order_Number = Worksheets(2).Range("B11").Value
    input_Date = Worksheets(2).Range("B12").Value
    
    input_Shipping_Method = Worksheets(2).Range("B14").Value
    input_Company = Worksheets(2).Range("B15").Value
    input_Code = Worksheets(2).Range("B16").Value
    input_Contents = Worksheets(2).Range("B17").Value
    input_Country_of_Manufacturer = Worksheets(2).Range("B18").Value
    
    'Load order
    i = 2
    Do While Worksheets(1).Cells(i, 2).Value <> ""
        input_Item_Name = Worksheets(1).Cells(i, 2).Value
        input_Qty = Worksheets(1).Cells(i, 3).Value
        input_Item_Price = Worksheets(1).Cells(i, 4).Value
        
        'Write Result
        Worksheets(3).Cells(i, 1).Value = input_Order_Number
        Worksheets(3).Cells(i, 2).Value = input_Date
        Worksheets(3).Cells(i, 3).Value = input_To_Name
        Worksheets(3).Cells(i, 5).Value = input_Destination_Street
        Worksheets(3).Cells(i, 7).Value = input_Destination_City
        Worksheets(3).Cells(i, 8).Value = input_Destination_Postcode
        Worksheets(3).Cells(i, 9).Value = input_Destination_State
        Worksheets(3).Cells(i, 10).Value = input_Destination_Country
        Worksheets(3).Cells(i, 11).Value = input_Destination_Email
        Worksheets(3).Cells(i, 12).Value = input_Destination_Phone
        Worksheets(3).Cells(i, 13).Value = input_Item_Name
        Worksheets(3).Cells(i, 14).Value = input_Item_Price
        Worksheets(3).Cells(i, 17).Value = input_Shipping_Method
        Worksheets(3).Cells(i, 20).Value = input_Qty
        Worksheets(3).Cells(i, 21).Value = input_Company
        Worksheets(3).Cells(i, 32).Value = input_Code
        Worksheets(3).Cells(i, 35).Value = input_Contents
        Worksheets(3).Cells(i, 37).Value = input_Country_of_Manufacturer
        
        i = i + 1
    Loop


End Sub


GitHub

https://github.com/crazypeace/XERO-Quote-or-Invoice-to-DEC-csv

评论
发表评论