Vendor Transport Comma and Quote Output

Vendor Transport Comma and Quote Output

Here are some directions that were sent to us by a user whose supplier was sending them an Excel file. We cannot support this method, we only offer it to you as an option if you are having trouble getting quotes and commas in your csv file.

1. Open the Excel file that your vendor sent you. Make any changes that you may need to. You may have to save it as a csv file at this point. Keep this file open.

2. Then press ALT+F11 (both at the same time). You will see this screen:


3. Click Insert and Module at the top of the screen:

macro insert

and you will see a white box:


4. Copy and paste all of the following macro code into the white box:

Sub CSVFile()

Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename(“”, “CSV File (*.csv), *.csv”)

ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ìî
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & “”"” & CurrCell.Value & “”"” & ListSep
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) – 1)
Print #1, CurrTextStr
Close #1
End Sub

5. Click Run at the top of the page and choose Run Sub/UserForm

You will be asked to Name and Save the file. Do that.

When you open the file it should have commas between and double quotes around each field.

It should now easily import into CostGuard.