To Andrew and GS

Thank you very much for your help!

GS, could you also post your file test function please? Thanks in advance!

"GS" wrote:

Hi Desmond,

Here's a function you can use for this.

Function bBookIsOpen(wbkName) As Boolean
' Tests if the named workbook is open
'
' Arguments: wbkName [In] The name of the workbook.
'
' Returns: True if the workbook is open

Const sSource As String = "bBookIsOpen()"

Dim x As Workbook
On Error Resume Next 'ignore the error if the workbook isn't open
Set x = Workbooks(wbkName) 'perform the test
bBookIsOpen = (Err = 0) 'our return value is the result

End Function

This function will return FALSE if the book is not open. We check for this
to open the file using the "Not" operator, as follows:

If Not bBookIsOpen("2005q4.xls") Then 'open your workbook

'put the rest of your code here since it will now be open. (This assumes the
workbook exists in the specified folder) If it's already open, your code
skips the "Then" part and continues the procedure.

I also have a function to test if a file exists, if you're interested, that
is used in a similar fashion.

Regards, GS


"Desmond" wrote:

I need to check if a workbook, named 2005q4, is open, and if not, open it
and filter the data, copy them to a worksheet in a different workbook (which
is open), and create a PivotTable. I have created my code assuming 2005q4 is
open, (may not be active). How can i open 2005q4 if it is not open, suppose
it is in the following path:

C:\Documents and Settings\ZZZ\My Documents\WorkRelatedExcel\[2005q4.xls]

Thank you in advance!