WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with Excel
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

TextToColumns FieldInfo Embedded Array Parameter Issue

Keywords: embed embedded array parameter OLE Method

Question:

I am trying to recreate the VB code (far below) into Winbatch and get an OLE error: "Uninitialised variable or undefined function" on the array. Looks like the array represents the number of cells expected as the outcome of the Cells to text function. Any ideas?

My Code: 

AllSortCols = "A:A" ; Sheet range
colrange = xlApp.Columns(AllSortCols) ; Define the area to be expanded = all column 1
setrange = colrange.select ; Select the area to be 
selection = xlApp.Selection ;... expanded
xlDelimited = 1
xlDoubleQuote = 1
xlFalse = @False
xlTrue = @True
Rangeobj = xlApp.Columns("A:A")
xlP = ':: Destination=RangeObj, DataType=xlDelimited,' 
xlP = Strcat(xlP, 'TextQualifier=xlDoubleQuote, ConsecutiveDelimiter=xlFalse, Semicolon=xlFalse, Comma=xlFalse, Space=xlFalse, Other=xlTrue, ')
xlP = StrCat(xlP, 'OtherChar="§", ')
xlP = Strcat(xlP, 'FieldInfo=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers=xlTrue')
Selection.TextToColumns(%xlP%)
VB code: 
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="§", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True

Answer:

The simple solution is to remove the Array statement, if all of the columns each had the same data type (1). If the columns had diffent data types then you will then need to pass the fieldinfo parameter.

The fieldInfo parameter is an optional variant. An array containing parse information for the individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the xlColumnDataType constants specifying how the column is parsed.

User Reply:

As you suggest, omitting the parameter, works just fine. However, for the record I would like to understand how one should correctly parse the VB command: 
FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,1), Array(6, 1), Array(7, 1)),
into Winbatch. Please could you parse the line above into it's correct Winbatch format.

Answer:

Unfortunately WinBatch doesn't directly support embedded arrays (an array inside of an array). However, the developers came up with the following code which should allow you create the Fieldinfo embedded array: 

Array = ArrDimension(5)
Temp  = ArrDimension(2)
Type  = 1
for i = 0 to 4
 
    ; Create a binary buffer that wb will convert to a safe array.
   Col = i + 1
   Temp[0] = ObjectType("I1", Col)
   Temp[1] =  ObjectType("I1", Type)
   Buf = BinaryAllocArray(temp)
   BinaryOleType(Buf, 103, 0, 0, 0)
   
   ; WB automagically converts the WIL array 'array' to a safearray when passed to a method.
   Array[i] = Buf
next
 
Here is the full test script that was used to test it... 
; Put the following text in the clipboard ( minus the semicolon) and then run the script
; 5 4 3 4 1  

xlApp = ObjectCreate("excel.application")
xlApp.visible = @True
xlApp.WorkBooks.add
xlApp.Worksheets("Sheet1").Activate

xlApp.ActiveSheet.Paste
xlApp.ActiveSheet.Range("A1").Select()

Message("Before Conversion","")

xlDelimited = 1
xlDoubleQuote = 1
xlFalse = @False
xlTrue = @True

xlP = ':: DataType=xlDelimited,'
xlP = Strcat(xlP, 'Space=xlTrue, Other=xlFalse, ')	

;****** CODE TO TRICK WINBATCH INTO CREATING AN EMBEDDED ARRAY *******
; Make an array of arrays.
Array = ArrDimension(5)
Temp  = ArrDimension(2)
Type  = 1
for i = 0 to 4
	Col = i + 1
	Temp[0] = ObjectType("I1",	Col)
	Temp[1] =  ObjectType("I1", Type)
	Buf = BinaryAllocArray(temp)
	BinaryOleType(Buf, 103, 0, 0, 0)
	Array[i] = Buf
next

xlP = Strcat(xlP, 'FieldInfo=Array')
xlApp.Selection.TextToColumns(%xlP%)

message("Did it work?", "")

; Clean up
RangeObj = 0
Selection = 0
objwb = 0
workbooks = 0
xlApp.quit
xlApp = 0
for i = 0 to 4
	BinaryFree( Array[i])
next

exit
Article ID:   W17164

File Created: 2007:07:03:14:28:34
Last Updated: 2007:07:03:14:28:34