Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IMPORTING PC-DOS PLAIN TEXT FILES WITH LEADING SPACES | Excel Discussion (Misc queries) | |||
importing xml files adds extra zeros | Setting up and Configuration of Excel | |||
Importing to Excel from csv AND keep leading zeros | Excel Discussion (Misc queries) | |||
Leading Zeros in .CSV files | Excel Discussion (Misc queries) | |||
retain leading zeros when importing | Excel Discussion (Misc queries) |