OleDB Connection Issues

[Devon_Nullman]

Here is the Class that is used - I believe it came from Karen

Public Class Connections
Public Sub New()
End Sub
''' <summary>
''' Create a connection where first row contains column names
''' </summary>
''' <param name="FileName"></param>
''' <param name="IMEX"></param>
''' <returns></returns>
''' <remarks></remarks>
<DebuggerStepThrough()>
Public Function HeaderConnectionString(ByVal FileName As String, Optional ByVal IMEX As Integer = 1) As String
Dim Builder As New OleDbConnectionStringBuilder
If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX={0};HDR=Yes;", IMEX))
Else
Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX={0};HDR=Yes;", IMEX))
End If

Builder.DataSource = FileName

Return Builder.ToString

End Function
''' <summary>
''' Create a connection where first row contains data
''' </summary>
''' <param name="FileName"></param>
''' <param name="IMEX"></param>
''' <returns></returns>
''' <remarks></remarks>
<DebuggerStepThrough()>
Public Function NoHeaderConnectionString(ByVal FileName As String, Optional ByVal IMEX As Integer = 1) As String
Dim Builder As New OleDbConnectionStringBuilder
If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX={0};HDR=No;", IMEX))
Else
Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX={0};HDR=No;", IMEX))
End If

Builder.DataSource = FileName

Return Builder.ToString

End Function
End Class


Code I am trying, which came from her as well

Private FileName As String = IO.Path.Combine("C:\Users\devon\Documents\[Redacted]", "[Redacted].xlsx")
Private Connection As New Connections

Using cn As New OleDbConnection With {.ConnectionString = Connection.NoHeaderConnectionString(FileName)}
'OR
Using cn As New OleDbConnection With {.ConnectionString = Connection.HeaderConnectionString(FileName)}


Dim SelectStatement As String = "SELECT * FROM 'Sheet1'" 'Syntax error in query. Incomplete query Clause.
Dim SelectStatement As String = "SELECT * FROM [Sheet1]" - 'Could not find the object
Dim SelectStatement As String = "SELECT * FROM Sheet1" - 'Could not find the object
Dim SelectStatement As String = "SELECT * FROM Sheet1$" - 'Syntax error in FROM
Dim SelectStatement As String = "SELECT * FROM 'Sheet1$'" - 'Incomplete query Clause.
Dim SelectStatement As String = "SELECT * FROM ['Sheet1$']" - '"Sheet1" is not a valid name

I can assure all that the spreadsheet exists and contains "Sheet1" - I am using Excel 2010

Continue reading...