Preserve file format from XLS to CSV

[shankar kumar]

Hi Everyone

I am using office 2007. I am trying to convert .xls/.xlsx file to .csv file.

I am using following piece of code:


Sub ConvertXLStoCSV()

Dim strXLSFile As String
Dim strCSVFile As String

'Change Input and Output folders to relevant location
Const strInputFolder As String = "C:\Documents and Settings\shankar_kumar\Desktop\VBA\"
Const strOutputFolder As String = "C:\Documents and Settings\shankar_kumar\Desktop\VBA\"

strXLSFile = Dir(strInputFolder & "*.xls")

Do While strXLSFile <> ""
strCSVFile = Left(strXLSFile, InStrRev(strXLSFile, ".")) & "csv"
Workbooks.Open strInputFolder & strXLSFile
ActiveWorkbook.SaveAs strOutputFolder & strCSVFile, xlCSV
ActiveWorkbook.Close False
strXLSFile = Dir
Loop

End Sub

It works absolutely fine.

However, there is one BIG problem.

One of the field in excel file has values say:6879450129070244523
But this value appears as 6880000000000000000 in the resulting csv file.

Is there any way to preserve the values of excel file in csv file? If yes, please tell me how to modify my code to make sure the preservation of values takes place?

Appreciate your co-operation.

Thanks
Shankar

 

[RoryA]

How are you checking the CSV file - in Excel or in Notepad?

 

[shankar kumar]

Hi Rory

I file that is posted was viewed in excel.

However, when I view the same csv file in notepad, it appears as 6.88E+18. This is the same value as excel one.

i.e. both are showing the same behavior.

Hope it helps.

Thanks
Shankar

 

[RoryA]

Is that how the data was actually displayed in the original Excel file? If so, you need to alter the formatting so that the displayed value is what you want before you save it as CSV.

 

[shankar kumar]

Hi Rory

The value appears as 6879450129070244523 in .xls file. However, as stated, the same value becomes 6.88E+18 in csv file.

You suggested, change the formatting in .xls file. Can you please suggest me to manipulate the vba code to affect this change?

I am doing as part of some automation. And so I can't afford to do things manually.

Hope things are clear now.

Thanks
Shankar

 

[RoryA]

That seems very strange. For that value to appear in an Excel cell, it would have to be formatted as text, and I cannot see why saving as a CSV would convert that to a number in exponent format unless you reopen the csv in Excel.

 

[shankar kumar]

Hi Rory

"That seems very strange. For that value to appear in an Excel cell, it would have to be formatted as text, and I cannot see why saving as a CSV would convert that to a number in exponent format unless you reopen the csv in Excel."

How to cross-check if my excel file (or say excel cell) is formatted as text and if it is not how to change its format to text.

If you have any snippet of VBA macro, please do help me with this.

Thanks
Shankar

 

[RoryA]

What I am saying is that if the number actually appears that way in Excel, the cell is formatted as text (or the number is preceded with an apostrophe so it becomes text) because Excel will not display more than 15 significant digits in a number value. Given that, I cannot see how saving as a CSV would produce a formatted number, nor can I replicate that behaviour in 2007.