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

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

If Not fileisopen(ActiveCell.Value) Then

Set oWB = oXL.Workbooks.Open(ActiveCell)
Else

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

End Sub


Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Launchnet" <u20911@uwe wrote in message news:76b699d8fd14a@uwe...
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

Thanks Bob

The macro ran until it reached the 6th line from the bottom of the sub
routine, then it gave message of Compile error: Sub or Function not definded.


The word fileisopen was highlited.

Additional information for you is that the activecell.value contains the
complete path and file name. I don't know if that would make a difference to
your changes or not.

I next tried entering . . . Dim fileisopen as object just under Dim oWB
as object
When I ran this, Excel opened but not the spreadsheet.
I closed Excel and the error message was:

Run-time error '91':
Object variable or With block variable not set

I clicked Debug and the 6th row from the bottom was highlited in yellow.

Any suggestions ?

p.s. If the file is already open, then Excel should not open again either.

Many thanks

Matt@Launchnet

Bob Phillips wrote:
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

If Not fileisopen(ActiveCell.Value) Then

Set oWB = oXL.Workbooks.Open(ActiveCell)
Else

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

End Sub

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

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
[quoted text clipped - 61 lines]

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

Mis-spelt my own fuynction

If Not fileisopen(ActiveCell.Value) Then


should be

If Not IsFileOpen(ActiveCell.Value) Then


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Launchnet via OfficeKB.com" <u20911@uwe wrote in message
news:76bcb5268ed17@uwe...
Thanks Bob

The macro ran until it reached the 6th line from the bottom of the sub
routine, then it gave message of Compile error: Sub or Function not
definded.


The word fileisopen was highlited.

Additional information for you is that the activecell.value contains the
complete path and file name. I don't know if that would make a difference
to
your changes or not.

I next tried entering . . . Dim fileisopen as object just under Dim
oWB
as object
When I ran this, Excel opened but not the spreadsheet.
I closed Excel and the error message was:

Run-time error '91':
Object variable or With block variable not set

I clicked Debug and the 6th row from the bottom was highlited in yellow.

Any suggestions ?

p.s. If the file is already open, then Excel should not open again
either.

Many thanks

Matt@Launchnet

Bob Phillips wrote:
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

If Not fileisopen(ActiveCell.Value) Then

Set oWB = oXL.Workbooks.Open(ActiveCell)
Else

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

End Sub

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

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
[quoted text clipped - 61 lines]

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

Hi Bob . . .

It works fine, except that Excel still opens the second time, but the file
does not. When I close Excel, the message box is displayed and has the
correct message. How can I stop Excel from opening ? I wish I was smart
enough to do what you guys do with code. I know what I want to happen, but
the coding is a nightmare for me. And, at 75 years old, I don't think I will
ever come close to you guys.

Again, Many Thanks

Matt

Bob Phillips wrote:
Mis-spelt my own fuynction

If Not fileisopen(ActiveCell.Value) Then

should be

If Not IsFileOpen(ActiveCell.Value) Then

Thanks Bob

[quoted text clipped - 100 lines]

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 http://www.officekb.com

Matt,

You need an instance of Excel to check if the file is open.

However ...

What are you starting a new instance of Excel? If this code is running from
within Excel, could you not use that instance instead?

Where would the workbook be open, in another instance of Excel, or in this
instance?

If I could better understand what you are doing I could help to supply a
proper solution.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Launchnet via OfficeKB.com" <u20911@uwe wrote in message
news:76bdd8fb2458b@uwe...
Hi Bob . . .

It works fine, except that Excel still opens the second time, but the file
does not. When I close Excel, the message box is displayed and has the
correct message. How can I stop Excel from opening ? I wish I was smart
enough to do what you guys do with code. I know what I want to happen,
but
the coding is a nightmare for me. And, at 75 years old, I don't think I
will
ever come close to you guys.

Again, Many Thanks

Matt

Bob Phillips wrote:
Mis-spelt my own fuynction

If Not fileisopen(ActiveCell.Value) Then

should be

If Not IsFileOpen(ActiveCell.Value) Then

Thanks Bob

[quoted text clipped - 100 lines]

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 http://www.officekb.com