I have a menu system works beautifully. We are testing it, so one of the
people I sent it to was my daughter. Naturally, she was trying to break it
and ended up opening a particular excel spreadsheet 2 times. I DON'T WANT
THIS TO HAPPEN.

I have the below "working" macro. I need to add to it, a test, to see if the
file being opened is already open in a different instance of Excel. In other
words . . .

If the file to be opened by this Macro is already open in a different
instance of Excel, a message should be displayed stating that the file is
already open. Clicking the OK button would stop the macro.

It would also be nice if the file that is already open, that it would be
displayed.


MY MACRO IS AS FOLLOWS:

Sub NewExcelWithWorkbook()
Dim oXL As Object 'This is needed to open a new instance of Excel.
'Without it, the file is only opened as a new Window

Dim testFileFind
Dim oWB As Object

ActiveCell.Offset(0, -1).Activate 'this moves the selected cell 1 cell to
the Left

'The following tests for a blank cell and ends processing
'It is needed because dir() function will not work with a blank.

If Len(Trim(ActiveCell)) = 0 Then
MsgBox "Active Cell " & ActiveCell.Address & " is blank. You have not
entered a Path & File Name."
End
End If

'The following tests for the existance of the file

testFileFind = Dir(ActiveCell)

'If the file is not found there will be nothing
'in the variable and processing ends.

If Len(testFileFind) = 0 Then
MsgBox "Invalid selection." & Chr(13) & _
"Filename " & ActiveCell & " not found"
End
End If

'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
Set oXL = CreateObject("Excel.Application")

'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
oXL.Visible = True

Set oWB = oXL.Workbooks.Open(ActiveCell)

End Sub


Can someone please give me a hand ?

--
Please take a look at www.openoursite.com Click on: "Keywords" and then
Click on "Matt's Story" and if you are a man, you should be very happy that
you read my story. God Bless for everyones help.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200708/1