1

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

1
  • It'd be helpful if you added the source to your existing macro. Nov 1, 2012 at 5:38

1 Answer 1

0

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
4
  • The problem is that the user needs to be able to select the file. The name is different (and not predictable) every time the macro is run. The only other way I have thought of is to have the macro shell some utility that puts a <space> before the date. That stops Excel from interpreting it as a date.
    – hdhondt
    Nov 2, 2012 at 9:09
  • 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.
    – Jook
    Nov 2, 2012 at 11:18
  • 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
    Nov 4, 2012 at 10:41
  • 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.
    – hdhondt
    Nov 4, 2012 at 22:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .