Import text file from VBA code; import all numbers as text

[maacmaac]

As part of a macro, I have code that prompts user for a text file and then opens. The issue I am is having is the macro automatically converts some of the numbers into scientific notation. For example the number in my text file is 86765E202. When the file opens via use of the macro, the number is coverted to 8.6765E+206. Is there a way I can modify the code or use some other method that will display the number as 86765E202? If I need to, I could open all columns as text but wasn't sure how to incorporate into the code.

Please Login or Register  to view this content.

[Leith Ross]

Hello maacmaac,

The default type setting for all columns is General. You can specify the type for column. Will the data always be in the same column or columns?

[watersev]

hi maacmaac, you need to check VB help file for Open Text method:

OpenText Method

Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.

expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

expression Required. An expression that returns one of the objects in the Applies To list.

FileName Required String. Specifies the file name of the text file to be opened and parsed.

Origin Optional Variant. Specifies the origin of the text file. Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. Additionally, this could be an integer representing the code page number of the desired code page. For example, "1256" would specify that the encoding of the source text file is Arabic (Windows). If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.

StartRow Optional Variant. The row number at which to start parsing text. The default value is 1.

DataType Optional Variant. Specifies the column format of the data in the file. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file.

TextQualifier Optional XlTextQualifier. Specifies the text qualifier.

XlTextQualifier can be one of these XlTextQualifier constants.
xlTextQualifierDoubleQuote default
xlTextQualifierNone
xlTextQualifierSingleQuote

ConsecutiveDelimiter Optional Variant. True to have consecutive delimiters considered one delimiter. The default is False.

Tab Optional Variant. True to have the tab character be the delimiter (DataType must be xlDelimited). The default value is False.

Semicolon Optional Variant. True to have the semicolon character be the delimiter (DataType must be xlDelimited). The default value is False.

Comma Optional Variant. True to have the comma character be the delimiter (DataType must be xlDelimited). The default value is False.

Space Optional Variant. True to have the space character be the delimiter (DataType must be xlDelimited). The default value is False.

Other Optional Variant. True to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value is False.

OtherChar Optional Variant (required if Other is True). Specifies the delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored.

FieldInfo Optional xlColumnDataType. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

XlColumnDataType can be one of these XlColumnDataType constants.
xlGeneralFormat General
xlTextFormat Text
xlMDYFormat MDY date
xlDMYFormat DMY date
xlYMDFormat YMD date
xlMYDFormat MYD date
xlDYMFormat DYM date
xlYDMFormat YDM date
xlEMDFormat EMD date
xlSkipColumn Skip Column

Example for one-column text file:

Please Login or Register  to view this content.

[maacmaac]

The data is always the same number of columns and always in the same column number.

[Leith Ross]

Hello maacmaac,

You can either set the columns to Text manually and save the workbook with changes or make an array to specify which columns in in the text file are to be treated as text, per watersev's post.

[maacmaac]

I only need to format column 13 (column "M"). I tried using following, but it is still formatting all cells in column M as "general". Can I choose to format one column or do I need to format all? Not sure what I am doing wrong in the code. (Where "(13, 2)" the 2 = xlFormatText)

Please Login or Register  to view this content.

[watersev]

you need to format all columns, example:

Please Login or Register  to view this content.

[maacmaac]

Ok. I have it working now. Thanks