Leading zeroes in times (Excel/csv)

[Dagfish]

I am about to receive hundreds of Excel spreadsheets, of which one of the five columns is a time in the format hh:mm
The format of this source file is fixed and can not be changed.

I need to open each of the spreadsheets and save as a csv file. If I do this manually, I experience no problems at all - the resulting .csv file can be viewed in notepad with no anomalies.

However, on using a simple macro to do this, I find that when I view the resulting .csv in Notepad, all the leading zeroes are missed from the time data - i.e. 09:45 becomes 9:45. (The macro is reproduced below).

I need to retain the time in the hh:mm format with the leading zero. Any manipulation of the time field results in it being converted to a number (e.g. 09:45 becomes 0.40625).

I don't particularly want to open each of the files I receive (or convert) as there are likely to be hundreds of them.

Any ideas how to retain the original formatting?

Thanks,
David.

Dim File_Names As Variant
Dim File_count As Integer
Dim Active_File_Name As String
Dim Counter As Integer
Dim File_Save_Name As Variant

File_Names = Application.GetOpenFilename(, , , , True)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

File_count = UBound(File_Names)
Counter = 1

Do Until Counter > File_count
Active_File_Name = File_Names(Counter)
Workbooks.Open Filename:=Active_File_Name
Active_File_Name = ActiveWorkbook.Name
File_Save_Name = InStr(1, Active_File_Name, ".xls", 1) - 1
File_Save_Name = Mid(Active_File_Name, 1, File_Save_Name) & ".csv"
ActiveWorkbook.SaveAs Filename:=File_Save_Name, FileFormat:= _
xlCSV
ActiveWindow.Close
Counter = Counter + 1
Loop

 

[xlbo]

Does it have to be saved as CSV?

If you can save it as .txt it would eliminate the issue - otherwise you will probably need to convert all the values to text 1st - not format - actually force a convert e.g.

=text(A2,"hh:mm")

then copy/paste values and save after that...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[robmazco]

While I dont have an answer to your question, it still confuses me why Excel wont allow a user to keep those leading zeros in. For years this has befuddled me and it will proly ot do so forever...
I cannot grasp why the user should not have control over what they see in an excel spreadsheet.

 

[AnotherHiggins]

I cannot grasp why the user should not have control over what they see in an excel spreadsheet.

The user does.

For a date, format the cell as "mm/dd" to always display two digits for both month and day.

For 'regular' numbers, format the cell as Text.

Or if you want to do arithmetic with the value in the cell and still wish to display leading zeros, you could format to Custom > "0000", which would display 20 as 0020, but still store it as a number.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

robmazco,

As John as so succinctly pointed out, the user has a great deal of control over what is DISPLAYED. If the user makes no overt choice, as in John's examples, Excel uses a default display.

As a rule, numbers never have leading or trailing zeros displayed. A file transfer to or from a mainframe application may require leading/trailing zeros or impled decimal, because the I/O is using numeric CHARACTERS in a fixed width file to fit the PIC in the mainfram app declarations. In cases like this, you have NUMERIC CHARACTERS (TEXT) in the cells and not numbers. A mere display format will not suffice.



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

...and to clarify further, a CSV file is a comma seperated VALUES file. If it sees a number (ie a VALUE), it will chop off the leading zeroes - and given that dates are in fact numbers, it is actually more of a puzzle as to why it does not chop off the leading zeroes when you save manually then why it does so when you save using code - hence my suggestion to use a .txt file instead which will happily take whatever it sees in the cell!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Dagfish]

Thanks guys - I sorted it by saving the CSV file, then formatting the appropriate column in the CSV file to hh:mm and ensuring that this new format was also saved. The altered bit of code can be seen below.

The file can then be opened successfully in Notepad.

Do Until Counter > File_count
Active_File_Name = File_Names(Counter)
Workbooks.Open Filename:=Active_File_Name
Active_File_Name = ActiveWorkbook.Name
File_Save_Name = InStr(1, Active_File_Name, ".xls", 1) - 1
File_Save_Name = Mid(Active_File_Name, 1, File_Save_Name) & ".csv"
ActiveWorkbook.SaveAs Filename:=File_Save_Name, FileFormat:=xlCSV

Columns("D").Select
Selection.NumberFormat = "hh:mm"
ActiveWorkbook.Save

ActiveWindow.Close
Counter = Counter + 1
Loop