Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Opening a Workbook from Access 2016

Posted on 2016-09-14
3
Medium Priority
?
252 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 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 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 22
ID: 41799912
you're welcome ~ happy to help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

926 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