Excel VBA to VB.NET conversion

[KreativeKai]

I have the following code in Excel VBA that I'm trying to convert to VB.NET:

Workbooks.OpenText(Filename:= _
"C:\temp.txt", Origin:=437, StartRow:= _
1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, TAB:=False, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 1)), TrailingMinusNumbers:=True)

I've converted everything except the array portion (arrColumnDataType shown below with ???). I'm having a problem coming up with the proper array in VB.NET to put in this code:

Dim xlApp As New Excel.Application
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlApp.Application.Visible = True
Dim strReportName As String = "C:\temp.txt"

??? How do I define arrColumnDataType ???

xlApp.Workbooks.OpenText(strReportName, 437, 1, _
Excel.XlTextParsingType.xlDelimited, _
Excel.XlTextQualifier.xlTextQualifierDoubleQuote, False, False, False, _
True, False, False, False, arrColumnDataType, , , , , True)

I know that the array is defined as (column number, format) where format is:
Excel.XlColumnDataType.xlGeneralFormat = 1
Excel.XlColumnDataType.xlTextFormat = 2
Excel.XlColumnDataType.xlMDYFormat = 3
Excel.XlColumnDataType.xlDMYFormat = 4
Excel.XlColumnDataType.xlYMDFormat = 5
Excel.XlColumnDataType.xlMYDFormat = 6
Excel.XlColumnDataType.xlDYMFormat = 7
Excel.XlColumnDataType.xlYDMFormat = 8
Excel.XlColumnDataType.xlSkipColumn = 9

I just can't seem to get a proper array setup that the compiler likes. Does anyone have a suggestion? Has anyone coded this type of project with success. I'm using VS 2005, but I just upgraded about two weeks ago. Is there an easier way in 2.0? Thanks

Lost in the Vast Sea of .NET

 

[mansii]

Something like:

Dim allArr As System.Array
Dim a1() As String
Dim a2() As Integer
a1 = System.Array.CreateInstance(GetType(String), 2)
a1.SetValue("a", 0)
a1.SetValue("b", 1)

a2 = System.Array.CreateInstance(GetType(Int32), 2)
a2.SetValue(1, 0)
a2.SetValue(2, 1)

allArr = System.Array.CreateInstance(GetType(Array), 2)
allArr.SetValue(a1, 0)
allArr.SetValue(a2, 1)

For el As Integer = LBound(allArr) To UBound(allArr)
	Dim o As System.Array = allArr(el)
	For e0 As Integer = LBound(o) To UBound(o)
		MessageBox.Show(Me, o(e0))
	Next
Next

Regards,
mansii

 

[KreativeKai]

Thanks for your help. I couldn't get your solution to work, but your feedback is appreciated.

I found a solution at:

http://gotdotnet.com/Community/MessageBoard/Thread.aspx?id=363074&Page=1#363383

Thanks again.

Lost in the Vast Sea of .NET

 

[mansii]

No, it will not work. it needs to be customized to your needs.
Anyways, good to know that the problem solved.
And thanks for the link.

Cheers,
mansii