vb.net creating QueryTables in Excel 1

[JScannell]

One of the properties of the QueryTables object is named TextFileColumnDataTypes and is supposed to be an Array. I'm passing in an integer array of the column data types (for instance, a value of 2 would be a Text column).

I've tried everything I can think of to set the property value to the passed in variable, to include:
destinationSheet.QueryTables(1).TextFileColumnDataTypes = columnDataTypes.ToArray()
destinationSheet.QueryTables(1).TextFileColumnDataTypes = columnDataTypes

But either method produces a "The parameter is incorrect" runtime error.

If I hard-code the property value like this:
destinationSheet.QueryTables(1).TextFileColumnDataTypes = New Integer() { 2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2 }

then it works, but that isn't very scalable. the number of columns and the data types aren't always going to be that set.


Has anyone done this kind of thing and know what to do with this property? The side effect of not being able to successfully apply this parameter is that a zip code column ends up with leading 0's
truncated. If I could just tell the object that I have a text column there (without having to hard code it!!), it would work.

Here's the entire function, if it will help you:

Public Sub ImportCSV ( oExcel As Excel.Application,
importFileName As String,
destinationSheet As Excel.Worksheet,
strCellrange As String,
columnDataTypes() As integer,
autoFitColumns As boolean )

Dim oLocalrange As Excel.Range = CType ( destinationSheet.Range ( strCellrange ), Excel.Range )

destinationSheet.QueryTables.Add ( "TEXT;" & importFileName, oLocalrange, Type.Missing )

destinationSheet.QueryTables(1).Name = Path.GetFileNameWithoutExtension ( importFileName )
destinationSheet.QueryTables(1).FieldNames = true
destinationSheet.QueryTables(1).RowNumbers = false
destinationSheet.QueryTables(1).FillAdjacentFormulas = false
destinationSheet.QueryTables(1).PreserveFormatting = true
destinationSheet.QueryTables(1).RefreshOnFileOpen = false
destinationSheet.QueryTables(1).RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells
destinationSheet.QueryTables(1).SavePassword = false
destinationSheet.QueryTables(1).SaveData = true
destinationSheet.QueryTables(1).AdjustColumnWidth = false
destinationSheet.QueryTables(1).RefreshPeriod = 0
destinationSheet.QueryTables(1).TextFilePromptOnRefresh = false
destinationSheet.QueryTables(1).TextFilePlatform = 437
destinationSheet.QueryTables(1).TextFileStartRow = 2
destinationSheet.QueryTables(1).TextFileParseType = Excel.XlTextParsingType.xlDelimited
destinationSheet.QueryTables(1).TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierNone

destinationSheet.QueryTables(1).TextFileConsecutiveDelimiter = false
destinationSheet.QueryTables(1).TextFileTabDelimiter = false
destinationSheet.QueryTables(1).TextFileSemicolonDelimiter = false
destinationSheet.QueryTables(1).TextFileCommaDelimiter = true
destinationSheet.QueryTables(1).TextFileSpaceDelimiter = false

destinationSheet.QueryTables(1).TextFileColumnDataTypes = columnDataTypes.ToArray()

destinationSheet.QueryTables(1).Refresh ( false )

if autoFitColumns = true then
destinationSheet.QueryTables(1).Destination.EntireColumn.AutoFit()
End If

' cleanup
oExcel.ActiveSheet.QueryTables(1).Delete()
End Sub




Thanks in advance,
Jerry

Jerry Scannell

 

[JScannell]

OK. I got it to work, but I don't understand why I had to do this.
First, to put everything in order:
1. Passing in an integer() columnDataTypes As Integer() (I have to call this an Array as I don't know what else to call it). It consists of a 250 element integer array defined as: Dim aColumnFormats(250) As Integer
2. In the ImportCSV() function I created a local Integer list: Dim locColumnTypes As New System.Collections.Generic.List(Of Integer)
3. I then looped through my input parameter array adding to the List:
For Each x In columnDataTypes
If x = 0 then
Exit For
End If

locColumnTypes.Add( x )
Next
4. then set the property like this: destinationSheet.QueryTables(1).TextFileColumnDataTypes = locColumnTypes.ToArray() AND IT WORKED!!

Why did I have to do all of this? Trying to utilize the input integer array directly like this: columnDataTypes.ToArray() generated a runtime error.

Thanks in advance,


Jerry Scannell