Excel Help Please (1 Viewer)

[Kenln]

I have code that seems to work??? It opens an Excel Spread Sheet if it exists or does nothing if it does not exist (I will add a msg box if it does not). It also works if the Excel app is already open. However, it does not work if more than one Excel app is open.

Q: How do I check to see if it is open
Q: How do I set focus to an open book
Q: Am I asking the right questions?

Can someone help please?

Dim MyPath
MyPath = "\\dbtest" ' Set the path.
' The Directory begins with the PM.Value
MyPath = MyPath + "\" + Trim(PM.Value) + "\"

Dim MyName As String
MyName = Dir(MyPath) ' Retrieve the first entry.

Dim MyFile As String

Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName <> "." And MyName <> ".." Then

If MyName = [Job_No].Value Then

'Define and open Excel Template
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MyPath + MyName)
Xl.Visible = True
XlBook.Windows(1).Visible = True
Set XlSheet = XlBook.Worksheets(1)
XlBook.Sheets("C.O. log").Select

End If

End If
MyName = Dir ' Get next entry.

Loop

Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

 

[ejstefl]

This will show you how to tell if Excel is running:

http://www.mvps.org/access/modules/mdl0006.htm

 

[Kenln]

Thanks it's very close but I'm still stuck.

This tells me if Excel is running, but I only seem to have problems if my specific Workbook is open at the same time as other workbooks.

A) If no excel is open - no problem - This fix is a good test

B) If other excel apps are open but not the one I need - no problem

C) If other excel apps are open and the on I need is also open - crash.

 

[Kenln]

I think it should be something like this:

Dim XL As Object
Set XL = GetObject("\\server\Dbtest\PM\Job_No.xls", "Excel.Application")

However this only crashes. Any ideas.

I'm really stuck on this one and would appreciate any help.

 

[ejstefl]

I'm not really sure. I know that you can loop through all open workbooks and check the name like so:

With xlObj.Application
    For Each wbk In .Workbooks
        Debug.Print wbk.Name
    Next wbk
End With

However, I don't know how you could modify that for multiple instances of Excel being open...

 

[Kenln]

It took more than I thought but I finally got it to work. First I had to check if Excel was running, then I was able to use your code to see if the requested workbook as running.

Thanks a appreciate your help,

You saved me a lot of Msgbox "OOPS! Your workbook may or maynot already be open."