GetObject or CreateObject... vb6

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")
Else
   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.

Thanks,
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
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
Next

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

on error goto 0
...

?
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
If there is no excel open, it won't go beyond:

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

I think. brb
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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

Mike
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Ryan ChongCommented:
i think the xtmpWb.FullName returns the full path of the workbook, isn't it?
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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.

Regards,

Mike
0
 
DaveCommented:
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")
    Else
        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

Cheers

Dave
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Dave,

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.

Regards,

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.