Wilder1626
asked on
Number format issue
Hello all
I have a macro in excel that generate a txt file for all rows with data.
But i have an issue with 1 single value
In my cells from column E, starting at row 7, i will have a value with this format:
201204081900 = YYYYMMDDHHMM
hrs would
The isssue i have is that i would like to add 7 hours to it. This should also respect the date.
Ex: If date is 20120408 and time at 2300, adding 7 hrs would be 201204090600.
How can i do that?
It just need to send the new date and time in the txt file.
So this is the macro i use to send the column E value in the txt file:
How can i add 7hrs on that code transfer.
Thanks again for your help.
Full code:
I have a macro in excel that generate a txt file for all rows with data.
But i have an issue with 1 single value
In my cells from column E, starting at row 7, i will have a value with this format:
201204081900 = YYYYMMDDHHMM
hrs would
The isssue i have is that i would like to add 7 hours to it. This should also respect the date.
Ex: If date is 20120408 and time at 2300, adding 7 hrs would be 201204090600.
How can i do that?
It just need to send the new date and time in the txt file.
So this is the macro i use to send the column E value in the txt file:
If i = 10 Then sOut = sOut & DELIMITER & Left(.Offset(0, 4).Text & String(74, PAD), 12)
How can i add 7hrs on that code transfer.
Thanks again for your help.
Full code:
Dim objNet As Object
On Error Resume Next
Set objNet = CreateObject("WScript.NetWork")
'vFieldArray contains field lengths, in characters, from field 1 to N
vFieldArray = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
nFileNum = FreeFile
Open "C:\Documents and Settings\All Users\Desktop\ord.02" & Format(Now, "yymmddhhmmss") For Output As #nFileNum
'Open "E:\JDA_TMS_ORDERS\ord.02" & Format(Now, "yymmddhhmmss") For Output As #nFileNum
For Each myRecord In Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
.Select
'sOut = "H" & DELIMITER & "A"
For i = 0 To 20
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
'-- column C : i=2
'HEADER
If i = 1 Then sOut = sOut & DELIMITER & Left("H" & String(1, PAD), 1)
If i = 2 Then sOut = sOut & DELIMITER & Left("A" & String(1, PAD), 1)
If i = 3 Then sOut = sOut & DELIMITER & Left(.Offset(0, 0).Text & String(3, PAD), 12) 'Order ID
If i = 4 Then sOut = sOut & DELIMITER & Left(Range("E2").Text & String(12, PAD), 6) 'SCHEDULE
If i = 5 Then sOut = sOut & DELIMITER & Left("HOST" & String(18, PAD), 6)
If i = 6 Then sOut = sOut & DELIMITER & Left(.Offset(0, 1).Text & String(24, PAD), 12) 'Origin ID
If i = 7 Then sOut = sOut & DELIMITER & Left(.Offset(0, 2).Text & String(36, PAD), 26) 'Destination ID
If i = 8 Then sOut = sOut & DELIMITER & Left(.Offset(0, 4).Text & String(74, PAD), 12) 'Early Delivery
If i = 9 Then sOut = sOut & DELIMITER & Left(.Offset(0, 4).Text & String(74, PAD), 12) 'Late Delivery
If i = 10 Then sOut = sOut & DELIMITER & Left(.Offset(0, 3).Text & String(62, PAD), 12) 'Early available
If i = 10 Then sOut = sOut & DELIMITER & Left(.Offset(0, 4).Text & String(74, PAD), 12) 'Late available
If i = 11 Then sOut = sOut & DELIMITER & Left(.Offset(0, 5).Text & String(110, PAD), 12) 'Order Group
If i = 12 Then sOut = sOut & DELIMITER & Left(.Offset(0, 6).Text & String(122, PAD), 1) 'IB / OB B
If i = 13 Then sOut = sOut & DELIMITER & Left("" & String(122, PAD), 34) 'EMPTY
If i = 14 Then sOut = sOut & DELIMITER & Left("COL" & String(157, PAD), 3)
If i = 15 Then sOut = sOut & DELIMITER & Left("" & String(160, PAD), 13) 'EMPTY
If i = 16 Then sOut = sOut & DELIMITER & Left(.Offset(0, 12).Text & String(173, PAD), 20) 'Passchar1
If i = 17 Then sOut = sOut & DELIMITER & Left("" & String(193, PAD), 38) 'EMPTY
If i = 18 Then sOut = sOut & DELIMITER & Left("M" & String(231, PAD), 1)
If i = 19 Then sOut = sOut & DELIMITER & Left("" & String(232, PAD), 199) 'EMPTY
If i = 20 Then sOut = sOut & DELIMITER & Left(objNet.UserName & String(432, PAD), 75) 'user tag name
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
ASKER
Hello
If i have multiple rows of data, will it add 7 hours to all value from column E that could be different?
Cause normally, they will be different.
That is why i wanted to add 7 hours to the date+time from column E
If i have multiple rows of data, will it add 7 hours to all value from column E that could be different?
Cause normally, they will be different.
That is why i wanted to add 7 hours to the date+time from column E
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the delay
Thanks for your help
It's all good now
Thanks for your help
It's all good now
It creates a 'real' date time from the string and adds 7 hours to that and then it reformats the string as required.
Open in new window
It might actually be worth creating a small function from this code
Open in new window