Question about saving in CSV format

[wayne ingalls]

I created an Excel file to make it easier to collect some data
at work. There are a few worksheet pages with instructions, some
illustrations and the page for the actual data. I made a custom
userform for entering the data, and do some calculations on the
user's input (convert between English and metric units;
calculate volume from length, width, depth; etc.)

The empty file is a bit large, so I added a button and code to
let the users quickly save in CSV format. My reasoning was that
it would be an easy way to delete all the VBA code and
additional worksheet pages since we only need the properly
formatted data, not the instructions or userform. I chose CSV
because it will launch Excel when double-clicked, so I don't
have to teach people about different file formats. Most of the
users want to launch attachments from their email by
double-clicking them. When I originally suggested saving in CSV
to make files smaller, I got blank stares except for one person
who asked if I couldn't give them a command button to do it for
them.

So the question that's bothering me and one of the more curious
users is this: The original file is 7 MB. If we enter only a
little bit of data, then save as CSV, the file is about 2 MB,
but we don't see anything other than our single line of text. If
we use File/Save As to save again as either CSV or Excel format,
the file shrinks to < 20 KB. Anyone have an idea what is
happening (not happening?) in that first save to CSV and whether
there is a way to eliminate the extra stuff that goes away with
the second save? I don't think I can make a button that does a
double save because the VBA code gets wiped out with the first
save to CSV.

A solution would be fantastic, but just an explanation of why we
see this would be enough to take care of our curiosity.

thanks!
-wayne

 

[Jim Gordon MVP]

Hi Wayne,

CSV works when the first row of a table is the headers and all
subsequent rows are data. Other arrangements won't work.

Try using Save As XML format instead.

-Jim

 

[wayne ingalls]

hi Jim

CSV works when the first row of a table is the headers and all
subsequent rows are data. Other arrangements won't work.

So if I have titles in the first two rows and a blank row before
the data, is that what's contributing to the extra large file size?

But again, if I save a second time as either CSV or Excel, then
the file size is what I expect it to be. The first two rows
still have titles and there's still a blank third row in this
second CSV file.

Try using Save As XML format instead.

I'm using Excel vX on the Mac and I believe the PCs have a
similar version. Save as XML isn't an option in vX.

-wayne