Solved

Opening a Workbook from Access 2016

Posted on 2016-09-14
3
60 Views
Last Modified: 2016-09-15
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?
0
Comment
Question by:LJKMartin
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41798243
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
 

Author Closing Comment

by:LJKMartin
ID: 41799346
Thanks Crystal
0
 
LVL 19
ID: 41799912
you're welcome ~ happy to help
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 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

24 Experts available now in Live!

Get 1:1 Help Now