null columns at end of csv

  • Thread starter Thread starter christm
  • Start date Start date
C

christm

Hi

I'm trying to get a comma delimited file out of excel to upload. My
file needs to have exactly 40 columns, the last 21 of which have a null
value. However when I view the csv file in notepad, my comma delimited
file stops after the last piece of data - therefore missing out all
these null columns. Is there a way I can force excel to take through
these columns even though there is no visable value in them?

Many thanks

Martin
 
one way:

Public Sub FixedFieldsCSV()
Const csDELIMITER As String = ","
Const cnNUMFIELDS As Long = 40
Dim rRecord As Range
Dim nField As Long
Dim nHandle As Long
Dim sOut As String

nHandle = FreeFile()
Open "Test.txt" For Output As #nHandle
For Each rRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rRecord
For nField = 1 To cnNUMFIELDS
sOut = sOut & csDELIMITER & .Cells(1, nField)
Next nField
Print #nHandle, Mid(sOut, 2)
sOut = Empty
End With
Next rRecord
Close #nHandle
End Sub
 
In order to get "exactly 40 columns" that is, 39 commas in every row, at
least one row has to have all 40 items. Then Excel will pad each shorter
row in the CSV to have 39 commas.
 
Back
Top