Well, I was mistaken. I thought I knew something but I must have just
assumed it. Specifically, even if you use the Opentext method, with a CSV
file Excel still automatically formats as a date even if you specify another
format. How annoying! My apologies.

Here's another approach - Unlike opening the text file in a new workbook
this opens it directly into the active sheet. If you want a new workbook
you'd have to add WorkBooks.Add to the macro before the part that opens the
file. This code is specific to the little file I've been using - one with
only 3 items: 123,abc,17Dec. You'd have to create a CSV file with this in
it before running this macro.

What do you think?

Sub Macro1()
Dim FName As Variant
FName = Application.GetOpenFilename("CSV Files (*.CSV),*.CSV")
If FName < False Then
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FName & "",
Destination _
:=Range("A1"))
.Name = ""
.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, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
End Sub


--
Jim Rech
Excel MVP
"Always Learning" <NoMoreSpam@MyEmail wrote in message
...
| Hi Jim,
|
| I know that if I name a file with a .Txt extension it will allow me to
pick
| the column to come in as text when using the import wizar but I am not
doing
| things this way.
| A user is opening an Excel file with VBA coding. It shows them a dialogue
| box for them to pick a .csv file to work on. That file is then opened and
| the VBA code then adds borders, page breaks and the like to create a nice
| printed report.
| The user does not use the import wizard and I do not want them to. All I
| want is for me to be able to select column C and make it's format to be
text
| and the 17DEC to stay as that. When I format the column it turns 17DEC
into
| 38338.
|
| If you open a worksheet and put 17DEC into a cell and tab off the cell it
| automatically gets changed to 17-Dec. Now format the cell to be text and
it
| automatically gets changed to 38338. Very annoying.
|
| Thanks for all your help Jim, you have been very patient.
|
| I would appreciate any other ideas.
|
| Best Regards,
|
| Steve Wilson.
|
| "Jim Rech" wrote in message
| ...
| Same good result<g. By selecting Text on the third screen of the
wizard
| for the third data field in my example it tells Excel to bring the item
in
| "as is". If you're sure you're doing this right I don't know what to
tell
| you.
|
| --
| Jim Rech
| Excel MVP
| "Always Learning" <NoMoreSpam@MyEmail wrote in message
| ...
| | Sorry Jim,
| |
| | It is 17DEC in its original form, not 17-Dec
| |
| | Thanks,
| |
| | Steve Wilson.
| |
| | "Jim Rech" wrote in message
| | ...
| | I tried your suggestion but 17-Dec gets converted to 38338
| |
| | Are you sure you did this right? It works fine for me.
| |
| | This is what my text file had in it:
| |
| | 123,abc,17-Dec
| |
| | and the last item came into Excel as text: 17-Dec
| |
| | --
| | Jim Rech
| | Excel MVP
| | "Always Learning" <NoMoreSpam@MyEmail wrote in message
| | ...
| | | Hi Jim,
| | |
| | | I tried your suggestion but 17-Dec gets converted to 38338
| | | Do you have any other ideas.
| | |
| | | Thanks,
| | |
| | | Steve Wilson.
| | |
| | | "Jim Rech" wrote in message
| | | ...
| | | The OpenText method allows you to specify the data type of each
| field.
| | | You
| | | would have to specify that this field is text.
| | |
| | | To see how this is done first temporarily change the file's
| extension
| | to
| | | TXT
| | | (just so long as it is not CSV). Then record a macro as you
open
| the
| | file
| | | in Excel. The File Import Wizard will appear and you use the
| third
| | screen
| | | of the wizard to change the field type of the "date" field to
Text
| | from
| | | General.
| | |
| | | The resulting macro will demonstrate using the FieldInfo
parameter
| to
| | | control data type.
| | |
| | |
| | |
| | | --
| | | Jim Rech
| | | Excel MVP
| | | "Always Learning" <NoMoreSpam@MyEmail wrote in message
| | | ...
| | | | Hi There,
| | | |
| | | | I have a procedure that imports a .csv file.
| | | | All was going well until some of the cell values being
imported
| were
| | | 17DEC,
| | | | 28DEC and that made Excel think they are dates and puts into
the
| | cell
| | | 17-Dec
| | | | & 28-Dec, which is wrong, these are actually stock codes.
| | | | How do I make sure that when I am processing the data that it
| stays
| | | exactly
| | | | as it is in the .Csv file.
| | | | Do I have to format the column to be Text before hand? When I
| tried
| | that
| | | it
| | | | changed 17DEC into something like 38334
| | | |
| | | | Any help or advice would be appreciated.
| | | |
| | | | Best Regards,
| | | |
| | | | Steve Wilson.
| | | |
| | | |
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|