Maybe I'm just not getting it...
Could someone please help me declare the Array needed in the following.
xlApp.Selection.TextToColumns Destination:=.Range("A1"), _ DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(25,2)) |
Thanks,
Ken
Dim or, in your example: FieldInfo:=New Integer()() {New Integer() {0, 2}, New Integer() {25, 0}} Hope this helps.
Lance
Thank you for the help..
I've tried both options and your first approach is more what I had in mind. However, I believe that this is going to get more complicated than it would have if I were using VB6. The error I get now is:
'An unhandled exception of type '"System.Runtime.InteropServices.SafeArrayTypeMismatchException" 'Additional information: Specified array was not of the expected type. |
I changed the code to read:
Dim MyArray()() As Object = {New Object() {0, 2}, New Object() {25, 0}} xlApp.Selection.TextToColumns(Destination:=.Range("A1"), _ It would have been nice if someone out there knew how to help me talk to Office from .Net. I've been reading a lot of controversy on the subject and I guess I'm just a victim of the eventual drop of VBA. Anyway, Thanks for responding. Ken
DataType:=xlFixedWidth, FieldInfo:=MyArray)
The reason for the change in type is because Excel wants a variant. At this point I think I'm just going to write the needed code to add the columns and change the data type of the columns to text then use left and right commands to move the data.
Hi
I realise that it has been a while since this post but I have been struggling with the exact same issue and
I just got it to work by specifying FieldInfo as an Excel range, which I have been told
loads up into the required array for FieldInfo.
So in this case type following into the respective cells: 0 into A1, 2 into B1, 25 into A2, 0 into B2.
Then for Field info specify xlApp.Range("A1:B2") - where xlApp is Microsoft.Office.Interop.Excel.Application of course.
I am going to try loading this from a ListBox or similar
Murray
To continue from my last point, what I dis was load the relevant values into a ListView control
on awindows form, then at runtime my program loads these values into a spreadsheet and uses the range
holding them to perform the TextToColumns method
Hi,
Old topics simply never die.
Here's my solution, after days of trying, for declaring an 'Array' parameter for Excel or any Office Application wiyhin VB 2005/VB.NET
Description:
First, create an Excel object, and instantiate it
Show Excel (helps debugging)
Create a 2 dim array composed of integers. The example is equal to Excel OpenText method ondelimited width text file with :
FieldInfo:=Array(Array(0,4),Array(7,2),Array(18,9),Array(32,1))
Code below=========================
Dim xlapp As Microsoft.Office.Interop.Excel.Applicationxlapp =
New Microsoft.Office.Interop.Excel.Applicationxlapp.Visible =
TrueDim ParamaterArrayXLFI(,) As Integer = {{0, 4}, {7, 2}, {18, 9}, {32, 1}}xlapp.Workbooks.OpenText(Filename:=
"C:\TestFile.txt", Origin:=2, StartRow:=1, DataType:=2, FieldInfo:=ParamaterArrayXLFI, TrailingMinusNumbers:=True)========================================
Hope this helps!
Sylvain
