VBA Assistance needed to test for open file in a different direct
 

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,

VBA Assistance needed to test for open file in a different direct
 

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

VBA Assistance needed to test for open file in a different direct
 

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