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
>

Hi Bob . . . I know its long, but I think it is needed to help tell you what
I have build and how it basically works.

I understand the questions in your mind. First, I want to point out that I
have known Excel quite good for many years and have taught Excel to many
people. I simply couldn't master VB Code. I am a committed Microsoft user
and I always recommend Microsoft to all people.

The reason that I open a new instance of Excel each time in "My Menu"
application is because it is very beneficial to my users. So, I have
developed a complete Menu System where "ANY" user can open almost any kind of
file or link out to any website, DIRECTLY FROM THE MAIN MENU PAGE. The
entire Menu system works much better than favorites and is far more extensive.
Each opening of an Excel file, from the menu, is opened in a new instance of
Excel.

One click of a macro and the user can list all of the Excel files that they
use on a regular basis. After the users list of files has been entered, the
user at anytime thereafter can click the link on the Main Menu which then
displays the list of Excel Files which can be opened directly. The user then
selects the file he wants to open and clicks a button at the tope of the
sheet which opens any selected file. Here is where opening files in new
instances becomes beneficial to the users, as many times they want to display
Excel file # 1 and Excel file # 2 at the same time, working data back and
forth between files without having to click different tabs to display one
file then click another tab to display the second file and continiously
switching from tab to tab. If the user can open two instances of file #1,
they could make an entry on one file - Save it - close it and then later
after doing other work, they see the same file which I will call File #2 and
they decide to save it, which cancels out their entries from the 1st file #1
which was previously saved.

From "My Menu" ( that stays open all the time ), we can go . . .

Internet Shopping, Check the News & Sports, Open Word Files in the same
manner as Excel, Do Checkbooks, Open eMails, Search Engines, Go to any
website, Go to "My Menu" Help, Go To Computer Training, Computer Help on the
Internet, To Do Lists, Travel Tickets & Hotel Reservations and THE LIST GOES
ON AND ON. Instructions on the usage of the all Menu Macros & Links is done
along side with comments.

Hope this is helpful.

One more thing. A good number of us use 2 monitors due to the need of
working between files of various types including internet files. Two of my
users has to copy certain data from an Internet Site to different Excel
spreadsheets. They both use 2 monitors.

QUESTION ON ANSWER:

Couldn't the blank Excel program be closed automatically if it is determined
that the requested file is already open? Naturally, it would be better if it
didn't have to open it at all when the requested file is already open. Hope
this is clear.

Glad to explain more if needed.

Matt

Bob Phillips wrote:
>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.
>
>> Hi Bob . . .
>>
>[quoted text clipped - 24 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

Matt,

In that casr it should be as simple as

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 selected cell 1 to 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

'Check if the file is already open, do nothing if so
If Not IsFileOpen(ActiveCell.Value) Then

'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)
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 via OfficeKB.com" <u20911@uwe> wrote in message
news:76c14066411d3@uwe...
> Hi Bob . . . I know its long, but I think it is needed to help tell you
> what
> I have build and how it basically works.
>
> I understand the questions in your mind. First, I want to point out that
> I
> have known Excel quite good for many years and have taught Excel to many
> people. I simply couldn't master VB Code. I am a committed Microsoft user
> and I always recommend Microsoft to all people.
>
> The reason that I open a new instance of Excel each time in "My Menu"
> application is because it is very beneficial to my users. So, I have
> developed a complete Menu System where "ANY" user can open almost any kind
> of
> file or link out to any website, DIRECTLY FROM THE MAIN MENU PAGE. The
> entire Menu system works much better than favorites and is far more
> extensive.
> Each opening of an Excel file, from the menu, is opened in a new instance
> of
> Excel.
>
> One click of a macro and the user can list all of the Excel files that
> they
> use on a regular basis. After the users list of files has been entered,
> the
> user at anytime thereafter can click the link on the Main Menu which then
> displays the list of Excel Files which can be opened directly. The user
> then
> selects the file he wants to open and clicks a button at the tope of the
> sheet which opens any selected file. Here is where opening files in new
> instances becomes beneficial to the users, as many times they want to
> display
> Excel file # 1 and Excel file # 2 at the same time, working data back and
> forth between files without having to click different tabs to display one
> file then click another tab to display the second file and continiously
> switching from tab to tab. If the user can open two instances of file #1,
> they could make an entry on one file - Save it - close it and then later
> after doing other work, they see the same file which I will call File #2
> and
> they decide to save it, which cancels out their entries from the 1st file
> #1
> which was previously saved.
>
> From "My Menu" ( that stays open all the time ), we can go . . .
>
> Internet Shopping, Check the News & Sports, Open Word Files in the same
> manner as Excel, Do Checkbooks, Open eMails, Search Engines, Go to any
> website, Go to "My Menu" Help, Go To Computer Training, Computer Help on
> the
> Internet, To Do Lists, Travel Tickets & Hotel Reservations and THE LIST
> GOES
> ON AND ON. Instructions on the usage of the all Menu Macros & Links is
> done
> along side with comments.
>
> Hope this is helpful.
>
> One more thing. A good number of us use 2 monitors due to the need of
> working between files of various types including internet files. Two of
> my
> users has to copy certain data from an Internet Site to different Excel
> spreadsheets. They both use 2 monitors.
>
> QUESTION ON ANSWER:
>
> Couldn't the blank Excel program be closed automatically if it is
> determined
> that the requested file is already open? Naturally, it would be better if
> it
> didn't have to open it at all when the requested file is already open.
> Hope
> this is clear.
>
> Glad to explain more if needed.
>
> Matt
>
> Bob Phillips wrote:
>>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.
>>
>>> Hi Bob . . .
>>>
>>[quoted text clipped - 24 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 again Bob . . .

It works beautifully. Thank You Much.

I do have one more macro to be modifed in the same way you did for Excel.
The following one is for Word. It is to work in the same way as Excel does
when opening a file. Hopefully, since you know what you did in Excel, the
changes for Word should be alot easier.

If you will be kind enough . . . please help me. I think this is the last
code I need help with for now.

THE FOLLOWING IS MY "WORD" CODE with copying your changes into my code as far
as I could go. I've noted in the code what I've done.




Sub NewWordWithDocument()
Dim oWordApp As Object
Dim oWordDoc As Object
Set oWordApp = CreateObject("Word.Application")

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


'I THINK THIS IS THE CODE THAT YOU USED FOR EXCEL.
'Check if the file is already open, do nothing if so
If Not IsFileOpen(ActiveCell.Value) Then

'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)
Else

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

End Sub





'THE FOLLOWING IS THE BALANCE OF THE CODE I USE TO OPEN WORD DOCUMENT.
'NATURALLY, WE CAN'T HAVE 2 . . . End Sub's
'IN REVIEWING YOUR CODE (EXCEL) AND THE CHANGES ADDED TO MY ORIGINAL CODE, I
'THINK THAT SOME OF THE FOLLOWING CODE HAS TO BE INCORPORATED INTO ABOVE
'PLUS, I AM SURE I NEED THE FUNCTION. (AS IS . . . OR DOES IT HAVE TO BE
CHANGED ?

'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 WORD.
Set oWordApp = CreateObject("Word.Application")

'THIS LINE OF CODE MAKES THE NEW INSTANCE OF WORD VISIBLE.
oWordApp.Visible = True

Set oWordDoc = oWordApp.Documents.Open(ActiveCell.Text)

End Sub




'HERE IS YOUR FUNCTION CODE, WHICH I DON'T UNDERSTAND AT ALL.

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


Bob Phillips wrote:
>Matt,
>
>In that casr it should be as simple as
>
>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 selected cell 1 to 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
>
> 'Check if the file is already open, do nothing if so
> If Not IsFileOpen(ActiveCell.Value) Then
>
> '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)
> 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
>
>> Hi Bob . . . I know its long, but I think it is needed to help tell you
>> what
>[quoted text clipped - 94 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