Open XLS file without it's number format using VBA?

[Dennis]

An XLS file has been saved with it's numbers formatted to a specified number of
decimal places. Using VBA how do you read in that XLS file with the numbers
unformatted and with the original number of decimal places?

Workbooks.Open (filename:="myFileName.xls", ????)

 

[Myrna Larson]

Not sure what you mean by "read in". The values are still in the cells. If you
open the workbook, then transfer the cell values from the worksheet to VBA,
you'll get the entire number, not the version that is rounded for viewing
purposes.

 

[Dennis]

Not sure what you mean by "read in". The values are still in the cells. If you
open the workbook, then transfer the cell values from the worksheet to VBA,
you'll get the entire number, not the version that is rounded for viewing
purposes.

That's true the entire number is still there.

However is you attempt to write the spreadsheet to a CSV file

using ActiveWorkbook.SaveAs filename:=MyNewFileName, _
FileFormat:=xlCSV, CreateBackup:=False

The numbers are saved in the csv file with the format and not as the original
number.

Maybe I should have asked How do you SaveAs an XLS file as a CSV file with
numbers in their original format?

Thanks for any help with this problem.

Dennis

 

[Dave Peterson]

How about removing the formatting from the cell?

Even better--how about applying the specific format you want, then saving as a
..csv file.

 

[Dennis]

Hi Dave,

I'd like the original format to be saved in the CSV file. Some numbers in the
XLS spreadsheet were originally 4 decimal places, some 5 and some 2. The
formatted SS changes the 2 decimal places to 1 and the 4 and 5 decimal places to
2. Thus when I attempt to SaveAs a csv file the CSV file has the formatted
version instead of the original format.

I tried

With .Sheets(1)
.Cells.Select
Selection.NumberFormat = "0.00000"
end with

But this just SaveAs all numbers with 5 decimal places in the CSV output
doubling the CSV file size.

Dennis

 

[Dave Peterson]

Once you've saved the data into a .csv file, you lose all formatting--it's just
plain text.

And when you reimport your data into excel, excel just treats it as
general--until you reformat the cells.

There isn't an option on the .saveas that changes this behavior.

When I do this kind of thing, it's usually one column of numbers formatted one
way and other columns formatted other ways--I don't usually have a mixture of
formats in the same column.

So I just format the columns the way I want to save them in the .CSV file. And
when I reopen it, I'll change the number formats again.

===
If you're going to reopen the data in excel, it might be easier to just save as
a normal .xls file.

It's usually better to use something like notepad to see how excel saved the
..csv file.

 

[Dennis]

When I use notepad to open the VBA created csv file the data is in formatted
form. For instance the unformatted number is 0.87 but the formatted number is
0.9 in the excel spreadsheet and the VBA created CSV file..

What I'm trying to do is to read in 100's of XLS files and write save them as
CSV files. I want those csv files to have the original unformatted numbers as
in the 0.87 example above. Now since there are 40 columns of data and quite a
number of these columns have been formatted with different decimal places it's
not possible to unformat in Excel each of the columns separately.

Is there a VBA command in Excel to "Unformat" all cells?

The funny thing is that when I load the XLS file into Excel and then SAVEAS a
csv file from the "File" menu then the new saved CSV file has the unformatted
numbers.

So why can't I do that with a VBA code?

Thanks

Dennis

 

[Frank Kabel]

Hi
what is your VBA code to save the files as *.csv file.

 

[Dennis]

Hi
what is your VBA code to save the files as *.csv file.

Hi Frank,

Here is my VBA code. Notice that I had to make *all* the number formats
"0.00000" in order to get the correct number of decimal places for all cells.
When you save this from Excel, Excel trims the zeros on the right hand side.
When you save the file using the VBA code below the right side zeros are not
trimmed. When you save the XLS file that has formatted numbers using the VBA
code below the CSV file is saved with the formatted numbers. When you save the
XLS file from Excel from the "File" menu with SaveAs then Excel saves the
unformatted numbers. Thus there must be an option that Excel executes that the
VBA code does not include but I can't find what it is.

With .Sheets(1)
.Cells.Select
Selection.NumberFormat = "0.00000"
end with

ActiveWorkbook.SaveAs filename:="MyFile.csv", _
FileFormat:=xlCSV, CreateBackup:=False

 

[Dave Peterson]

There's not a command to Unformat all the cells, but you could apply the General
format--it sounds that's what you mean by "unformat"

worksheets("sheet1").cells.numberformat="General"

Then do your saveas .csv file.

You could even copy the worksheets to a different workbook first.

Option Explicit
Sub testme01()
Dim wks As Worksheet
Set wks = Worksheets("sheet1")
wks.Copy 'to a new workbook
With ActiveSheet
.cells.numberformat="general"
with .Parent
.SaveAs Filename:="whateveryousaveitas.CSV", FileFormat:=xlCSV
.Close savechanges:=False
end with
End With
Workbooks.Open Filename:="whateveryousaveitas.CSV"
End Sub

This way you won't alter the "real" excel file.

 

[Dennis]

Thanks Dave.

You know I tried the:

With .Sheets(1)
.Cells.Select
Selection.NumberFormat = "General"

before and it truncated all my decimal numbers to 1.

However when I just tried it again after your suggestion then it saved the
numbers correctly in the CSV file in their unformatted state. Maybe I didn't
put the quotation marks around the General the first time I tried it?

Anyway, NumberFormat="General" works and this solves the problem.

Thanks again.

Dennis

 

[Dave Peterson]

My bet is if you widened the columns, you would have seen all those decimals.

And instead of selecting, you could have just formatted all the cells:

I think that this makes it easier to see what's going on--instead of using the
..selects.