Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export Format | Excel Discussion (Misc queries) | |||
Export a spreadsheet and keep format | Excel Discussion (Misc queries) | |||
Format of CSV File in export | Excel Programming | |||
Date format problem on export to CSV | Excel Programming | |||
export cell value - Date Format | Excel Programming |