Opening a Workbook from Access 2016

Hi,

My client is upgrading from Office 2010 to Office 2016 so I need to get the following code to work but I don't understand why the following won't work.

I am getting the following error unless I set Excel to be visible:

Runtime Error -2147417851 (80010105)
Method 'Open' of object 'Workbooks' failed


dim objXL as Object
Dim objWkb As Object

Set objXL = New Excel.Application
stDealerAC = "ABBEYNW"
'objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("H:\order book\order_status\" & stDealerAC & ".xls")


Question:  How can I get it to run without objXL.Visible = True?
LJKMartinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the variables are dimensioned as object so that late binding can be used.  This statement, however, requires early binding:
Set objXL = New Excel.Application
Instead, use
 Set objXL = CreateObject("Excel.Application")

Open in new window

You can also test if Excel is open and first try Set xlApp = GetObject(, "Excel.Application") and only CreateObject if that is an error.
   'attempting to use something that is not available
   'will generate an error
   On Error Resume Next
   Set objXL = GetObject(, "Excel.Application")
   On Error GoTo Proc_Err 'line label for error code*
  
   'If objXL is defined, then we
   'already have a conversation
   If TypeName(objXL) = "Nothing" Then
      booLeaveOpen = False 'dimension this as boolean and test to see if you need to objXL.quit
      'Excel was not open -- create a new instance
      Set objXL = CreateObject("Excel.Application")
   End If

Open in new window


Once you switch this, objXL.Visible = True (or False) should work too.

Don't forget to save and close the workbook, release objWkb,  and quit and release objXL, in that order.

*error handling videos on EE:

1. basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

2. Run and Fix Code Loop through rows of an Excel spreadsheet using VBA (6:00)
http://www.experts-exchange.com/videos/1498/Excel-Error-Handling-Part-2-VBA-to-Copy-Values-Down-to-Blank-Cells-in-an-Excel-Column.html

3. Error Handling Part 3 - Run and Fix Bugs (7:51)
http://www.experts-exchange.com/videos/1518/Excel-Error-Handling-Part-3-Run-and-Fix-Bugs.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LJKMartinAuthor Commented:
Thanks Crystal
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

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.