G
Guest
Help on formatting date exported to text file!!
I am exporting data to a text file (appending if existing, creating if not).
I have one date field but am exporting everything as text. I want the
date to be in the form mmddyyyy (no slashes and always 8 characters).
I use the following code to pick the data from the sheet and export it.
All works well and the date on the sheet is formatted to look the way I want
it to look. However, in the text file it is formatted with slashes and no
leading zeros.
I found Tom Ogilvy's method to export a single cell in a format but don't
see how to imbed this in my string(s). The date is in Column B.
All suggestions gratefully accepted.
Dim ff As Integer
ff = FreeFile()
Lastrecord = 1
FirstCol = 1
LastCol = 24
Dim FirstFind As Range
Set FirstFind = Range("W1:W100").Find(what:="No Data", _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not FirstFind Is Nothing Then
Lastrecord = FirstFind.Row - 1
Else: GoTo Error
End If
'Now output the data to a text batch file
Sheets("BatchFile").Select
Open "L:\ CheckRequests\BatchFile.txt" For Append As ff
For r = Firstrecord To Lastrecord
For C = FirstCol To LastCol
Print #ff, Cells(r, C) & vbTab;
Next C
Print #ff, 'blank line
Next r
Close ff
--
I am exporting data to a text file (appending if existing, creating if not).
I have one date field but am exporting everything as text. I want the
date to be in the form mmddyyyy (no slashes and always 8 characters).
I use the following code to pick the data from the sheet and export it.
All works well and the date on the sheet is formatted to look the way I want
it to look. However, in the text file it is formatted with slashes and no
leading zeros.
I found Tom Ogilvy's method to export a single cell in a format but don't
see how to imbed this in my string(s). The date is in Column B.
All suggestions gratefully accepted.
Dim ff As Integer
ff = FreeFile()
Lastrecord = 1
FirstCol = 1
LastCol = 24
Dim FirstFind As Range
Set FirstFind = Range("W1:W100").Find(what:="No Data", _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not FirstFind Is Nothing Then
Lastrecord = FirstFind.Row - 1
Else: GoTo Error
End If
'Now output the data to a text batch file
Sheets("BatchFile").Select
Open "L:\ CheckRequests\BatchFile.txt" For Append As ff
For r = Firstrecord To Lastrecord
For C = FirstCol To LastCol
Print #ff, Cells(r, C) & vbTab;
Next C
Print #ff, 'blank line
Next r
Close ff
--