Exporting Time Formatted Numbers inside Macros

[Guest]

I have a Macro that exports certain pieces of data into a ".TXT" file.
Everything is working well, but today, I am trying to add to columns to the
export file, both of which are stored as Custom "h:mm" time values.

Currently, for example, the following values are in the spreadsheet, and
when they arrive in mt text file, they look as follows:

6:15 ------> .260416666666667
15:06 -----> .629166666666667
6:45 ------> .28125
16:51 -----> .702083333333333

The commands to capture the values to be exported in my macro as as follows:

ST2 = Range("K" + currow2).Value
ET2 = Range("L" + currow2).Value

Column K contains the 6:15 and 6:45 values from above, and column L contains
15:06 and 16:51 (obviously all the values are in different rows of the
different columns)

I would like the export file to simply show 6:15, 15:06, 6:45, 16:51, as
they are in the spreadsheet.

 

[Guest]

Post the code you're using for your macro.

 

[Guest]

Here is the section of the code that is doing the export. I apologize in
advance to all the real programmers out there...I am a legitimate "hacker",
not a coder. I am sure you will be able to recognize all sorts of flaws!

-------------------------------------
Sheets("Day").Select
nDate2 = Range("C2").Value
nDate = CStr(nDate2)
ncurrow2 = 9
currow2 = CStr(ncurrow2)

Nemp2 = Range("A" + currow2).Value
Emp2 = CStr(Nemp2)
NTruck = Range("E" + currow2).Value
NTruck2 = CStr(NTruck)
RT2 = Round(Range("W" + currow2).Value, 2)
OT2 = Round(Range("X" + currow2).Value, 2)
DT2 = Round(Range("Y" + currow2).Value, 2)
XT2 = Round(Range("V" + currow2).Value, 2)
ST2 = Range("K" + currow2).Value
STTxt = CStr(ST2)
ET2 = Range("L" + currow2).Value
ETTxt = CStr(ET2)


If Dir("\\odcdata\Hourly Payroll\SQLImports\" + savedfile2) <> "" Then
Kill ("\\odcdata\Hourly Payroll\SQLImports\" + savedfile2)
Open "\\odcdata\Hourly Payroll\SQLImports\" + savedfile2 For Output As #2
Do While Emp2 <> ""
If XT2 = 0 Then 'ie if there is no stat holiday
If RT2 > 0 Then
Write #2, "Readymix", nDate, Emp2, NTruck, "REG", RT2,
STTxt, ETTxt
End If
If OT2 > 0 Then
Write #2, "Readymix", nDate, Emp2, NTruck, "OT", OT2, STTxt,
ETTxt
End If
If DT2 > 0 Then
Write #2, "Readymix", nDate, Emp2, NTruck, "DT", DT2, STTxt,
ETTxt
End If
End If
If XT2 > 0 Then
Write #2, "Readymix", nDate, Emp2, NTruck, "Stat", XT2, STTxt,
ETTxt
End If

ncurrow2 = ncurrow2 + 1
currow2 = CStr(ncurrow2)
Nemp2 = Range("A" + currow2).Value
Emp2 = CStr(Nemp2)
RT2 = Round(Range("W" + currow2).Value, 2)
OT2 = Round(Range("X" + currow2).Value, 2)
DT2 = Round(Range("Y" + currow2).Value, 2)
XT2 = Round(Range("V" + currow2).Value, 2)
ST2 = Range("K" + currow2).Value
STTxt = CStr(ST2)
ET2 = Range("L" + currow2).Value
ETTxt = CStr(ET2)
NTruck = Range("E" + currow2).Value
NTruck2 = CStr(NTruck)
Loop
Close #2

 

[David Biddulph]

I don't know about all your macro stuff, but isn't the easiest way to have a
helper column of the form =TEXT(A1,"h:mm") , and export that?

 

[Guest]

Thanks. I can probably do that, but I inherited these spreadsheets and I am
leery of adding columns that don't already exist. I am hoping that someone
can tell me how to code your suggestion in a Macro, because that is basically
how I feel the answer can be derived...just not sure on the syntax, at least
I hope it is just a "syntax" issue.

Thanks for the response.