Leung,

Thanks much for your assistance. I tried the function, but couldn't quite
get that to work. Possibly because I wasn't calling the function correctly.
I ended up going w/ this type of sub and seemed to work well.
I really appreciate your help.

Dim Book As Workbook
On Error Resume Next
Set Book = Workbooks("QA_Accounts_Overview2007.xls")
On Error GoTo 0
If Not Book Is Nothing Then
Else
ChDir _
"Q:\SYLVIA_WORKLOAD_FOLDER\CHARTS\LABOR CHARTS\2007 LABOR
CHARTS\LONG BEACH_JQA"
Workbooks.Open Filename:= _
"Q:\SYLVIA_WORKLOAD_FOLDER\CHARTS\LABOR CHARTS\2007 LABOR
CHARTS\LONG BEACH_JQA\QA_Accounts_Overview2007.xls" _
, UpdateLinks:=0
End If


"Leung" wrote:


Thanks

Let me know if it is helpful or not.

If yes, would you please click "yes" at the bottom of this message?

thanks

Leung
HK

"Brent E" wrote:

I'll give that a go. Thanks, Leung

"Leung" wrote:

Hi

I ususally use this function to see if the Excel is opened:


Function IsOpenWorkbook(strWorkbookName As String, Optional bFullname As
Boolean) As Boolean

Dim wb As Workbook
Dim strName As String

IsOpenWorkbook = False
For Each wb In Workbooks
If bFullname = False Then
strName = wb.Name
Else
strName = wb.FullName
End If

If (StrComp(strName, strWorkbookName, vbTextCompare) = 0) Then
IsOpenWorkbook = True

Exit Function
End If
Next
End Function


just tried it in your immediate pane to see if it can return true when open,
false when close.

IsOpenWorkbook("QA_Accounts_Overview2007.xls", "Q:\BUDGETS PROGRAM LEVEL\QA
DIRECT LABOR\LONG BEACH\LONG BEACH_JQA\QA_Accounts_Overview2007.xls")

happy coding.




"Brent E" wrote:

Good day,

I need assistance w/ a sub that will test to see if a file is open, but the
file is in a different directory.

I found a similar issue in the online post database, but seems to only work
for files in the same directory. I need assistance to customise this sub for
my directory path. Please advise

My file is located at:
Q:\BUDGETS PROGRAM LEVEL\QA DIRECT LABOR\LONG BEACH\LONG
BEACH_JQA\QA_Accounts_Overview2007.xls

I've tried this following code, but regardless if file is open or closed, I
get a message box that the file is not open.

Dim Book As Workbook
On Error Resume Next
Set Book = Workbooks("Q:\BUDGETS PROGRAM LEVEL\QA DIRECT LABOR\LONG
BEACH\LONG BEACH_JQA\QA_Accounts_Overview2007.xls")
On Error GoTo 0
If Not Book Is Nothing Then
MsgBox "MyBook.xls is already open in excel"
Else
MsgBox "MyBook.xls is not open"
End If

Thanks,