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 dir
 

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,

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

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,

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

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,

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

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,