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
| 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.
|
| | > 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
| > | > | Sorry Jim,
| > |
| > | It is 17DEC in its original form, not 17-Dec
| > |
| > | Thanks,
| > |
| > | Steve Wilson.
| > |
| > | | > | > >>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
| > | > | > | > | Hi Jim,
| > | > |
| > | > | I tried your suggestion but 17-Dec gets converted to 38338
| > | > | Do you have any other ideas.
| > | > |
| > | > | Thanks,
| > | > |
| > | > | Steve Wilson.
| > | > |
| > | > | | > | > | > 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
| > | > | > | > | > | > | 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.
| > | > | > |
| > | > | > |
| > | > | >
| > | > | >
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|
