How to check workbook is already opened before passing the workbook obj to a subroutine in Word

[Bon]

Hello all

I want to pass a workbook object or a filepath string from a module in
MS Access 2000 to a subroutine in MS Word 2000. Before passing the
object or filepath, I want to check the workbook is already opened or
not. How can I do that?

IF the workbook is already opened
MS Word subroutine will extract excel data from the opened workbook
ELSE
Open the excel workbook
MS Word subroutine extract excel data
End IF

If the workbook has been opened, the workbook is opened in the MS Word
subroutine would be better? Or the workbook is opened in MS Access
would be better?

My whole workflow
Firstly, MS Access calls a module with Workbook obj/filepath as
parameter in MS Word
Secondly, MS Word will extract data from MS Excel and then paste it
under a bookmark in MS Word.

Could anyone give me some advices? Thank you

Cheers
Bon

 

[Bob Phillips]

This should get you started

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

Sub test()
If Not IsFileOpen("C:\MyTest\volker2.xls") Then
Workbooks.Open "C:\MyTest\volker2.xls"
End If
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[NickHK]

Bon,
Presumably if you have a reference to a workbook (your workbook object), you
must have opened it in order to obtain the reference. You can't have a
reference to a file that is not open.
Otherwise you would only have a file name/path.

Why not pass a variant and check if its an object (Excel.Workbook) or a
string and act accordingly

NickHK