GetObject or CreateObject... vb6

Posted on 2004-09-15
Last Modified: 2008-01-09
From vb form, I am selecting a workbook which it may or may not already open.  

Public Sub OpenExcelFile(strPath As String)

Dim xlWb As Excel.Workbook

If <file is already open?> Then                    ' What logic can I use here, or I have use error number to handle it?
   Dim xlObj As Excel.Application
   Set xlObj = GetObject(,"Excel.Application")
   Dim xlObj As New Excel.Application
   Set xlObj = CreateObject(,"Excel.Application")
End if

Set xlWb = xlObj.OpenWorkBook(strPath)

Set xlObj = Nothing
Set xlWb = Nothing

Please QC above code also.

Question by:Mike Eghtebas
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 52

Accepted Solution

Ryan Chong earned 500 total points
ID: 12072096
Try something like:

Dim xlObj As Excel.Application
Dim xWb As Excel.WorkBook
Dim xtmpWb As Excel.WorkBook
Dim isFound as boolean

on error resume next
   Set xlObj = GetObject(,"Excel.Application")

if xlObj is nothing then Set xlObj = CreateObject(,"Excel.Application")

isFound  = false
For each xtmpWb in xlObj.workbooks
   if xtmpWb.FullName = strPath then
          Set xWb = xtmpWb
          isFound = true
          exit for
   end if

if isfound = false then Set xlWb = xlObj.OpenWorkBook(strPath)

on error goto 0

LVL 34

Author Comment

by:Mike Eghtebas
ID: 12072111
If there is no excel open, it won't go beyond:

Set xlObj = GetObject(,"Excel.Application")

I think. brb
LVL 34

Author Comment

by:Mike Eghtebas
ID: 12072391
Now, I can see your logic regarding Set xlObj = GetObject(,"Excel.Application").

Btw, what happens if we had two workbooks with the same name but in different folder?  How following code would know what 'FullName' to consider:

if xtmpWb.FullName = strPath then

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

LVL 52

Expert Comment

by:Ryan Chong
ID: 12072519
i think the xtmpWb.FullName returns the full path of the workbook, isn't it?
LVL 34

Author Comment

by:Mike Eghtebas
ID: 12072577
I used:

if frmMain.cboFolder & "\" & xtmpWb.Name = strPath then

Because there maybe to identical xl file present in two different folders; therefore, xtmpWb.FullName cann't tell what xtmpWb we intend to open.

You were great as always.


LVL 50

Expert Comment

by:Dave Brett
ID: 12072600
If GetObject returns an Excel instance then rather than loop through all open workbooks you could attempt to set xWb to yourbook name - if an error occurs then the file is closed

Dim xlObj      As Excel.Application
Dim xWb        As Excel.Workbook
Dim xtmpWb     As Excel.Workbook
Dim isFound    As Boolean

    On Error Resume Next
    Set xlObj = GetObject(, "Excel.Application")

    If xlObj Is Nothing Then
        Set xlObj = CreateObject("Excel.Application")
        On Error Resume Next
        Set xtmpWb = xlObj.Workbooks("aa.xls")
        If Err.Number <> 0 Then Set xlWb = xlObj.Workbooks.Open("C:\test\aa.xls")
        On Error GoTo 0
    End If


LVL 34

Author Comment

by:Mike Eghtebas
ID: 12085718

Thank you for the code you provided.  As you know, your post was after the question has been closed -- to late to split.  

Although I am using the solution from ryancys, but I appreciate having your code.



Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month6 days, 11 hours left to enroll

634 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