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 ?
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.
Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.