Missing commas in csv save

[AC]

Hi

I have a question about saving to csv files, in some circumstances a
leading "," is being dropped and I wanted to know if there is a way
around it.

Explanation of situation first, then the specific question releated to
the situation at the end.


I have some code which copies a table from an Excel workbook, pastes
it into a new workbook, and then saves that workbook as a csv file:

Code something like:
Application.Goto Reference:=strDataRange
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
strOutpath & "\" & strDataRange & ".csv", _
FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWindow.Close SaveChanges:=False


Of particular interest is a table which has a heading row where the
first column has no header at all.
eg <blank> myFirstHeaderCol mySecondHeaderCol etc
data1 data2 data3
data4 data5 data6
etc

Normally this gets saved into a csv file as:
,myFirstHeaderCol,mySecondHeaderCol,<etc>
data1,data2,data3
data4,data5,data6
etc

The important point is that leading "," on the first line

This output is exactly what I want.



Things get interesting if this table has no data. In these cases the
range is still specified over the table, it is just the table is empty
bar the header row.

When I do the csv save in this case I get the following:
myFirstHeaderCol, mySecondHeaderCol,<etc>

So excel has dropped the first ","

I suppose that without data in the rest of the table Excel didnt know
that I wanted that first column, ie it thought it was doing me a
favour by dropping it like it also drops empty rows etc.


QUESTION:
Is there any way I can get Excel to include every comma even if
subsequent rows etc for that column are empty?

Thanks
Andy C

 

[Dave Peterson]

Put something in that cell.

I like to use:
=""

The cell looks empty, but if I select it, I can see the formula and remember why
I did it.

 

[Jim Rech]

Excel .. thought it was doing me a favour by dropping it like it also
Sort of but I'd put it this way - When you save a CSV Excel writes the 'used
range' to the file. People commonly think that the used range always starts
at A1 but it doesn't necessarily. In a new sheet if you enter data from B1
down and right and then do this in the Immediate window:

?sheet1.UsedRange.Address

you get something like this:

$B$1:$F$14

So doing what Dave suggested will include A1 in the used range.

--
Jim
| Hi
|
| I have a question about saving to csv files, in some circumstances a
| leading "," is being dropped and I wanted to know if there is a way
| around it.
|
| Explanation of situation first, then the specific question releated to
| the situation at the end.
|
|
| I have some code which copies a table from an Excel workbook, pastes
| it into a new workbook, and then saves that workbook as a csv file:
|
| Code something like:
| Application.Goto Reference:=strDataRange
| Selection.Copy
| Workbooks.Add
| Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
| SkipBlanks:= _
| False, Transpose:=False
| Application.CutCopyMode = False
| Application.DisplayAlerts = False
| ActiveWorkbook.SaveAs Filename:= _
| strOutpath & "\" & strDataRange & ".csv", _
| FileFormat:=xlCSV, _
| CreateBackup:=False
| ActiveWindow.Close SaveChanges:=False
|
|
| Of particular interest is a table which has a heading row where the
| first column has no header at all.
| eg <blank> myFirstHeaderCol mySecondHeaderCol etc
| data1 data2 data3
| data4 data5 data6
| etc
|
| Normally this gets saved into a csv file as:
| ,myFirstHeaderCol,mySecondHeaderCol,<etc>
| data1,data2,data3
| data4,data5,data6
| etc
|
| The important point is that leading "," on the first line
|
| This output is exactly what I want.
|
|
|
| Things get interesting if this table has no data. In these cases the
| range is still specified over the table, it is just the table is empty
| bar the header row.
|
| When I do the csv save in this case I get the following:
| myFirstHeaderCol, mySecondHeaderCol,<etc>
|
| So excel has dropped the first ","
|
| I suppose that without data in the rest of the table Excel didnt know
| that I wanted that first column, ie it thought it was doing me a
| favour by dropping it like it also drops empty rows etc.
|
|
| QUESTION:
| Is there any way I can get Excel to include every comma even if
| subsequent rows etc for that column are empty?
|
| Thanks
| Andy C