Solved

open xls workbook, check if already open

Posted on 2013-12-09
5
682 Views
Last Modified: 2014-01-07
In MS Access I want to first check to see if Excel is open.  If so, check to see if a certain workbook is already open.  If not open it, otherwise set the open workbook as the active workbook.

Here is what I have so far.  Almost there but if it is already open it gives a warning about re-opening an open workbook.

Private Function UpdateCrisisChart()
Dim xls As Object
Dim wrk As Object
Dim Sheet As Object
Dim strExcelFile As String
        
    If IsExcelRunning Then
        Set xls = GetObject(, "Excel.Application")
    Else
        Set xls = CreateObject("Excel.Application")
    End If
    
    xls.Visible = True
    
    strExcelFile = GetExcelFilename(Me.SDate, "Adult")
    Set wrk = xls.Workbooks.Open(strExcelFile)
        
End Function

Open in new window

0
Comment
Question by:KCTechNet
  • 2
  • 2
5 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 39706883
< but if it is already open it gives a warning about re-opening an open workbook.>

if you can get the error number from the warning message, grab that error code and
use it in your code to divert to the open workbook.
0
 

Author Comment

by:KCTechNet
ID: 39706935
Sounds good.  So on error trap, what do I use instead of .Open in:  
          Set wrk = xls.Workbooks.Open(strExcelFile)
0
 

Author Comment

by:KCTechNet
ID: 39707225
I should mention I am concerned if they have multiple workbooks open,  one of which is the one I need.  How do I set the one I want as active?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 39708365
You need to
1. error check to see if an existing instance of excel is open and respond accordingly
2. Check for the desired workbook and activate, create or open it accordingly

Try this, and read the inline explanations:

Sub DoStuffInExcel()


Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim wb As Excel.Workbook
Dim strPath As String
Dim blFound As Boolean

' This is the path/file we're working with -- change as needed
strPath = "c:\Test\TestXYZ.XLS"

On Error Resume Next
    Set xlApp = Nothing
    
    ' Try to use currently open application
    Set xlApp = GetObject(, "Excel.Application")
    
    ' If that fails, create a new application
    If Err = 429 Then
      Set xlApp = CreateObject("Excel.Application")
    End If
    
    On Error GoTo EH
    
    ' See if the workbook you need is currently open.  If so, set it as the workbook object.
    For Each wb In xlApp.Workbooks
        If wb.Name = Mid(strPath, InStrRev(strPath, "\") + 1) Then
            Set xlWB = wb
            blFound = True
            Exit For
        End If
    Next
    
    ' If not, then see if the file exists
    If blFound = False Then
        If Dir(strPath) = "" Then
            ' If it doesnt exist then create it
            Set xlWB = xlApp.Workbooks.Add
            xlWB.SaveAs strPath
        Else
            ' Otherwise open it
            Set xlWB = xlApp.Workbooks.Open(strPath)
        End If
    End If
    xlApp.Visible = True
    xlWB.Activate
    
    '  The rest of your code goes here
    
    
    
    
    ' Use the close and quit commands if needed.  If now, remove those lines but keep the = nothing lines.
    ' Close the workbook and clean up the objects
    xlWB.Close
    Set xlWB = Nothing
    ' Quit Excel and clean up the object
    xlApp.Quit
    Set xlApp = Nothing
    
    Exit Sub
    
EH:
    MsgBox "Error " & Err.Number & ": " & Err.Description
 
End Sub

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39708394
As an aside, I'm using "Early Binding" in my code, declaring the excel objects like this:

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim wb As Excel.Workbook

Open in new window


This makes for easy programming, because you get auto completion of your commands in the VB Editor,

However, if compatibility across multiple versions of Office is a concern, use late binding instead - declaring these as generic Objects:

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim wb As Object

Open in new window


The downside to Late Binding is that you lose the intellisense/autocompletion in programming, and possibly take a slight performance hit.

I typically keep both versions in my code and switch between them - commenting out the late binding in favor of early binding to get auto completion, and then switching back to late binding before deploying an application so that it will work regardless of Office version.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel if Match convert to Access 16 63
Send a .ZPL file to a network printer from Access 8 46
data analyst 3 48
Two list boxes - best structure 3 31
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
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…

929 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

18 Experts available now in Live!

Get 1:1 Help Now