Using Excel's TextToColumn in Access

[automaticbaby]

I've recorded a macro in Excel and even adapted code from an old thread (1033329) but neither has removed the unwanted data.

Here's what I've got:

With oApp
    .Columns.("J:J").Select
    .Selection.TextToColumns _
        Destination:=.Range("J2"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=True, _
        OtherChar:="(", _
        FieldInfo:=Array(Array(1, 1), Array(2, 9))
End With

The column in question holds info similar to this: 50 (E: 50, I: 0).

This shouldn't make a difference, but the file is a CSV file and the top line is a header.

Any suggestions?

Thanks

 

[SkipVought]

Hi,

You have not told us what outcome that you want.

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[PHV]

I guess your destination range is invalid.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[automaticbaby]

Yeah, sorry about that, Skip. I need everything to the right of the ( gone. I only want the number.

PH, I'm not sure about the destination, but it works in Excel. I would think it would carry over, but what do I know, since I can't make this work.

 

[SkipVought]

Some things just don't translate from one application into another.

Parse it in Excel and then import into Access.

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[SkipVought]

... or

just do an update query and strip the field in a QUERY.

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[rmikesmith]

When I tried your code, I got a "Destination reference not valid" error, as PHV suggested. Occurs whether the code is run from within Excel or another Office application (Word, in my case). This is "cured" only by changing Destination to J1 or another column altogether. If I change it to J1 or eliminate it completely, it works just as expected.


Regards,
Mike

 

[automaticbaby]

Thanks for all your help guys. I figured out my problem as I was questioning if I was missing something. Apparently I needed to reference the Excel Object Library.

To quote George Costanza "I am Costanza, King of the Idiots!"

Thanks again for the suggestions.

Albert