Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
call Text to Columns from another App
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").Sele ct '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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
call Text to Columns from another App
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" 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").Sele ct '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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I call up the Text Import Wizard via a macro. | Excel Worksheet Functions | |||
Number and text format in the same call | New Users to Excel | |||
HOW TO VIEW SPECIFIC COLUMNS for A CALL LIST? | New Users to Excel | |||
How to call out object's text w/o selecting | Excel Programming |