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.
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.
