Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help!!! TexttoColumns

Status
Not open for further replies.
ACTHelp

ACTHelp

MIS
Feb 8, 2005
30
US
I'm trying to execute a Texttocolumns command from a vb module in Access against an Excel spreadsheet.

Here's my code:

Sub parsetext()
'
' parsetext Macro
'
Dim strexceltab As String
strexceltab = "excel"
objXLApp.Worksheets(strexceltab).Range("b1").Select
objXLApp.Worksheets(strexceltab).Range("b1").Activate

objXLApp.Selection.EntireColumn.Insert
objXLApp.Selection.EntireColumn.Insert
objXLApp.Selection.EntireColumn.Insert
objXLApp.Selection.EntireColumn.Insert
' Application.CutCopyMode = False
objXLApp.Worksheets(strexceltab).Range("a2:a380").Select
objXLApp.Worksheets(strexceltab).Range("a2:a380").Activate


objXLApp.Selection.TextToColumns.Destination(Range("b1")) _
.DataType(xlDelimited) _
.TextQualifier(xlNone) _
.ConsecutiveDelimiter(False) _
.Tab(False) _
.Semicolon(False) _
.Comma(False) _
.Space(False) _
.Other(True) _
.OtherChar(".") _
.FieldInfo (Array(Array(1, 1), Array(2, 1), Array(3, 1)))

End Sub

The insert of the three columns works fine and the selection of the area prior to the texttocolumns command works fine as well.

The data in the first column contains data such as:

Atlanta.Anderson, Mark - 5501.C8000 INSTRUMENT
Atlanta.Anderson, Mark - 5501.All Instruments
Atlanta.Atlanta.C8000 INSTRUMENT


It is delimited by a period.

I am getting this error:
Error #1004:Application-defined or object_defined error



 
Sort by date Sort by votes
Hi
Do you have a reference to the Excel library?
 
Upvote 0 Downvote
Probably not since I'm not sure. Since all the other Excel stuff is working...thought I was ok.
 
Upvote 0 Downvote
And this ?
objXLApp.Selection.TextToColumns _
Destination:=objXLApp.Range("b1") _
DataType:=xlDelimited _
TextQualifier:=xlNone _
ConsecutiveDelimiter:=False _
Tab:=False _
Semicolon:=False _
Comma:=False _
Space:=False _
Other:=True _
OtherChar:="." _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
get a compile error:
objXLApp.Selection.TextToColumns _
Destination:=objXLApp.Range("b1") _
DataType:=xlDelimited _
TextQualifier:=xlNone _
ConsecutiveDelimiter:=False _
Tab:=False _
Semicolon:=False _
Comma:=False _
Space:=False _
Other:=True _
OtherChar:="." _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))


expected end of statement with DataType highlighted
 
Upvote 0 Downvote
Sorry for the typo:
objXLApp.Selection.TextToColumns _
Destination:=objXLApp.Range("b1"), _
DataType:=xlDelimited, _
TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:=".", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Yes...I had already added the commas and it worked! Thanks so much for your help and quick responses!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
420
combo
combo
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
363
Andrzejek
Andrzejek
arvarr
  • Locked
  • Question
Excel Help - Worksheets by UserID 3
2
Replies
39
Views
656
arvarr
arvarr
simc22
  • Locked
  • Question
Help with VBA find loop?
Replies
5
Views
163
strongm
strongm
Shiv_MIS
  • Locked
  • Question
NEED HELP IN AUTOMATING MS FORM DATA COLLECTION TO TRIGGER EMAIL BASED ON CONDITIONAL FORMATTING
Replies
0
Views
85
Shiv_MIS
Shiv_MIS

Part and Inventory Search

Sponsor

Back
Top