Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-03-23
11
453 Views
Last Modified: 2012-05-11
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
Comment
Question by:JCutcliffe
  • 6
  • 5
11 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35201618
Which line is giving you the error?

I managed to test the above till

db.TableDefs.Append XLTable

without any errors.

Sid
0
 

Author Comment

by:JCutcliffe
ID: 35205382
The line that is giving me the error is

db.TableDefs.Append XLTable.

Any ideas?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205402
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:JCutcliffe
ID: 35206007
No, I can't see the table being produced. Not why as it used to.
0
 

Author Comment

by:JCutcliffe
ID: 35206015
I mean. I am not sure why as it used to work
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35206096
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
 

Author Comment

by:JCutcliffe
ID: 35206193
I am using Excel 2007. Heres the Excel file
IEB-ORACLE-Mar-11-Dailies-Templa.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35206224
>>>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
 

Author Comment

by:JCutcliffe
ID: 35206304
just did it. i get the error message "Run Time error 3170 - could not find installable ISAM". what does that mean?
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35206618
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35206731
Ok now tell me which line was giving you the error ;)

Sid
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Many businesses neglect disaster recovery and treat it as an after-thought. I can tell you first hand that data will be lost, hard drives die, servers will be hacked, and careless (or malicious) employees can ruin your data.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question