Solved

Excel Automation with Access 2007

Posted on 2008-10-15
6
724 Views
Last Modified: 2013-11-27
The following protion of code worked perfect with Access 2003 and after I updated 2003 to 2007. Now we have new computers with a fresh install of Office 2007 and now I get Run-time error '-2147417851 (80010105)': Method 'Add' of object 'Workbooks' failed when the compiler tries to add a workbook (Set objWB = objXL.Workbooks.Add). I saw another post about early and late binding so I tried:
Set objWB = objXL.Workbooks
objWB.Add
but that didn't work either.  

I added all the references that were added in 2003 except of course the Office references are now 12 instead of 11.

If anyone knows what may be causing this I would greatly appreciate some assistance.

Dim objXL As Object

    Dim objWB As Object

    Dim strReportName As String

 

    strReportTitle = "Report Name"

      

    'Create a new excel document

    Set objXL = CreateObject("Excel.Application")

    

    'To create new workbook

    Set objWB = objXL.Workbooks.Add 'This now creates the error. With or without parenthesis () at the end.

    objWB.Sheets(1).Name = strReportName

Open in new window

0
Comment
Question by:OpieTaylor
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22723621
The only error I got was on:

strReportTitle = "Report Name"

It should be :

strReportName = "Report Name"

I also added the line below to see the results:

objXL.Visible = True

See the code snippet for the code that works for me in Access 2007 and Excel 2007.
Public Sub Test()

    Dim objXL As Object

    Dim objWB As Object

    Dim strReportName As String

    strReportName = "Report Name"

    'Create a new excel document

    Set objXL = CreateObject("Excel.Application")

    objXL.Visible = True

    'To create new workbook

    Set objWB = objXL.Workbooks.Add

    objWB.Sheets(1).Name = strReportName

End Sub

Open in new window

0
 

Author Comment

by:OpieTaylor
ID: 22728201
Thanks but making it visible before adding a workbook resulted in the same error message. Besides, I don't want the user to see Excel until after the workbook is added. I only want them to see the end result.
Did you execute this code with Access 2007? If so, did you upgrade from a previous version of Access? This code works fine on computers upgraded from previous versions of office but not on brand new machines that  have 2007 installed without upgrades.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22730010
My machine had Office 2007 SP1 installed on Vista Enterprise from the beginning. I executed the code in a module in Access 2007. We tried adding Office 2000 to my computer but Access 2000 quits working after the first time I use Access 2007.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:OpieTaylor
ID: 22738329
It must have something to do with Windows XP then. I'm running Office 2007 SP1 on XP SP3.
0
 

Accepted Solution

by:
OpieTaylor earned 0 total points
ID: 22783537
I figured out th problem.
A program called "ApproveIt" added the following registry key: HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\ADTAPILib.ApproveItAddin
and set the load behavior to 3. If I delete this key or change it to a value other than 3, everything works fine.
So ApproveIt is the problem...
Thanks for you help.
0
 

Expert Comment

by:KevinS2112
ID: 34176874
I had the same problem in Word where I was getting the same error, but on the doc.SaveAs method.

I removed the ApproveIt add-in and got rid of the problem.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now