After creating a delimited (tab) text file from a table in a MDB database file I convert the text file to an Excel file using the Workbooks.OpenText method. Works a treat....except I wish to format some columns in the process using the OpenText method which usually is very easy.

The code below uses arrays to hold the column information but it seems to have no effect on the columns format when I open the file in Excel. I suspect I am overlooking something with respect of the array. Any help would be appreciated as I seem to have dug myself a big hole.

'set the column numbers
Dim ColumnDesired() As Int16 = {2,4}
'set the column formats for the columns
'2=text and 4=dmy date
Dim DataTypeArray() As Int16 = {2,4}
'number of columns and number of formats
Dim ColumnArray(6, 1) As Int16
Dim x As Integer


' Create a For-Next that populates the two dimensions of
' the ColumnArray array.
For x = LBound(ColumnDesired) To UBound(ColumnDesired)
ColumnArray(x, 0) = ColumnDesired(x)
ColumnArray(x, 1) = DataTypeArray(x)
Next x

'Open the text file and save it in the Excel workbook format.
oExcel.Workbooks.OpenText("C:\MyFile.txt", _
, , 1, -4142, , True, , , , , _
ColumnArray)