Import Excel Sheet to Back-End Database (Use Named Range)

Question

I was not able to get it to import a named range of MS Excel spreadsheet into my back-end database. Does anyone know if this is possible, and if so, the correct syntax ?

Answer

Jeff,

This should work:

Dim dbs As Database
Dim strSql As String
Dim strBackEndMdbPath As String
Dim strImportedTableName As String
Dim strWorkBookPath As String
Dim strNamedRange As String

strBackEndMdbPath = "c:\temp\db2.mdb"
strImportedTableName = "tblMyNr"
strWorkBookPath = "c:\temp\Book1_test.xls"
strNamedRange = "MyNR"

Set dbs = DBEngine(0).OpenDatabase(strBackEndMdbPath)
strSql = "Select * into [" & strImportedTableName & "] from [" & _
              strNamedRange & "] in '' [" & "Excel 8.0;" & "HDR=YES;IMEX=2;DATABASE=" & _
              strWorkBookPath & "]"

On Error Resume Next
dbs.TableDefs.Delete strImportedTableName
dbs.TableDefs.Refresh

On Error GoTo 0
dbs.Execute strSql, dbFailOnError

strImportedTableName shouldn't be equal to strNamedRange.

HTH,
Shamil