How can I automatically format a column as it is imported?
 

I have a macro which automatically imports the contents of a .csv file. The
data which is imported into Coulmn A is a date but it is not in the format
that we want. When it is imported, it looks like this...

20081223155307

We want it to look like this...

12/23/08

We currently have the column format set as Numeric without decimal places.
Can we set the column to a Date format and then have the macro automatically
truncate and convert the imported data to the format we want?

Here is my macro which does the import...

With
ActiveSheet.QueryTables.Add(Connection:="TEXT;c:\U PS_CSV_EXPORT.csv",Destination:=rDest)
.Name = "UPS_CSV_EXPORT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With


--
Thanks!

How can I automatically format a column as it is imported?
 

Sub way()
Dim c As Range

For Each c In Sheet1.UsedRange.Columns(1) 'adjust range as required
c.Value = Mid(c.Text, 5, 2) & "/" & Mid(c.Text, 7, 2) & "/" & Mid
(c.Text, 3, 2)
Next c
Sheet1.UsedRange.Columns(1).NumberFormat = "mm/dd/yy;@"

End Sub


Cliff Edwards

How can I automatically format a column as it is imported?
 

Cliff,

This loop will format just the imported rows? The sheet is cumulative and
will have rows in it that already have the date in column 1 formatted
properly.



"ward376" wrote in message
...
Sub way()
Dim c As Range

For Each c In Sheet1.UsedRange.Columns(1) 'adjust range as required
c.Value = Mid(c.Text, 5, 2) & "/" & Mid(c.Text, 7, 2) & "/" & Mid
(c.Text, 3, 2)
Next c
Sheet1.UsedRange.Columns(1).NumberFormat = "mm/dd/yy;@"

End Sub


Cliff Edwards

How can I automatically format a column as it is imported?
 

I tried this but it is changing everything in column 1 to // including the
column header in row 1.


"Saucer Man" wrote in message
...
Cliff,

This loop will format just the imported rows? The sheet is cumulative and
will have rows in it that already have the date in column 1 formatted
properly.



"ward376" wrote in message
...
Sub way()
Dim c As Range

For Each c In Sheet1.UsedRange.Columns(1) 'adjust range as required
c.Value = Mid(c.Text, 5, 2) & "/" & Mid(c.Text, 7, 2) & "/" & Mid
(c.Text, 3, 2)
Next c
Sheet1.UsedRange.Columns(1).NumberFormat = "mm/dd/yy;@"

End Sub


Cliff Edwards

How can I automatically format a column as it is imported?
 

You have to define the range... will the data you want to modify
always be in column a? Will there be any interruptions (blanks) in
column a?

http://support.microsoft.com/kb/291304/en-us

Cliff Edwards

How can I automatically format a column as it is imported?
 

Thanks for that link. If I set Column A to be in Date format, when I do the
import from the .csv, the field looks like this...

##########

Knowing this is constant, I modified your script to check the data and if it
finds ##, then replace that data with the current date. This seems to work
nicely. Thanks.

"ward376" wrote in message
...
You have to define the range... will the data you want to modify
always be in column a? Will there be any interruptions (blanks) in
column a?

http://support.microsoft.com/kb/291304/en-us

Cliff Edwards

How can I automatically format a column as it is imported?
 

You can use the named range produced when you add the query table to
define the range to loop within:

Sub way()
Dim c As Range
Dim bgn As Long
Dim nd As Long

For Each c In Sheet1.Range("UPS_CSV_EXPORT").Columns(1) 'adjust range
as required
c.Value = _
Mid(c.Text, 5, 2) & "/" & Mid(c.Text, 7, 2) & "/" & Mid
(c.Text, 3, 2)
Next c

Sheet1.Range("UPS_CSV_EXPORT").Columns(1).NumberFo rmat = "mm/dd/yy;@"

End Sub

Cliff Edwards