Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text fields using the import wizard (Excel 2003)

    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

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Text fields using the import wizard (Excel 2003)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text fields using the import wizard (Excel 2003)

    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.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text fields using the import wizard (Excel 2003)

    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

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text fields using the import wizard (Excel 2003)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text fields using the import wizard (Excel 2003)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text fields using the import wizard (Excel 2003)

    Ok, many thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •