Excel VBA Code Conversion

The FieldInfo in the following code is giving me some problems. I've tried online converters, but they don't seem to do very well with this portion of the code. I've also tried to look through MSDN for informa开发者_运维问答tion on it, but I couldn't find any. Where would I be able to find information on how to convert that part, i.e. what the Array(x,y) translates to in C#?

Any help is appreciated.

Below is an example of the code that I'm struggling with.

    Workbooks.OpenText Filename:= _
    "" & myZdrive & "\CI_System\Source_Files\" & myPosFile & "", Origin:= _
    xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
    Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 1), _
    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 2), Array(15 _
    , 1), Array(16, 1), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2))

    Rows("1:1").Select
    Selection.Font.Bold = True
    Cells.Select
    Cells.EntireColumn.AutoFit

The VBA code you posted looks like a macro recording done within Excel. For example, I recorded the following by opening a plain text file (.txt):

Workbooks.OpenText Filename:= _
    "C:\test.txt" _
    , Origin:=xlWindows, startRow:=1, DataType:=xlDelimited, TextQualifier _
    :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
    False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)

This was recorded with the "Delimited" option checked on the first dialog box. If I instead select "Fixed Width", and then manually create a number of columns in the input data, the recording looks like this:

Workbooks.OpenText Filename:= _
    "C:\test.txt" _
    , Origin:=xlWindows, startRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
    Array(Array(0, 1), Array(2, 1), Array(10, 1), Array(18, 1), Array(30, 1), _
    Array(41, 1), Array(55, 1))

If I had to guess, I would say that someone recorded the macro using the "Fixed Width" option, and then later changed the VBA code from DataType:=xlFixedWidth to DataType:=xlDelimited.

If your input data is actually delimited, you should be able to omit all of the messy FieldInfo array info and let Excel handle the delimiters automatically just by setting DataType:=xlDelimited

If you do need to specify fixed-width columns, the link that Bobort posted would be a great place to start. By the look of it, FieldInfo requires a VBA Variant array contaning any number of sub-arrays, each containing two numbers: a column width and a data type.

Edit:

The Origin field is used to describe the format of the text file: Macintosh, Windows (ANSI), or MS-DOS (PC-8) as selected from the drop-down menu.