Array function from VBA



VBA, as used in Excel 2000 and Excel 2003, has a function called Array.
It's commonly seen in statements like the following:

Workbooks.OpenText Filename:=ACFileName, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), _
Array(9, 1), Array(18, 1), Array(33, 1), Array(49, 1), Array(71, 1), _
Array(75, 1), Array(89, 1), Array(113, 1), Array(117, 1), Array(135, 1))

The FieldInfo parameter is an array of arrays, and the inner arrays have
two items. (The values are column numbers and data types; for data type, 1
might mean text, 2 might mean date, etc. Don't hold me to that.)

I can't find how to convert this statement into VB.NET 2005. I have the
ExcelApp object of type Excel.Application, and so far I have this:

ExcelApp.Workbooks.OpenText(Filename:=ACFileName, _
StartRow:=1, DataType:=Excel.XlTextParsingType.xlFixedWidth, _
FieldInfo:=

Notice that I found how to fully qualify the xlFixedWidth constant!

Here's the doc for VBA's Array function:

Returns a Variant containing an array.

Syntax

Array(arglist)

The required arglist argument is a comma-delimited list of values that are
assigned to the elements of the array contained within the Variant. If no
arguments are specified, an array of zero length is created.

Example:

Dim A As Variant
A = Array(10,20,30)
B = A(2)

.....

How do I build the parameter list for the OpenText method?

Thanks a bunch for any help.

David Walker
.



Relevant Pages

  • Re: I believe Ive found an Access bug...can anyone confirm?
    ... Set objXL = CreateObject ... and use the Excel function from Access. ... Dim IRRValues() As Variant ' Set up array. ...
    (microsoft.public.access.modulesdaovba)
  • Re: transferring matrix to range
    ... You must use a VARIANT to a block of data from an excel range. ... There WERE limitations to the size of the array. ... Dim vData as Variant ...
    (microsoft.public.excel.programming)
  • Application.Transpose in VBA EXCEL 2003
    ... I have been using Application.Transpose function since Excel 97 first ... When execution comes to the point when you use that variant Array ...
    (microsoft.public.excel.programming)
  • Application.Transpose in VBA EXCEL 2003
    ... I have been using Application.Transpose function since Excel 97 first ... It allows to transfer a range of cells values into Variant ... When execution comes to the point when you use that Variant array ...
    (comp.apps.spreadsheets)
  • Re: Evaluate v. WorksheetFunction: best practice?
    ... The argument passed to Evaluate must resolve to a string, ... representation of the VBA variable value or array (use Excel constant ... arguments are VBA arrays or Excel ranges. ...
    (microsoft.public.excel.programming)