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"
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE