getopenfilename <> this workbook

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ward376 - 24 May 2008 19:28 GMT

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

Thanks!
Cliff Edwards

Reply to this Message

Tim Zych - 24 May 2008 20:42 GMT

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

Signature

Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

> How can I keep users from selecting thisworkbook without using APIs or
> a custom file extension?
>
> Thanks!
> Cliff Edwards

Reply to this Message

ward376 - 24 May 2008 21:43 GMT

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

Reply to this Message

Tim Zych - 24 May 2008 22:25 GMT

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

Signature

Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

> This project validates/processes multiple files per run. I would like
> to prevent users from seeing or selecting thisworkbook in the file
[quoted text clipped - 3 lines]
> Thanks!
> Cliff Edwards

Reply to this Message

Tim Zych - 24 May 2008 22:37 GMT

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

Signature

Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

> Ok..this will work for zero or more files. It ignores ThisWorkbook, but it
> doesn't hide it from the file selection dialog.
[quoted text clipped - 25 lines]
>> Thanks!
>> Cliff Edwards

Reply to this Message

ward376 - 25 May 2008 00:57 GMT

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

Reply to this Message