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!

Figured it out:

Workbooks.Open ("C:\Documents and Settings\ZZZ\My
Documents\WorkRelatedExcel\2005q4.xls")


"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!

i intend to check if 2005q4 is open, if not, then open it.

Problems with the following code: it stops at the for loop; when 2005q4
opens, it still prompts a message asking me if i want to update links,
displayalerts set to false does not work in this kind of prompted message?

i = Workbooks.Count
For Each wk In Workbooks(i)
If wk.Name < "2005q4" Then
On Error Resume Next
Application.DisplayAlerts = False
Workbooks.Open ("C:\Documents and Settings\Mei FENG\My
Documents\Work Related Excel\2005q4.xls")
On Error GoTo 0
End If
Next



"Desmond" wrote:

Figured it out:

Workbooks.Open ("C:\Documents and Settings\ZZZ\My
Documents\WorkRelatedExcel\2005q4.xls")


"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!

The optional second parameter of Workbooks.Open is "UpdateLinks", so
Workbooks.Open (filename, False)
will open it without prompting and without updating. Change False to
True
if you do want to update links.


Desmond wrote:
i intend to check if 2005q4 is open, if not, then open it.

Problems with the following code: it stops at the for loop; when 2005q4
opens, it still prompts a message asking me if i want to update links,
displayalerts set to false does not work in this kind of prompted message?

i = Workbooks.Count
For Each wk In Workbooks(i)
If wk.Name < "2005q4" Then
On Error Resume Next
Application.DisplayAlerts = False
Workbooks.Open ("C:\Documents and Settings\Mei FENG\My
Documents\Work Related Excel\2005q4.xls")
On Error GoTo 0
End If
Next



"Desmond" wrote:

Figured it out:

Workbooks.Open ("C:\Documents and Settings\ZZZ\My
Documents\WorkRelatedExcel\2005q4.xls")


"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!

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!

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!

The following code says syntax error:

Workbooks.Open ("C:\Documents and Settings\Mei FENG\My Documents\Work
Related Excel\2005q4.xls", False)

"Andrew Taylor" wrote:

The optional second parameter of Workbooks.Open is "UpdateLinks", so
Workbooks.Open (filename, False)
will open it without prompting and without updating. Change False to
True
if you do want to update links.


Desmond wrote:
i intend to check if 2005q4 is open, if not, then open it.

Problems with the following code: it stops at the for loop; when 2005q4
opens, it still prompts a message asking me if i want to update links,
displayalerts set to false does not work in this kind of prompted message?

i = Workbooks.Count
For Each wk In Workbooks(i)
If wk.Name < "2005q4" Then
On Error Resume Next
Application.DisplayAlerts = False
Workbooks.Open ("C:\Documents and Settings\Mei FENG\My
Documents\Work Related Excel\2005q4.xls")
On Error GoTo 0
End If
Next



"Desmond" wrote:

Figured it out:

Workbooks.Open ("C:\Documents and Settings\ZZZ\My
Documents\WorkRelatedExcel\2005q4.xls")


"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!

Remove the parentheses.

--
Regards,
Tom Ogilvy


"Desmond" wrote in message
...
The following code says syntax error:

Workbooks.Open ("C:\Documents and Settings\Mei FENG\My Documents\Work
Related Excel\2005q4.xls", False)

"Andrew Taylor" wrote:

The optional second parameter of Workbooks.Open is "UpdateLinks", so
Workbooks.Open (filename, False)
will open it without prompting and without updating. Change False to
True
if you do want to update links.


Desmond wrote:
i intend to check if 2005q4 is open, if not, then open it.

Problems with the following code: it stops at the for loop; when
2005q4
opens, it still prompts a message asking me if i want to update links,
displayalerts set to false does not work in this kind of prompted
message?

i = Workbooks.Count
For Each wk In Workbooks(i)
If wk.Name < "2005q4" Then
On Error Resume Next
Application.DisplayAlerts = False
Workbooks.Open ("C:\Documents and Settings\Mei FENG\My
Documents\Work Related Excel\2005q4.xls")
On Error GoTo 0
End If
Next



"Desmond" wrote:

Figured it out:

Workbooks.Open ("C:\Documents and Settings\ZZZ\My
Documents\WorkRelatedExcel\2005q4.xls")


"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!

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!