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