Solved

Opening a Workbook from Access 2016

Posted on 2016-09-14
3
77 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

825 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