Select query does not work when column name has parenthesis in excel sheet

Question

Hi,
I am trying to fill the data of an excel sheet in a data table by using select query. When the sheet has parenthesis or any other special characters in its column names, I replace each one with "#" but execution results in error.

 Private Sub ReadExcel
    Dim sqlReadExcel as String = "Select [ModelCode] as ModelCode,[ListPrice] as ListPrice,[DiscountMultiplier] as Property39,[Cost] as Cost,[LeadTime#days#] as 
          LeadTime,[Manufacturer] as Manufacturer from [PPIF 2.6 Price Format$] where [ModelCode] = 'CP102-1-B-16S-005'"

     If sqlReadExcel <> Nothing Then

            Dim oDatabaseconnString As String
            Dim dbConnection As OleDbConnection
            Dim oDataAdapter As OleDbDataAdapter
            Dim excelPath  as String = "E:\excelFile.xlsx"
            oDatabaseconnString = "PROVIDER='Microsoft.ACE.OLEDB.12.0;Data Source='" & excelPath & "';Extended Properties='Excel 8.0;'"
            dbConnection = New OleDbConnection(oDatabaseconnString)
            dbConnection.Open()
            oDataAdapter = New OleDbDataAdapter(sqlReadExcel, dbConnection)
            Try
                oDataAdapter.Fill(dtExcelData)
            Catch ex As Exception
            Finally
                oDataAdapter.Dispose()
                oDataAdapter = Nothing
            End Try

            dbConnection.Close()
        End If

      End Sub

The execution of 16th line results in exception. The exception message is "Specified cast is not valid."

Any suggestions what should I do?
Thanks in advance

Answer 1

After the experiments, add the problematic column. If it is "LeadTime (days)" in Excel, then:

Select ModelCode, [LeadTime (days)] from [PPIF 2.6 Price Format$] where [ModelCode] = 'CP102-1-B-16S-005'

Use the exact name of the column.

Also try adding two parameters to connection string:

oDatabaseconnString = "PROVIDER= . . .;HDR=YES;IMEX=1"