Excel VBA Code Conversion

Question

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 information 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

Can you describe what the code does in general? There is not a simple one-to-one mapping from this code to something in C#. You will be better off starting by writing an algorithm (pseudo-code) of what this code does, and writing that in C# step by step. — mellamokb
– mellamokb, Commented Oct 7, 2011 at 15:39
I'm not entirely sure, which is largely the problem. I don't know what the purpose of either the FieldInfo or Origin properties, so it's difficult to translate. — Joshua H.
– Joshua H., Commented Oct 7, 2011 at 15:43
When you run the code in production what happens as you see it? Or is it too complicated to tell outside of the code itself? I would think it would do something obvious that could be described in words without needing the code, and that description would be a lot easier to translate into another language. — mellamokb
– mellamokb, Commented Oct 7, 2011 at 15:45
It seems that it is simply an array of column information- in this case, it was the data type of each column. Looking at what the code was supposed to do, rather than trying to translate directly is what I needed to do. Thanks for the help. — Joshua H.
– Joshua H., Commented Oct 7, 2011 at 16:03
Here's a thread about FieldInfo: excelforum.com/excel-general/… — Bobort
– Bobort, Commented Oct 7, 2011 at 16:04

Community · Accepted Answer · 2017-05-23 12:01:16Z

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))

Excel text import dialog box screenshot

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.

Collectives™ on Stack Overflow

Excel VBA Code Conversion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related