How to force Excel to treat dates in CSV as text in a macro

Question

I have a macro that lets the user select a CSV file and then manipulates it. However, it treats cells that contain things like 1/2/12 as a date. I need to keep these strings as text, not dates.

I know I can do that by 1) start a new worksheet 2) import the CSV instead of opening it. Set the column with the dates to "text" and finish.

The question is: how can I interrupt Data > Import so that the user can select the file. After that, the macro should continue to set the format of the columns, finish the import and perform the data manipulations.

Edit: Here is the relevant code of the macro as it stands:

ChDir "C:\RoomTimerData\"

MyFile = Application.GetOpenFilename("Comma Separated Values (.csv),.csv")

Workbooks.Open fileName:=MyFile

Solution

Usually one would select the data-type for each imported column as step 3 of 3 of the text-import dialog. To avoid misformatting, just use text as format, and you're good.

However, you seem to use a VBA macro to import your CSV files. So I just recorded such an uncorrupted text import:

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\test.csv", Destination:=Range("$A$1"))
    .Name = "test_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 1) 'THIS IS THE MAGIC LINE!
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Also, look at this example from excel-help for TextFileColumnDataTypes:

Set shFirstQtr = Workbooks(1).Worksheets(1) 
Set qtQtrResults = shFirstQtr.QueryTables _
        .Add(Connection := "TEXT;C:\My Documents\19980331.txt", _
        Destination := shFirstQtr.Cells(1, 1))
With qtQtrResults
    .TextFileParseType = xlFixedWidth
    .TextFileFixedColumnWidths = Array(5, 4)
    .TextFileColumnDataTypes = _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
    .Refresh
End With

These are the formats you can use:

• xlGeneralFormat
• xlTextFormat
• xlSkipColumn
• xlDMYFormat
• xlDYMFormat
• xlEMDFormat
• xlMDYFormat
• xlMYDFormat
• xlYDMFormat
• xlYMDFormat

Comments

[Isaac Rabinovitch]

It'd be helpful if you added the source to your existing macro.

[Jook]

or you could just use text as the default input for everey col, and figure out their formats afterwarts - or place a ' before every day beforehand with some parsing over the file. But when you want your data exactly as they came in, then using text format is the way to go.

[hdhondt]

The problem is that Excel overrides the cell formatting when it imports the CSV - unless I tell it during the import that it is text. Anyway, the real problem is that I need to "stop" the macro so the user can select the file. After that the macro should continue, by first selecting text for the date column and then finishing the import.

[hdhondt]

Just realised I can use GetOpenFilename to prompt the user for the file and then plug the variable in, by using Connection:="TEXT;" & Myfile, . Thanks for the help.