Export Date Format
 

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
--

Export Date Format
 

Hi Mr. C,

It is just a small change to your code:

For r = Firstrecord To Lastrecord
For C = FirstCol To LastCol
If C = 2 Then
Print #ff, Format( Cells(r, C), "mmddyyyy") & vbTab;
Else
Print #ff, Cells(r, C) & vbTab;
End If
Next C
Print #ff, 'blank line
Next r

Hoop This Helps


Executor

Export Date Format
 

Executor,

Super! Exactly what I was looking for. I hope everyone is as appreciative
of these forums as I am.
Thanks very much for your answer (both timely and useful).


"Executor" wrote:

Hi Mr. C,

It is just a small change to your code:

For r = Firstrecord To Lastrecord
For C = FirstCol To LastCol
If C = 2 Then
Print #ff, Format( Cells(r, C), "mmddyyyy") & vbTab;
Else
Print #ff, Cells(r, C) & vbTab;
End If
Next C
Print #ff, 'blank line
Next r

Hoop This Helps


Executor