Making Packing Slips With Excel Data

i am trying to make packing slips with a tab deliminated flie, does anyone know if there is a way to have excel autofill a packing slip template in excel or word?

the only way i can get the data is to export it from my websites backend in tab - it allows me to export the header fields too - it is customer order information - each piece of information is listed on single row - if a customer orders one product it puts all their info in one row and lists that data under the proper column heading - if a customer orders 3 products is duplicates all the customer info in three rows only changing the item number, item description and weight please see the example below, it has been exported as pipe deliminated - i would like to be able to import or export this data to make packing slips - if anyone knows of any software out there that will allow me to import excel data and populate a packing slip please let me know - thanks in advance!

order number|email|first name|last name|company|address 1|address 2|country|city|state|postal code|day phone|shipping method|signature service|editable 1|editable 2|quantity|item number

Customer ordering 1 item...
200804021426396456|[email protected]|Chris|Feik||180 Ave of the Flags #195||USA|Buellton|California|93427|805-636-1777|1. US Ground|No|||1|BFSSCLEARCI


Customer ordering 4 items...
200804021355000554|[email protected]|carole|cotton|happy balloons parties|11080 sw 69 drive||USA|miami|Florida|33173|305-596-0712|1. US Ground|No|||3|BFSCLIMECI
200804021355000554|[email protected]|carole|cotton|happy balloons parties|11080 sw 69 drive||USA|miami|Florida|33173|305-596-0712|1. US Ground|No|||4|BFSSLIMECI
200804021355000554|[email protected]|carole|cotton|happy balloons parties|11080 sw 69 drive||USA|miami|Florida|33173|305-596-0712|1. US Ground|No|||2|66627
200804021355000554|[email protected]|carole|cotton|happy balloons parties|11080 sw 69 drive||USA|miami|Florida|33173|305-596-0712|1. US Ground|No|||1|BFS4SET

Run the procedure DoTheImport. It looks for csv files in a directory you choose. The separator used is |. If you are using another, the tab is vbtab without the " " (I think). Now that you have the ordernumbers, you can check on the next. If it's a different one, create a new slip and continue.

Give a sample slipstatement if you want some guidelines to fill that out for the imported data.

Public Sub DoTheImport()
'Name of your csv file
    Dim FName As Variant
'String to hold your separation character
    Dim Sep As String
    FName = Application.GetOpenFilename _
    (filefilter:="CSV Files(*.csv),*.csv,All Files (*.*),*.*")
'(filefilter:="TXT Files(*.txt),*.txt,All Files (*.*),*.*")
    If FName = False Then
        MsgBox "You didn't select a file"
        Exit Sub
    End If
    Sep = "|"
    ImportTextFile CStr(FName), Sep
End Sub

Public Sub ImportTextFile(FName As String, Sep As String)
'row
Dim Rw As Long
'columnno
Dim Cno As Long
'line of data of file
Dim WholeLine As String
'worksheet to import to
Dim WS As Worksheet
'array to hold every item of the line
Dim txt As Variant
'a free file no
Dim fileno As Long
Application.ScreenUpdating = False
fileno = FreeFile
On Error GoTo EndMacro:
Set WS = ActiveSheet
Rw = WS.Cells(Rows.Count, 1).End(xlUp).Row
Open FName For Input Access Read As #fileno
While Not EOF(fileno)
    Line Input #fileno, WholeLine
    txt = Split(WholeLine, Sep)
    For Cno = LBound(txt) To UBound(txt)
        If IsNumeric(txt(Cno)) Then
'to avoid the mathematic notation ex. E+2
            WS.Cells(Rw, Cno + 1).Value = "'" & txt(Cno)
        Else
            WS.Cells(Rw, Cno + 1).Value = txt(Cno)
        End If
    Next Cno
    Rw = Rw + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #fileno
'remove empty rows and rows starting with Customer
For Cno = Rw To 1 Step -1
    If WS.Range("A" & Cno).Value = vbNullString Or _
       Left(WS.Range("A" & Cno).Value, 8) = "Customer" Then
        WS.Range("A" & Cno).EntireRow.Delete
    End If
Next Cno
'fit the columns so you could read everything
WS.Range("A1:R" & Rw).Columns.AutoFit
End Sub

Charlize

Excellent!

My question is how would you export data from the results of the above macro and use it in MS WORD Mail Merge?

I wouldn't use a mailmerge myself. Because the customer could order multiple things. Since each row would be a record in a mailmerge it's rather difficult to create something using mailmerge.

- I would use a boilertemplate in Word with fixed locations that are named (bookmarks). Something like address, customerno, total, vat ...
- Boilertemplate is a word document and you open it by declaring an object as word document from within excel.
- With this object you could jump to bookmarks and insert the cell contents on that location
- the actual items of the slip are inserted at the bookmark named sliplines (or something you want) with vbtab (if keyword for a tab is this) and each line is ended with a vbcrline. You repeat this in a loop until a new customerno is encountered in your excel file (or last line in your list).
- Save the word document under a new name (orderno + customerid + date ???) and close word object (boilertemplate isn't changed)
- Repeat this until you are at the last row in your list.

Just an idea.

Charlize