Some of you will ask, why not simply use the TransferSpreadsheet method? The answer is that any use of TransferSpreadsheet method will display the Access NavPane, even if it is disabled in the Startup options. I know I could simply hide it again, but not without some screen flicker, and I want to avoid that.
Dim db as DAO.Database Dim tdf as DAO.Tabledef Set db = CurrentDb Set tdf = db.CreateTableDef("xl_NamedRange") tdf.Connect = "Excel 12.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & strExcelFileName tdf.SourceTableName = "MyRange" db.TableDefs.Append tdf
allows the code to link the range properly. However, when the workbooks tab name is numeric, this does not work:
tdf.SourceTableName = "MyTabName$MyRange"
and neither does
tdf.SourceTableName = "'1234'$MyRange"
tdf.SourceTableName = "1234$MyRange"
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.