Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
It'd be helpful if you added the source to your existing macro. –  Isaac Rabinovitch Nov 1 '12 at 5:38
add comment

1 Answer

up vote 0 down vote accepted

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
share|improve this answer
    
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 '12 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 '12 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 '12 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 '12 at 22:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.