File already open

[VBAva]

Morning

to prevent errors in my program i need to use my own procedure to check if a file selected to open is already open (the normal excel one causes problems).

i have the code below, but i will not know the name of the file.
the file name comes from GetOpenFileName so it is the complete path.
the Sub below does not work if the whole path is used instead of SC3001_s.txt.
so is there a way to check the full file path against the open files or else get just the file name to check against the open Workbooks.

Sub checkOpen()
Dim wksht As Workbook
For Each wksht In Application.Workbooks
If wksht.Name = "SC3001_s.TXT" Then
MsgBox "sheet already open"
End If
Next
End Sub

i hope this makes sence
thank you

 

[Bowers74]

Try using wksht.FullName to get the path and filename.

;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[VBAva]

Thanks Mike

You must have great fun all day helping clueless "programmers" like me,
it is much appreciated

 

[Bowers74]

I try to help where I can. I'm glad I could help. The recognition is enough for me, and I'm happy with a Thank You!





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[VBAva]

Just incase anyone was looking for the answer to this, i though i would post the code that i use

.....
Call basTest.CheckOpen(FileToOpen)
.....

Sub CheckOpen(ByVal FileName As String)
Dim wksht As Workbook
FileIsOpen = False
For Each wksht In Application.Workbooks
If wksht.FullName = FileName Then
MsgBox "The file selected (" & FileName & &quot is already open." & Chr(13) & "Either close the file that is open or select a different file.", vbExclamation
FileIsOpen = True
End If
Next
End Sub