DAO to append range from closed XLSM to an MDB table

[taigovinda]

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

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

 

[taigovinda]

Correction, my original code that was working said 5.0, not 4.0.... must've got it switched around when I hoped just tweaking that # would fix it.

Still hoping someone can show me how to make this work or use a different method to achieve my goal.

Thanks.
Tai