[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-03-23
11
Medium Priority
?
462 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
[X]
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
  • 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
Q2 2017 - Latest Malware & Internet Attacks

WatchGuard’s Threat Lab is a group of dedicated threat researchers committed to helping you stay ahead of the bad guys by providing in-depth analysis of the top security threats to your network.  Check out our latest Quarterly Internet Security Report!

 

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 2000 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

Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

Question has a verified solution.

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

This article covers the basics of data encryption, what it is, how it works, and why it's important. If you've ever wondered what goes on when you "encrypt" data, you can look here to build a good foundation for your personal learning.
An overview of cyber security, cyber crime, and personal protection against hackers. Includes a brief summary of the Equifax breach and why everyone should be aware of it. Other subjects include: how cyber security has failed to advance with technol…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

650 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