Automate Excel from Access 2007

[Ryan]

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 hits the last line.

------------------------------------------------------------------------------------------
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 = strReportNam
------------------------------------------------------------------------------------------------

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.

 

[Tom van Stiphout]

On Wed, 15 Oct 2008 05:55:00 -0700, Ryan

Why are you using late binding?

This worked for me:
Dim objXL As Excel.Application 'Requires reference to Excel
Dim objWB As Excel.Workbook
Dim strReportName As String

'Note: next line had strReportTitle but that variable is
undefined.
strReportName = "Report Name"

'Create a new excel document
Set objXL = New Excel.Application
objXL.Visible = True 'So we can see what we're doing.

'To create new workbook
Set objWB = objXL.Workbooks.Add
objWB.Sheets(1).Name = strReportName

-Tom.
Microsoft Access MVP

 

[JP]

Since you're using automation, you might have to fully qualify the
application object reference.

objXL.objWB.Sheets(1).Name = strReportName

Otherwise I'd go with what Tom said, if you set a reference to the
Excel object library, you should declare the application object as
Excel.Application, not Object.

--JP

 

[Ryan]

Thanks but I still get the error.
--
Ryan

Since you're using automation, you might have to fully qualify the
application object reference.

objXL.objWB.Sheets(1).Name = strReportName

Otherwise I'd go with what Tom said, if you set a reference to the
Excel object library, you should declare the application object as
Excel.Application, not Object.

--JP

 

[Ryan]

Thanks for the excellent feedback.

I'm using late binding because it's the first way I was able to create an
Excel workbook and work with it. I'm open to a better way if you have one.

The variable declaration isn't causing the problem. I made a mistake
trimming down the code to just show the error.

I tried your suggestions but I still get the same error message on the same
line of code. Here it is with you suggestions and without being trimmed down:

Dim objXL As Excel.Application
Dim objWB As Excel.Workbook
Dim sheet As Object
Dim SheetName As String
Dim intColCount As Integer
Dim rstCount As Long
Dim varRange As String
Dim strCol As String
Dim Counter As Integer
Dim strReportName As String
Dim strReportTitle As String
Dim strInteriorPattern As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

strReportTitle = "Stock Record Account"
strReportName = strReportTitle & " Report"
strInteriorPattern = "xlSolid"

'Create a new excel document
Set objXL = New Excel.Application

objXL.Visible = True

'To create new workbook
Set objWB = objXL.Workbooks.Add
objWB.Sheets(1).Name = strReportName

All code that follows is formating and populating the worksheet with data....

Thanks for your help,

Ryan

 

[4hkrdr]

try rearranging your code in the following order:

Set objXL = New Excel.Application
'To create new workbook
objXL.Workbooks.Add
Set objWB = activeworkbook

objXL.Visible = True

objWB.Sheets(1).Name = strReportName

and the unreasons why:

#1: when using automation, Excel isn't happy doing anything until you add a new workbook. why? i don't know

#2: sometimes set x = workbooks.add fails, but .add and set x = activeworkbook works. why? don't know