Opening Excel from Word, first detecting if workbook already opened

[gtaborda]

Hi

I've looked in the forum, but I was unable to find an answer.

This is what I am trying to achieve:

From a table in Word, instead of opening a Form to select items, I prefer to open a database in Excel so user ...

1. rightclicks on Word (done)
2. selects new Menu Option called "Open Excel DB" (done)
3. Excel opens
4.Users selects choice (1000's to choose from)
5. Data from the Excel row selected is transfered to Word table
6.Excel hides (doesn't close because it gets used again every few seconds/minutes by user)

Point 1 and 2 is already done.
However opening Excel from Word I have the problem that it opens several instances of Excel everytime I right click+choose "Open Excel DB"...

Anyone could advise on..

1. check with VBA first if Excel file is opened, if so, make it visible OR open Excel file if not opened yet.

2. Easiest way to transfer data from selected cells in excel to Word table cells ?

Your help is most appreciated

 

[PHV]

Have a look at the GetObject function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gtaborda]

thanks PHV

I have checked getObject function, however doesn't seem to work (probably I've done something wrong)


strName = "MATRIX.XLS"
strPath = "C:\TUTORIALS\MATRIX\" '<<== CHANGE TO SUIT

Set MiXL = GetObject(strPath & strName)
If Err.Number <> 0 Then
' file not opened...do it
Set MiXL = CreateObject("Excel.Application")
xlApp.Workbooks.Open (strPath & strName), ReadOnly:=True
Else
' file already opened, don't open

End If

 

[gtaborda]

sorry, I pressed the Submit button too soon...

The code mentioned on the above post, regardless if the file is opened or not, it opens it again...

the If err.number<>0 then doesn't seem to work

any ideas ?

 

[PHV]

Have a look to the On Error instruction

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886