• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Save CSV without headers

ButchJ

New Member
How can I save a csv file without the top header row?

I have a spreadsheet with up to 14 columns of data:

The column headers are: Name Addr Phon (for the first three columns now in use)

Total character count for row one is 12, that is A1, B1 & C1 all have 4 characters each.


I have a button macro to save the data name, phone and address data to a csv file without the column headers showing up in the csv file. This is the code and it works well generating the .csv file in a good format without the header row:


Private Sub CommandButton1_Click()

' Sub Save_CSV.csv

'

Dim iResponce As Integer

iResponce = MsgBox("previous CSV.csv will be overwritten - Is that OK?", vbYesNo, "Copy CSV.csv to filepath")

If iResponce = vbYes Then ' They Clicked YES!

Dim i As Long, rng As Range, txt As String, temp As String

Set rng = ActiveSheet.UsedRange

With CreateObject("VBScript.RegExp")

.Pattern = ",+$"

For i = 1 To rng.Rows.count

temp = Join(Evaluate("transpose(transpose(" & rng.Rows(i).Address & "))"), ",")

txt = txt & vbCrLf & .Replace(temp, "")

Next

End With

Open ThisWorkbook.Path & "CSV.csv" For Output As #1

Print #1, Mid$(txt, 19)

Close #1

End If

End Sub


The issue is when I add (or delete) columns of data with headers, the csv formatting is not correct because the number of header character changes. For example, if I add a forth column with ZIP in the header, I need to change the Print line of code to: Print #1, Mid$(txt, 24) to get the formatting correct again. That compensates for the additional three characters and a line break character.


Note: If you have 1 data column with one character in the header, the Print line will be: Print #1, Mid$(txt, 3) to correctly format the csv file without a header line.


If additional columns with headers are added (or deleted), is there a way to change VB code to automatically find the number of characters in row 1 and add (or subtract) “1” for each column added or deleted to that so the .csv file formatting will remain correct?


I use this formula just to determine how many characters are in the header row:

=LEN(A1)+LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+LEN(H1)+LEN(I1)+LEN(J1)

But it doesn’t count the extra characters need for line breaks.


Thanks for your help in advance with this.
 

Hui

Excel Ninja
Staff member
Butch

Wouldn't a simple change to the rows you process, do the job

ie: Change

For i = 1 To rng.Rows.count

to

For i = 2 To rng.Rows.count
 

ButchJ

New Member
Hui,


Great! With these two changes, the issue is solved.

For i = 2 To rng.Rows.count

Print #1, Mid$(txt, 3)

Now every time the csv file is saved, the formatting is exactly what I need.

The best part is now I don't have to deal with the character count in the header row.


Thanks for your help and quick response.

Butch
 
Top