Importing .csv Files into Excel and reformatting Date Columns Using VBA

Question

In a previous question I asked about a method for importing .csv files using Excel using VBA. I received a helpful answer about opening .csv files using VBA, but I worry that using this method will cause issues with date formats, as a commenter on another of my questions mentioned. With that in mind, is there a similar method to the one used by Dan to import files? I understand that recorded macros are often clumsy and so I was wondering how something like the standard code below would be improved.

With ActiveSheet.QueryTables.Add(Connection:= _
    "FAKENAME.csv" _
    , Destination:=Range("$A$1"))
    .CommandType = 0
    .Name = "FAKENAME"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 65001
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 3, 3, 1, 1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1, _
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
    , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

UPDATE:

The issue that occurs with the dates if I choose not to import is like so:

     DateTime               Format

     05/11/2014 3:22        Custom
4/27/2014 9:53:01 AM        General
     11/22/2013 8:29:35 AM  Custom
     05/11/2014 8:26        Custom
1/17/2014 12:28:24 PM       General
     05/11/2014 3:22        Custom

While this can be resolved when I import, if I simply open the file attempting to change the date format manually for the whole column doesn't actually change the format.

Answer 1

I wouldn't likely use this QueryTables method either, as @Dan-Wagner mentioned in your previous post. I would parse the file using something like a Text Stream object, found in the Microsoft Scripting Runtime library. You WILL have to add it as a reference (Tools-->References + find and select it from the list) See below for a little sample of how to use it:

Dim fileSys as New FileSystemObject 'the New keyword is important here!
Dim TS as TextStream
Dim txt as String, sp() as String   'first one is one string, second is an Array
Dim i as Integer

Set TS = fileSys.OpenTextFile("C:\myinputfile.csv")
i = 1
Do While Not TS.AtEndOfStream
    txt = TS.ReadLine               'Read one whole line of data
    sp = Split(txt, ",")            'Split by commas into "Cells" (kinda)
    Sheets(2).Cells(i, 1) = sp(0)     'Note, sp() is "Zero-Based" but ranges are 1 based
    Sheets(2).Cells(i, 2) = sp(1)     'So you'll always access sp onelower than myOutputRange
    '
    '
    '
    i = i + 1
Loop

Note that I assigned your output to the second worksheet, but you could use any range here.

MSDN TextStream Object: http://msdn.microsoft.com/en-us/library/aa242724%28v=vs.60%29.aspx Referencing Libraries: How do I use FileSystemObject in VBA?

Hope this helps!