taigovinda
Well-known Member
- Joined
- Mar 28, 2007
- Messages
- 2,639
Hi,
I've been using the below code for awhile to append from a closed .xls into a .mdb (Access 2000 or 2003) file. I tried to use it for appending a .xlsm to the same Access file and it doesn't seem to like the line that I've marked in red... Is there something simple I can adjust to make this work, maybe change 4.0 to some other number?
If that's not the case, then maybe there is a better way to do this. My goal is, I have a workbook (.xlsm) with a named range, its headers match those of an Access table (.mdb). I want to append that named range to the Access table...
Thanks,
Tai
I've been using the below code for awhile to append from a closed .xls into a .mdb (Access 2000 or 2003) file. I tried to use it for appending a .xlsm to the same Access file and it doesn't seem to like the line that I've marked in red... Is there something simple I can adjust to make this work, maybe change 4.0 to some other number?
If that's not the case, then maybe there is a better way to do this. My goal is, I have a workbook (.xlsm) with a named range, its headers match those of an Access table (.mdb). I want to append that named range to the Access table...
Thanks,
Tai
Rich (BB code):
Sub AppendToAccess(Access_Full_Path As String, strWkBkFullPath As String, strSourceRangeName As String, strTblName As String)
'adapted from http://www.ozgrid.com/forum/showthread.php?t=36431&page=1
Dim db As Database
Dim XLTable As DAO.TableDef
Dim strSQL As String
'Open the Microsoft Access database.
Set db = OpenDatabase(Access_Full_Path)
' Create temp table
Set XLTable = db.CreateTableDef("Temp")
' connect append range
XLTable.Connect = "Excel 4.0;DATABASE=" & strWkBkFullPath '''''''''''ERROR HERE'''''''''''
XLTable.SourceTableName = strSourceRangeName
db.TableDefs.Append XLTable
' create the append query
strSQL = "Insert into [" & strTblName & "] Select * from Temp"
'Execute the SQL statement.
db.Execute strSQL
'Remove the temp table.
db.TableDefs.Delete "Temp"
db.Close
Set db = Nothing
Set XLTable = Nothing
End Sub