Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exporting Time Formatted Numbers inside Macros
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exporting Time Formatted Numbers inside Macros
Post the code you're using for your macro.
-- Brevity is the soul of wit. "Don" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exporting Time Formatted Numbers inside Macros
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 ----------------------------------------------------- "Dave F" wrote: Post the code you're using for your macro. -- Brevity is the soul of wit. "Don" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exporting Time Formatted Numbers inside Macros
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? -- David Biddulph "Don" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exporting Time Formatted Numbers inside Macros
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. "David Biddulph" wrote: 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? -- David Biddulph "Don" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting time from cell in 24 hr format | Excel Worksheet Functions | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Converting Numbers to Time | Excel Worksheet Functions | |||
update row numbers after different active cells in macros followi. | Excel Discussion (Misc queries) | |||
entering numbers to display a time format | Excel Discussion (Misc queries) |