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,

When you're using the workbooks collection, you don't specify the drive and
path:

Set Book = Workbooks("QA_Accounts_Overview2007.xls")

should get you closer.



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,

--

Dave Peterson

Sub opentest()
For Each wb In Workbooks
If wb.Name = "QA_Accounts_Overview2007" Then
MsgBox ("it is open")
Exit Sub
End If
Next
MsgBox ("it is not open")
End Sub

--
Gary''s Student - gsnu200718