Importing files into Excel with leading zeros
 

I ran your code and it seemed to work okay. Not having a 78 field file
handy I modified your code for a 4 field file. Make sure that the
TextFileColumnDataTypes property has a "2" for each field you want brought
in as text (so leading zeros are maintained). As it is now only the first 5
(columns A-E) will preserve leading zeros.

When a macro that you record doesn't work as the manual process did, it's
sometimes a problem with regional settings (Excel's VBE is "U.S. Centric",
meaning it assumes you are running under U.S. settings). But I tried
switching to UK settings and still the code run okay, preserving the leading
zeros.

--
Jim Rech
Excel MVP
"Jason L" wrote in message
...
| Hi,
|
| I am an Excel and Excel VBA newbie, so please be patient. :-) I am using
| Excel 2002. I have a macro that imports a delimited txt file into Excel
and
| separates each field according to the ~ (tildas) in the file. There are
| around five fields that contain leading zeros. Despite my attempts to
change
| the code thus far, I can't seem to keep it from removing these zeros as it
| merges the data into Excel. Here is my code:
|
| With ActiveSheet.QueryTables.Add(Connection:="TEXT;O:\F orms\brpt.doc", _
| Destination:=Range("A1"))
| .Name = "brpt"
| .FieldNames = True
| .RowNumbers = False
| .FillAdjacentFormulas = False
| .PreserveFormatting = True
| .RefreshOnFileOpen = False
| .RefreshStyle = xlInsertDeleteCells
| .SavePassword = False
| .SaveData = True
| .AdjustColumnWidth = True
| .RefreshPeriod = 0
| .TextFilePromptOnRefresh = False
| .TextFilePlatform = 437
| .TextFileStartRow = 1
| .TextFileParseType = xlDelimited
| .TextFileTextQualifier = xlTextQualifierDoubleQuote
| .TextFileConsecutiveDelimiter = False
| .TextFileTabDelimiter = False
| .TextFileSemicolonDelimiter = False
| .TextFileCommaDelimiter = False
| .TextFileSpaceDelimiter = False
| .TextFileOtherDelimiter = "~"
| .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1,
1,
| 1, 1, 1, 1, 1, 1, 1, 1, 1, _
| 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
| 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
| , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
| .TextFileTrailingMinusNumbers = True
| .Refresh BackgroundQuery:=False
| End With
|
| There is more code after this, but it's not relevant. When I go through
and
| record my actions (as you see above) in importing the data and changing
the
| format of columns to text, it works fine. However, when I try to run the
| macro, it doesn't work.
|
| Any help would be greatly appreciated.
|
| TIA,
| Jason