I have the following Macro that works, but I am trying to make it check to
see if the workbook is already open . . . and if it is open, to give the
message and not open the file again.

Can someone give me a hand please.

Matt@Launchnet



Sub NewExcelWithWorkbookSpecialOne()
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

'The following tests for the existance of the file



'THIS MAY BE PROBLEM AREA . . .ALSO SEE FURTHER DOWN THE CODE JUST AFTER
'THE 'FOLLOWING IF STATEMENT WHERE THE PROBLEM MIGHT ALSO BE.

'I MAY BE COMPLETELY OFF.

testFileFind = Dir("c:\extrafiles\special1.xls") 'TRIED THIS FOR DIRECT
OPENING
'END OF PROBLEM AREA



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

If Len(testFileFind) = 0 Then
MsgBox "You do not have this file in C:\extrafiles\"
'Invalid selection." & Chr(13) & _"Filename " & ActiveCell & " not found"
End
End If







'I THINK I NEED SOMETHING LIKE THIS HERE, BUT NONE OF MY IDEAS WORK.

'ADDED FOR TEST
'Check if the file is already open, do nothing if so
'If Not IsFileOpen(ActiveCell.Value) Then
'NEW TRY
'If Not IsFileOpen("c:\extrafiles\special1.xls") Then
'NEW TRY
'If Not IsFileOpen(testFileFind) Then
'NEW TRY
'If Not IsFileOpen("c:\extrafiles\special1.xls") Then
'END ADD

'???????????????????????????????????????







'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("c:\extrafiles\special1.xls")


'ADDED FOR TEST TO DISPLAY MESSAGE
Else

MsgBox "File " & ActiveCell.Value & " is already open"
End If
'END ADD



End Sub

--
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