Excel Automation and TexttoColumn

Carle_001
Carle_001 Member Posts: 4
edited 2005-04-19 in NAV/Navision Classic Client
Hi all,

Has anyone in the past used the Excel Automation TextToColumn within 'Microsoft Excel 11.0 Object Library'.Range?

I am trying to take a text field which could be more than 1024 Characters and use the TextToColumn Function with Parameters to set it to Fixed Length of 250 Characters and split the field out to a number of fields.

This would allow me to then set-up an array when reading the Excel sheet back into Navision which would read the 250 character text fields and place them into a text/comments table.

The problems I seem to be getting are on formating the TextToColumn statement so any help would be greatly appreciated.

Regards
Carl Edwards

Comments

  • Carle_001
    Carle_001 Member Posts: 4
    The error I seem to be getting is TextToColumns Method of Range Class failed.

    From what I have read this is down to the Parameters being passed.

    ](*,)
  • Carle_001
    Carle_001 Member Posts: 4
    Okay I found a solution and maybe others might be interested in this so I decided to post it. :)

    I passed the VB Constant number so for example
    xlTextQualifierNone = -4142 on the TextToColumn Function.

    I tried this method with PasteSpecial so passed the command
    XlRange.PasteSpecial(-4163,-4142);

    This did a Paste Special Values Only with a Paste Special Operations of None.

    Hope this helps someone as it's only taken me 5 days to track down the problem.

    Carl
  • ARPaul
    ARPaul Member Posts: 10
    I am no programmer and no expert in Print2Excel yet (just obtained it) but wouldn't it be easier to just use this print driver that "prints" to Excel?

Categories