call Text to Columns from another App

[BillyRogers]

I'm opening excel from Outlook and copying a recordset in that has 80
semi-colon separated values into cell a1. I want to use text to columns
after doing this. I select the column in the code which works fine, but the
code i recorded in excel to perform the text to columns fails when I call it
from outlook.

xlApp.Workbooks(1).Worksheets(1).Range("A:A").Select 'selection works
fine

' code fails here

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18,
1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1),
Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31,
1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1),
Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44,
1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1),
Array(51, 1), Array(52, 1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57,
1), Array(58, 1), Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1),
Array(64, 1), Array(65, 1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70,
1), Array(71, 1), Array( _
72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1),
Array(77, 1), Array(78, 1), _
Array(79, 1), Array(80, 1)), TrailingMinusNumbers:=True





xlApp.Visible = True

--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003

 

[Jim Cone]

Billy,
Your code is in Power Point and PP will not know what the Excel constants mean.
The xl contstants were replaced with their numeric value.

When automating Excel, set references to all Excel objects.
Use the appropriate reference every time you make reference to a spreadsheet.
Avoid the use of Selection, ActiveSheet, ActiveWorkbook etc.
Note that "selection" is not used and Range("A1") is qualified with the Worksheet reference.
(two places)...

Dim xlApp As Excel.Application
Dim appWbook As Excel.Workbook
Dim appWsheet As Excel.Worksheet

Set xlApp = New Excel.Application ' or Create Object
Set appWbook = xlApp.Workbooks.Add ' or open
Set appWsheet = appWbook.Worksheets(1)

appWsheet.Range("A:A").TextToColumns Destination:=appWsheet.Range("A1"), _
DataType:=1, TextQualifier:=1, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
-snip-
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"BillyRogers" <[email protected]>
wrote in message
I'm opening excel from Outlook and copying a recordset in that has 80
semi-colon separated values into cell a1. I want to use text to columns
after doing this. I select the column in the code which works fine, but the
code i recorded in excel to perform the text to columns fails when I call it
from outlook.

xlApp.Workbooks(1).Worksheets(1).Range("A:A").Select 'selection works
fine

' code fails here

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18,
1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1),
Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31,
1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1),
Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44,
1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1),
Array(51, 1), Array(52, 1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57,
1), Array(58, 1), Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1),
Array(64, 1), Array(65, 1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70,
1), Array(71, 1), Array( _
72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1),
Array(77, 1), Array(78, 1), _
Array(79, 1), Array(80, 1)), TrailingMinusNumbers:=True





xlApp.Visible = True

--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003