PDA

View Full Version : Solved: WorksheetFunction.Rept or "0"?



Erdin? E. Ka
11-21-2006, 04:04 PM
Hi there,:hi:

I am trying write some data to txt from xls file.

But there is a problem.

The result of the current codes:
<TICKER>,<PER>,<DATE>,<TIME>,<HIGH>,<LOW>,<CLOSE>,<VOLUME>
ABANA.E,60,19940714,11248,0.032,0.029,0.030,24827
ABANA.E,60,19950714,12345,0.033,0.030,0.032,45496
ABANA.E,60,19960714,134556,0.034,0.030,0.032,7535
ABANA.E,60,19970714,45557,0.033,0.030,0.032,14289

My wish:

<TICKER>,<PER>,<DATE>,<TIME>,<HIGH>,<LOW>,<CLOSE>,<VOLUME>
ABANA.E,60,19940714,112408,0.032,0.029,0.030,24827
ABANA.E,60,19950714,120345,0.033,0.030,0.032,45496
ABANA.E,60,19960714,134556,0.034,0.030,0.032,7535
ABANA.E,60,19970714,045557,0.033,0.030,0.032,14289

In Column D, if Hour or Minute or Second's lenght 2 so it is ok. But i want to write 2 character even if lenght is 1.

I tried WorksheetFunction.Rept but i couldn't solve it.:dunno

Any ideas or help me please?:help

I attached the file.

Thanks in advance.:friends:

rbrhodes
11-21-2006, 05:49 PM
Hi,

Try formatting the cells containing the times as Custom using HHMMSS as the format.

Cheers,

dr

Erdin? E. Ka
11-21-2006, 06:19 PM
Hi rbrhodes,

I solved the problem now via If-End-If:


If Len(Hour(Cells(i, 4))) = 1 Then
H = "0" & Hour(Cells(i, 4))
Else
H = Hour(Cells(i, 4))
End If
If Len(Minute(Cells(i, 4))) = 1 Then
M = "0" & Minute(Cells(i, 4))
Else
M = Minute(Cells(i, 4))
End If

If Len(Second(Cells(i, 4))) = 1 Then
S = "0" & Second(Cells(i, 4))
Else
S = Second(Cells(i, 4))
End If


Thanks you very much. :hi: