getopenfilename <> this workbook

[ward376]

How can I keep users from selecting thisworkbook without using APIs or
a custom file extension?

Thanks!
Cliff Edwards

 

[Tim Zych]

Function GetXlFilename()
Dim varFName As Variant
Do
varFName = Application.GetOpenFilename _
(FileFilter:=("Excel Files,*.xls"), MultiSelect:=False)
If TypeName(varFName) = "Boolean" Then
' Clicked cancel
varFName = ""
Exit Do
End If
If varFName = ThisWorkbook.FullName Then
MsgBox "Can't select this workbook. Please select another."
Else
Exit Do
End If
Loop
If varFName <> "" Then
MsgBox "Workbook selected was: " & varFName
End If
End Function

 

[ward376]

This project validates/processes multiple files per run. I would like
to prevent users from seeing or selecting thisworkbook in the file
open dialog. If I can't keep them from selecting it, I need to be able
to continue to the next file without interaction.

Thanks!
Cliff Edwards

 

[Tim Zych]

Ok..this will work for zero or more files. It ignores ThisWorkbook, but it
doesn't hide it from the file selection dialog.

Sub XLFiles()
Dim varFName As Variant, i As Integer
Do
varFName = Application.GetOpenFilename _
(FileFilter:=("Excel Files,*.xls"), MultiSelect:=True)
If TypeName(varFName) = "Boolean" Or TypeName(varFName) =
"Variant()" Then
Exit Do
End If
Loop
If TypeName(varFName) = "Variant()" Then
For i = LBound(varFName) To UBound(varFName)
If varFName(i) <> ThisWorkbook.FullName Then
Debug.Print "Process " & varFName(i)
End If
Next
End If
End Sub

 

[Tim Zych]

Or better:

Sub XLFiles()
Dim varFName As Variant, i As Integer
varFName = Application.GetOpenFilename _
(FileFilter:=("Excel Files,*.xls"), MultiSelect:=True)
If TypeName(varFName) = "Variant()" Then
For i = LBound(varFName) To UBound(varFName)
If varFName(i) <> ThisWorkbook.FullName Then
Debug.Print "Process " & varFName(i)
End If
Next
End If
End Sub

 

[ward376]

Now why couldn't I think of that?

Added a little in case they grab a file with the same name in a
different directory:

Sub XLFiles()
Dim varFName As Variant, i As Integer, strFileName As String

varFName = Application.GetOpenFilename _
(FileFilter:=("Excel Files,*.xls"), MultiSelect:=True)

If TypeName(varFName) = "Variant()" Then
For i = LBound(varFName) To UBound(varFName)
strFileName = Mid(varFName(i), 1 + InStrRev(varFName(i), "\"))
If strFileName <> ThisWorkbook.Name Then
Debug.Print "Process " & varFName(i)
End If
Next
End If
End Sub

Thanks!
Cliff Edwards