Opening Excel Workbook More Than Once

[Launchnet]

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.aspx/excel-programming/200708/1

 

[Joel]

Try this

Sub getworkbooks()

Const findname = "BOOK1.XLS"

Found = False
For Each Wb In Workbooks
If UCase(Wb.Name) = UCase(findname) Then
Found = True
Exit For
End If
Next Wb

If Found = True Then
MsgBox ("found file " & findname & " Do not open the file again")
End If

End Sub

 

[Gary Keramidas]

this may work for you:

Sub test()
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("invoice_entry.xls") 'whatever the name is
If Err <> 0 Then
On Error GoTo 0
' workbook open code
MsgBox "File not open"
Else
MsgBox "file is open"
'do nothing
End If
End Sub

 

[Launchnet via OfficeKB.com]

Good Morning Joel . . . I appreciate the help.

The following is a copy of the code I used. Nothing Happens. Any Ideas ?
When I step thru it, the "Const findname ="specialone.xls" is skipped over.
When it gets to the If Found = True Then, it skips over the MsgBox and then
finishes.

Sub NewExcelWithWorkbookSpecialOne()

Const findname = "specialone.XLS"

Found = False
For Each Wb In Workbooks
If UCase(Wb.Name) = UCase(findname) Then
Found = True
Exit For
End If
Next Wb

If Found = True Then
MsgBox ("found file " & findname & " Do not open the file again")
End If

End Sub

Thanks
Matt@Launchnet

Try this

Sub getworkbooks()

Const findname = "BOOK1.XLS"

Found = False
For Each Wb In Workbooks
If UCase(Wb.Name) = UCase(findname) Then
Found = True
Exit For
End If
Next Wb

If Found = True Then
MsgBox ("found file " & findname & " Do not open the file again")
End If

End Sub

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

[quoted text clipped - 65 lines]

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.aspx/excel-programming/200708/1

 

[Launchnet via OfficeKB.com]

Good Morning Gary . . . Thanks for helping

The following is the code that I used of yours with a little extra that
needed to be used.
It opens the file, but if I use the macro a second time, it opens the file
again in "Read Only"

ANY IDEAS ?

Sub NewExcelWithWorkbookSpecialOne()
Dim wb As Workbook

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



'I ADDED THIS LINE OF CODE
Dim oWB As Object



On Error Resume Next
Set wb = Workbooks("special1.xls")
If Err <> 0 Then
On Error GoTo 0
' workbook open code



'I ADDED THE FOLLOWING 3 LINES OF CODE
'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")



'MsgBox "File not open"
Else
MsgBox "Workbook Is Already Open"
'do nothing
End If
End Sub

Thanks - I hope that I made everything clear.
Matt@Launchnet

this may work for you:

Sub test()
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("invoice_entry.xls") 'whatever the name is
If Err <> 0 Then
On Error GoTo 0
' workbook open code
MsgBox "File not open"
Else
MsgBox "file is open"
'do nothing
End If
End Sub

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

[quoted text clipped - 62 lines]

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.aspx/excel-programming/200708/1

 

[Gary Keramidas]

looks like your opening a 2nd instance of excel instead of opening the workbook
in the same instance that's already running. that's why you get the readonly
prompt,

--


Gary

Good Morning Gary . . . Thanks for helping

The following is the code that I used of yours with a little extra that
needed to be used.
It opens the file, but if I use the macro a second time, it opens the file
again in "Read Only"

ANY IDEAS ?

Sub NewExcelWithWorkbookSpecialOne()
Dim wb As Workbook

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



'I ADDED THIS LINE OF CODE
Dim oWB As Object



On Error Resume Next
Set wb = Workbooks("special1.xls")
If Err <> 0 Then
On Error GoTo 0
' workbook open code



'I ADDED THE FOLLOWING 3 LINES OF CODE
'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")



'MsgBox "File not open"
Else
MsgBox "Workbook Is Already Open"
'do nothing
End If
End Sub

Thanks - I hope that I made everything clear.
Matt@Launchnet

this may work for you:

Sub test()
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("invoice_entry.xls") 'whatever the name is
If Err <> 0 Then
On Error GoTo 0
' workbook open code
MsgBox "File not open"
Else
MsgBox "file is open"
'do nothing
End If
End Sub

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

[quoted text clipped - 62 lines]

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.aspx/excel-programming/200708/1

 

[Launchnet via OfficeKB.com]

Hi Again Gary
I want the application to open in a new instance of Excel, so that is working
correctly.
What is not working is the check to be sure that it doesn't open the new
instance of Excel or the File a second time, but gives a message stating that
the file is already open.

Thanks
Matt@Launchnet

Good Morning Gary . . . Thanks for helping

The following is the code that I used of yours with a little extra that
needed to be used.
It opens the file, but if I use the macro a second time, it opens the file
again in "Read Only"

ANY IDEAS ?

Sub NewExcelWithWorkbookSpecialOne()
Dim wb As Workbook

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



'I ADDED THIS LINE OF CODE
Dim oWB As Object



On Error Resume Next
Set wb = Workbooks("special1.xls")
If Err <> 0 Then
On Error GoTo 0
' workbook open code



'I ADDED THE FOLLOWING 3 LINES OF CODE
'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")



'MsgBox "File not open"
Else
MsgBox "Workbook Is Already Open"
'do nothing
End If
End Sub

Thanks - I hope that I made everything clear.
Matt@Launchnet

this may work for you:

[quoted text clipped - 17 lines]

--
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.aspx/excel-programming/200708/1

 

[Joel]

If you right click the wb.name and add a watch you can see what value is
being read. What I think is happening is the file specialone.xls has never
been saved. Excel when a file has been create and not saved the name doesn't
include the xls extension. Therefore you wer comparing "specilalone" with
specialone.xls and not getting a match.

This can be fixed by using the instr function.

if instr(ucase(findname),ucase(wb.name)) > 1 then

You should determine why you have an unsaved workbook and determine if it is
appropriate that the file wasn't saved before you make any changes.

Good Morning Joel . . . I appreciate the help.

The following is a copy of the code I used. Nothing Happens. Any Ideas ?
When I step thru it, the "Const findname ="specialone.xls" is skipped over.
When it gets to the If Found = True Then, it skips over the MsgBox and then
finishes.

Sub NewExcelWithWorkbookSpecialOne()

Const findname = "specialone.XLS"

Found = False
For Each Wb In Workbooks
If UCase(Wb.Name) = UCase(findname) Then
Found = True
Exit For
End If
Next Wb

If Found = True Then
MsgBox ("found file " & findname & " Do not open the file again")
End If

End Sub

Thanks
Matt@Launchnet

Try this

Sub getworkbooks()

Const findname = "BOOK1.XLS"

Found = False
For Each Wb In Workbooks
If UCase(Wb.Name) = UCase(findname) Then
Found = True
Exit For
End If
Next Wb

If Found = True Then
MsgBox ("found file " & findname & " Do not open the file again")
End If

End Sub

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

[quoted text clipped - 65 lines]

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.aspx/excel-programming/200708/1