Text fields using the import wizard (Excel 2003)

Topic

New Reply

Hi,

I am importing a range of files using the import wizard with a special seperator in a macro, this works fine but I have one issue, I need to set all the fields to text, well at least the date fields as I want to manage the format, I cannot find a way of controlling this in the macro.

If I do it manually I can change the column in the wizard, but not in the WorkBooks.opentext command.

If I try to change the format after the data has loaded it does not format back to the original input data.

Any idea.

Thanks

Mike

Reply | Quote

Replies

See the below from Excel VBA:

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

You can use xlEMDFormat only if you have installed and selected Taiwanese language support. The xlEMDFormat constant specifies that Taiwanese era dates are being used.

The column specifiers can be in any order. If there’s no column specifier for a particular column in the input data, the column is parsed with the General setting.

Notes

If you specify that a column is to be skipped, you must explicitly state the type for all remaining columns or the data will not parse correctly.
If there is a recognizible date in the data, the cell will be formatted as a date in the worksheet even if the setting for the column is General. Additionally, if you specify one of the above date formats for a column and the data does not contain a recognized date, then the cell format in the worksheet will be General.
This example causes the third column to be parsed as MDY (for example, 01/10/1970), the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.

Array(Array(3, 3), Array(1, 2))

If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two-element array specifies the parse option for the column as a number between 0 and 9, as listed in the preceding table.

Good Luck.

Tom Duthie

Reply | Quote

Thanks Tom,

I have problem with this, I do not want to have the array option defined as my macro opens multiply files and they have different column lengths, so I just want to have all fields set to text, is there anyway I can force all columns to text.

Regards

Mike

Reply | Quote

Tom has already pointed out the constants you can use – you want xlTextFormat to force Excel to treat a column as text.

You can use Tools | Macro | Record New macro to get an idea of what the code is going to look like.

Reply | Quote

Thanks Hans,

I have problem with this, I do not want to have the array option defined as my macro opens multiply files and they have different column lengths, so I just want to have all fields set to text, is there anyway I can force all columns to text.

I did use the record option to look for this without any additional information showing.

Regards

Mike

Reply | Quote

No. If you don’t specify the column type, it will be imported as General, there is no way to specify Text as default.

One option would be to read the text file line by line, parse each line into its parts (fields) and enter the data into a blank new sheet.

Reply | Quote

Ok, many thanks.

Reply | Quote