CreganTur
05-27-2008, 07:56 AM
I've got some code that opens a file dialog window so that the user can select Excel files. The code then grabs the filepath of the Excel spreadsheet, grabs the name of the worksheets in the spreadsheet, and then creates a Linked Table out of the specified filepath and worksheet name.
This code works perfectly, but only for the first worksheet in the collection. If there is more than 1 worksheet then the code will throw error 3011 when it tries to make the second worksheet into a linked table.
Here's the code for getting the worksheet name and the code for creating a linked table:
Dim excelname As String, AppExcel As New Excel.Application, Wkb As Workbook, Wksh As Worksheet
Dim obj As AccessObject, dbas As Object, tempTable As String, spaceIn As Integer
Dim count As Integer
excelname = strFilepath
Set Wkb = AppExcel.Workbooks.Open(strFilepath)
For Each Wksh In Wkb.Worksheets
'--------------------
'make the selected Excel file into a Linked Table
Dim myDB As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String
Dim LinkExcel As Variant
Set myDB = CurrentDb()
stSource = Wksh.Name
stConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFilepath
'Make chosen Excel file into a Linked Table titled mySheet
Set tbl = myDB.CreateTableDef("mySheet")
tbl.Connect = stConnect
tbl.SourceTableName = stSource
myDB.TableDefs.Append tbl
LinkExcel = True
I've also attached a sample database that shows the entire process in action. It includes 2 sample spreadsheets (the data in them is fake). If you look at the database, then be sure to click on the "Load Data From Customers Excel Spreadsheet" button- it has this code behind it. The other button has an older version of code.
This code works perfectly, but only for the first worksheet in the collection. If there is more than 1 worksheet then the code will throw error 3011 when it tries to make the second worksheet into a linked table.
Here's the code for getting the worksheet name and the code for creating a linked table:
Dim excelname As String, AppExcel As New Excel.Application, Wkb As Workbook, Wksh As Worksheet
Dim obj As AccessObject, dbas As Object, tempTable As String, spaceIn As Integer
Dim count As Integer
excelname = strFilepath
Set Wkb = AppExcel.Workbooks.Open(strFilepath)
For Each Wksh In Wkb.Worksheets
'--------------------
'make the selected Excel file into a Linked Table
Dim myDB As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String
Dim LinkExcel As Variant
Set myDB = CurrentDb()
stSource = Wksh.Name
stConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFilepath
'Make chosen Excel file into a Linked Table titled mySheet
Set tbl = myDB.CreateTableDef("mySheet")
tbl.Connect = stConnect
tbl.SourceTableName = stSource
myDB.TableDefs.Append tbl
LinkExcel = True
I've also attached a sample database that shows the entire process in action. It includes 2 sample spreadsheets (the data in them is fake). If you look at the database, then be sure to click on the "Load Data From Customers Excel Spreadsheet" button- it has this code behind it. The other button has an older version of code.