• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

Excel Macro that Exports & Appends data to an Access Table -ERROR MESSAGE

Hi Guys, I am getting an Error Message on an Excel Macro that is saying "RUN Time Error 3011
The Microsoft Jet Database Engine could not find the object "Excel Data2". Make sure the Object exists and that you spell its name and path correctly". This is an Excel Macro that exports data from Excel & appends it in an Access table. I am not sure why but it used to work, but now it doesn't. See code down below. Anyone have any ideas?
Sub APPEND_DATA_TO_ACCESS()
    Dim ExcelBook2 As String
    Dim db As Database
    Dim AccessMDB As String
    Dim XLTable As TableDef
    Dim SQLstring As String
    '---------------------------------------------------------------------------------
    '- 1. SET THE NEW EXCEL NAMED RANGE ("ExcelData")
    rg = ActiveSheet.Range("A1").CurrentRegion.Address
    ActiveWorkbook.Names.Add Name:="ExcelData2", _
                         RefersTo:="=" & ActiveSheet.Name & "!" & rg
    '---------------------------------------------------------------------------------
    '- LINK TO THE ACCESS DATABASE
    AccessMDB = "g:\Restricted\UKMSI-FA-FS-EUC01 Man Securities Global EUCs\Equities 2011\Dailies Databases\FEB 2011\Feb ManSecs Dailies- Oracle.mdb"
    Set db = DBEngine(0).OpenDatabase(AccessMDB)
    '---------------------------------------------------------------------------------
    '- 2. ACCESS : MAKE THE TEMPORARY TABLE ("Temp"). LINK IT TO THE EXCEL DATA
    ExcelBook2 = ThisWorkbook.FullName
    Set XLTable = db.CreateTableDef("Temp55")
    '*********************************************************************************
    XLTable.Connect = "Excel 8.0;DATABASE=" & ExcelBook2      ' CHECK VERSION        **
    '*********************************************************************************
    XLTable.SourceTableName = "ExcelData2"
    db.TableDefs.Append XLTable
    '---------------------------------------------------------------------------------
    '- 3. ACCESS : APPEND DATA FROM Temp TABLE TO ExcelData TABLE. DELETE Temp TABLE
    strsql = "INSERT INTO [GFM Daily PandL] SELECT * FROM Temp55"
    db.Execute strsql
    db.TableDefs.Delete "Temp55"
    db.Close
    Set XLTable = Nothing
    Set db = Nothing
    '---------------------------------------------------------------------------------
    MsgBox ("Done")
End Sub
0
JCutcliffe
Asked:
JCutcliffe
  • 6
  • 5
1 Solution
 
SiddharthRoutCommented:
Which line is giving you the error?

I managed to test the above till

db.TableDefs.Append XLTable

without any errors.

Sid
0
 
JCutcliffeAuthor Commented:
The line that is giving me the error is

db.TableDefs.Append XLTable.

Any ideas?
0
 
SiddharthRoutCommented:
That is surprising become that line didn't error out when I tested it on my machine.

Was it able to create the table "Temp55" when you ran the code?

Sid
0
A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

 
JCutcliffeAuthor Commented:
No, I can't see the table being produced. Not why as it used to.
0
 
JCutcliffeAuthor Commented:
I mean. I am not sure why as it used to work
0
 
SiddharthRoutCommented:
Can you just keep one record in the table and delete the rest and upload the table here? Let me test it for you real time. Also which Excel version are you using?

Sid
0
 
JCutcliffeAuthor Commented:
I am using Excel 2007. Heres the Excel file
IEB-ORACLE-Mar-11-Dailies-Templa.xlsm
0
 
SiddharthRoutCommented:
>>>I am using Excel 200

Then try this

XLTable.Connect = "Excel 12.0;DATABASE=" & ExcelBook2

instead of

XLTable.Connect = "Excel 8.0;DATABASE=" & ExcelBook2

Sid
0
 
JCutcliffeAuthor Commented:
just did it. i get the error message "Run Time error 3170 - could not find installable ISAM". what does that mean?
0
 
SiddharthRoutCommented:
Ok try this :)

I think this should work.

Sub APPEND_COMMS_DATA_TO_ACCESS3()
    Dim ExcelBook As String
    Dim db As Database, fld1 As Field
    Dim AccessMDB2 As String
    Dim XLTable2 As TableDef
    Dim SQLstring As String
    
    rg = ActiveSheet.Range("A1").CurrentRegion.Address
    
    ActiveWorkbook.Names.Add Name:="ExcelData", _
    RefersTo:="='" & ActiveSheet.Name & "'!" & rg
    
    AccessMDB2 = "g:\Restricted\UKMSI-FA-FS-EUC01 Man Securities Global EUCs\Equities 2011\Dailies Databases\FEB 2011\Feb ManSecs Dailies- Oracle.mdb"
    
    Set db = DBEngine.Workspaces(0).OpenDatabase(AccessMDB2)
    ExcelBook = ThisWorkbook.FullName
    
    Set XLTable2 = db.CreateTableDef("Temp58")
    
    XLTable2.Connect = "Excel 8.0;DATABASE=" & ExcelBook
    XLTable2.SourceTableName = "ExcelData"
    db.TableDefs.Append XLTable2
    db.TableDefs.Refresh
    strsql = "INSERT INTO [comm] SELECT * FROM Temp58"
    db.Execute strsql
    db.TableDefs.Delete "Temp58"
    db.Close
    Set XLTable2 = Nothing
    Set db = Nothing
    MsgBox ("Done")
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
Ok now tell me which line was giving you the error ;)

Sid
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now