I wish to open ".csv" files using a VSTO Excel 2003 template. All the files have 4 columns each, and all should be set as "XlColumnDataType.xlTextFormat.
I'm stumbling with the FieldInfo parameter of the Workbooks.OpenText method. The documentation says:
FieldInfo: Optional XlColumnDataType. An array containing parse information for 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.
1: How does one do that (I can't find an example of code for this.)
Here's what I have so far:
If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
'Other code goes here ...
Dim strCsvFullName As String = OpenFileDialog1.FileName
Dim xlCsvText As Excel.XlTextParsingType = _
Excel.XlTextParsingType.xlDelimited
Dim xlText As Excel.XlColumnDataType = _
Excel.XlColumnDataType.xlTextFormat
Globals.ThisWorkbook.Application.Workbooks.OpenText( _
Filename:=strCsvFullName, _
StartRow:=1, _
DataType:=xlCsvText, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=xlText )
End If
2: If the number of columns weren't known ahead of time, how would one write a function to find the number of "two-element arrays"
For reference, here's what the code looks like in VBA:
Workbooks.OpenText(Filename:= _
vrtSelectedItem, Origin:=-535, StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlNone, 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, 2)), TrailingMinusNumbers:=True)
Thanks,
Noel C. Gordon.