Hi Desmond,

Thanks for the feedback, ..I'm glad to help!
Here's the function you asked for. I also included one for checking a valid
path. You can place them in any standard module. I keep them in
"MFunctions.bas" and drop it into my projects as a standard component.

Good luck! Code follows:

Function bFileExists(fileName) As Boolean
' Tests if the filename exists
'
' Arguments: fileName [In] The fullname of the file.
'
' Returns: True if the file (filename) exists

Const sSource As String = "bFileExists()"

On Error Resume Next 'ignore the error if the filename
doesn't exist
bFileExists = (Dir$(fileName) < "") 'our return value is the test result

End Function

Function bPathExists(sPath As String) As Boolean
' Tests if the named path to a directory (folder) exists
' "\nul" appended to the path makes it work with empty folders
'
' Arguments: sPath [In] The fullpath to the directory (folder).
'
' Returns: True if the path exists

Const sSource As String = "bPathExists()"

On Error Resume Next 'if the path doesn't exist
bPathExists = (Dir$(sPath & "\nul") < "") 'our return value is the
test result

End Function

Regards, GS

"Desmond" wrote:

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!