CODEWITHSUNDEEP
vb.netoledb
user1077685
user1077685

Reputation:

OleDB Get Table Issue - Sheet Name is Completely Numerical

I'm using the following function to get a sheet name from an OleDB Schema Table:

Public Function GetSheetName(ByVal dtSchema As DataTable) As String
        Dim sheetName As String = ""
        Try

            If Not dtSchema.Rows(0)("TABLE_NAME").ToString().Contains("#") Then
                sheetName = dtSchema.Rows(0)("TABLE_NAME").ToString()
            End If

        Catch ex As Exception
            BuildResultTraceString("Exception in GetSheetName")
            BuildResultTraceString(ex.Message)
        End Try

        Return sheetName
    End Function

And then I get the table like so:

sql.Append("SELECT * FROM [" & sheetName & "A22:T10000]")
dt = _dh.GetTable(sql.ToString())

However, when I try to load a sheet named '20130418113010937', the Get Table method catches this exception:

The Microsoft Access database engine could not find the object ''20130418113010937$'A2:I10000'

It works for all the other sheet names I'm using, but it won't work with this one. I assume that it's because it's completely numerical. Is there any workaround for this? The process is automated, so I cannot manually change the sheet name unless I somehow do it programmatically.

Thanks!

Upvotes: 0

Views: 339

Answers (1)

Jay Riggs
Jay Riggs

Reputation: 53603

You're right, the problem is the numeric sheet name. When you retrieve the sheet name using OleDB it does you a 'favor' by surrounding numeric names with single quotes (presumably to retain its stringyness).

Remove the single quotes from both ends of the table name and you should be good:

sheetName = dtSchema.Rows(0)("TABLE_NAME").ToString().Trim("'")

Upvotes: 1

Related Questions