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

Excel Automation with Access 2007

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
OpieTaylor
Asked:
OpieTaylor
  • 3
  • 2
1 Solution
 
Chuck WoodCommented:
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
 
OpieTaylorAuthor Commented:
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
 
Chuck WoodCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
OpieTaylorAuthor Commented:
It must have something to do with Windows XP then. I'm running Office 2007 SP1 on XP SP3.
0
 
OpieTaylorAuthor Commented:
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
 
KevinS2112Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now