Create Excel Packing Slips using CSV Data

[StaceyVECL]

Hi All,

I apologize if this has been asked previously but I can't seem to find the answer, but I may not be searching for the correct thing.

I have to create packing slips for orders being sent out. Currently, I am manually entering information (copy and pasting) into an Excel file that has required returns information and more information included as per our retailer's requirements. This is fine for 5 or 6 orders per day, however, we've recently expanded and we're now processing anything from 30-50 orders a day.

I can download the orders into a CSV file and the information I need is below.

I would like to know if there is a way I can use the CSV file and have Excel pull the information in and create multiple packing slips in one file that I can simply click print on. This would also need to work if a customer ordered 2 items which would show up as separate lines on the csv.

Order Date	Customer Name	Shipping Address 1	Shipping Address 2	Shipping Address 3	Shipping Zip	Item SKU	Item Qty Ordered	Channel Order Reference	Supplier Reference	Dispatch Date
11/12/2023​	Joe Bloggs	123 King Road	Here	There	NN12 D34	TVD12345	1​	ME23432343	TU123456789	12/12/2023​

 

[lrobbo314]

You can save all the CSVs into a folder and use Power Query to combine them all into a single table.

 

[StaceyVECL]

You can save all the CSVs into a folder and use Power Query to combine them all into a single table.

Thank you but I don't need a table.

Sorry, I probably haven't explained very well.

I would need each line of the CSV to be a new sheet in excel as a packing slip with page breaks for each new order.

 

[lrobbo314]

Get all the CSVs into a folder. Adjust the code below to point to that folder. Then, the code will add a new sheet for each CSV and get its contents into the workbook.

Not sure what page break stuff you want exactly.

Sub GETCSVS()
Dim wb As Workbook:     Set wb = ActiveWorkbook
Dim FSO As Object:      Set FSO = CreateObject("Scripting.FileSystemObject")
Dim fol As Object
Dim fil As Object

Set fol = FSO.getfolder("C:\YourPathHere")

For Each fil In fol.Files
    CSVtoEXCEL wb, fil.path, fil.Name
Next fil

End Sub

Sub CSVtoEXCEL(wb As Workbook, path As String, fileName As String)
Dim ws As Worksheet: Set ws = wb.Sheets.Add(after:=wb.Sheets(wb.Sheets.Count))
Dim CR As Integer:   CR = 1

ws.Name = fileName
Open path For Input As #1

Do Until EOF(1)
    Line Input #1, LineFromFile
    LineItems = Split(LineFromFile, ",")
    For j = 0 To UBound(LineItems)
        Cells(CR, j + 1).Value = LineItems(j)
    Next j
    CR = CR + 1
Loop

Close #1

End Sub

 

[StaceyVECL]

Get all the CSVs into a folder. Adjust the code below to point to that folder. Then, the code will add a new sheet for each CSV and get its contents into the workbook.

Not sure what page break stuff you want exactly.

Sub GETCSVS()
Dim wb As Workbook:     Set wb = ActiveWorkbook
Dim FSO As Object:      Set FSO = CreateObject("Scripting.FileSystemObject")
Dim fol As Object
Dim fil As Object

Set fol = FSO.getfolder("C:\YourPathHere")

For Each fil In fol.Files
    CSVtoEXCEL wb, fil.path, fil.Name
Next fil

End Sub

Sub CSVtoEXCEL(wb As Workbook, path As String, fileName As String)
Dim ws As Worksheet: Set ws = wb.Sheets.Add(after:=wb.Sheets(wb.Sheets.Count))
Dim CR As Integer:   CR = 1

ws.Name = fileName
Open path For Input As #1

Do Until EOF(1)
    Line Input #1, LineFromFile
    LineItems = Split(LineFromFile, ",")
    For j = 0 To UBound(LineItems)
        Cells(CR, j + 1).Value = LineItems(j)
    Next j
    CR = CR + 1
Loop

Close #1

End Sub

Thank you, however, I am a bit of a novice and have no idea where to put this information.

Also, it would be one CSV with multiple lines of information. Each line of the CSV would need to be a new packing slip (sheet) with individual customer and order information on.