Hi,
I am using an ADODB connection to connect to an Excel file (multiple worksheets) which is produced by modelling program. The format of the file is such that the column headings are 3 rows from the top, with the top row containg irrelevant information about the modeller, and the second row blank. The column headings are of type string, and the rest of the data is is of type integer. When I read the relevant worksheet into a recordset using SQL queries, the row where the column headers should be is full of null values. I have checked all the rows above and below (so I didn't get the row number wrong) as well as the column headings (just in case) and there is still no trace of the data I need. All the integer data reads in ok. I'm guessing I have this problem because there are two different data types in the worksheet, so is it possible to open the whole sheet up as type string or some equivalent? I need to extract the headings out so the users can view them, and they are liable to change between files (ie there is no way around having to read them in)
Here is my code (FileName has already been set, I am using VB 2005):
CODE
Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
Dim SheetName As String
Dim xlInput As ADODB.Connection
Dim tempRS As ADODB.Recordset
xlInput = New ADODB.Connection
With xlInput
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" + FileName + ";" & _
"Extended Properties=Excel 8.0;"
.Open()
End With
SheetName = lbxSheets.SelectedItem
tempRS = xlInput.Execute("SELECT * FROM [" + SheetName + "$]")
End Sub
Thanks
| 