GetOpenFilename MultiSelect failure

[Greg Wilson]

I need to use Application.GetOpenFilename with MultiSelect set to True to
obtain a list of files as an array. The project is rather large and complex.
The appended code consistantly failes to return an array. It is just simple
test code.

When it is run in this project it returns "String" when it should return
"Variant". Other projects return "Variant" with the exact same code. MsgBox
UBound(arr) returns an error ("Type mismatch") but in other projects the
exact same code (copied and pasted) returns an integer.

I have commented out all Workbook_Open code plus all other code in standard
modules (UF code excepted) including all local and public var declarations;
then closed and reopend the project, in an attempt to source the problem.
This has no effect. I ensure that I select the exact same files in exactly
the same way. I have used Rob Bovey's Code Cleaner to no effect. I have
exported the code module to a floppy and imported to another project on
another computer and it works (in the context of the entire module). I have
rebooted to no effect.

Hoping someone has an insight or can provide a list of the usual suspects.
It's an important project and I need this to work. My brain is fogged from
late nights working on this. For what it's worth, the test code follows. Very
appreciative of your time and effort.

Greg

Sub Test()
Dim arr As Variant
arr = Application.GetOpenFilename("Excel files (*.xls), *.xls",
MultiSelect:=True)
MsgBox TypeName(arr)
End Sub

 

[chijanzen]

Greg Wilson:

try,

Dim FName As Variant
Dim wb As Variant
FName = Application.GetOpenFilename(MultiSelect:=True)
If VarType(FName) = vbBoolean Then 'No Select
Exit Sub
End If
For Each wb In FName
Workbooks.Open CStr(wb)
Next wb

 

[Greg Wilson]

Thanks for your reply.

I tried your code and it didn't work. I got "Type mismatch" error. It's
clearly refusing to return an array. It lets me select more than one file but
doesn't recognize it.

I emptied my Recycle Bin and renamed my .xlb files to no avail. I built a
copy and only copied over the code. I even tried changing the file extension
to .xla. Nothing helps. I also checked out some other files and found it not
working on some of them also. Can't figure out what the difference is. Maybe
references ??? Got me stumped.

Does anyone know if this is a bug ? I can't find any info on it.

Greg

 

[Tom Ogilvy]

Sub Test()
Dim arr As Variant
arr = Application.GetOpenFilename( _
"Excel files (*.xls), *.xls", _
MultiSelect:=True)
If Not IsArray(arr) Then
MsgBox "Nothing selected"
Else
MsgBox UBound(arr) - LBound(arr) + 1 _
& " items selected"
End If
End Sub

worked fine for me.

 

[Greg Wilson]

Tom, sorry for the late follow-up but I got booted off the computer.

After extensive investigation trying to narrow down where the problem
starts, what I found was that having a worksheet function contained in a
Conditional Format screws-up the MultiSelect feature. If you run your code
with a conditional format formula in A1:

"=$B$1 = 100"

then the MultiSelect macro works fine - i.e. I returned "3 items selected".
However, if the formula is:

"=$B$1>Today()"

then it won't work. I returned "Nothing selected". It apparently doesn't
matter what the worksheet function is. And setting calculation to manual
doesn't help. Hope there's a solution because this is a major set-back. I
wanted to get this project in good order for next week and have been working
late hours on it. Hope I'm just brain dead.

Regards,
Greg

 

[Greg Wilson]

The solution seems to be to simply activate another sheet when doing the
GetOpenFilename with Multiselect. You can keep the Conditional Formats with
worksheet functions. Your code works in this case. This must have bedevilled
many others.

Greg

 

[Greg Wilson]

Simply scrolling so that the conditional formatted cells are off-screen also
works.

Greg

 

[Jim Cone]

Greg,
I wonder if the problem is restricted to a particular XL version?
What version are you using?
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Greg Wilson" <[email protected]>
wrote in message
Simply scrolling so that the conditional formatted cells are off-screen also
works.
Greg

 

[Tom Ogilvy]

I couldn't reproduce that behavior in Excel 97.

 

[Greg Wilson]

Jim, thanks for responding.

I've tested this on 3 computers:
1. xl2000 (9.0.2720 SP3)
2. xl2000 (9.0.6926 SP3)
3. xl2000 (N/A)

I am using Tom's test code:

Sub Test()
Dim arr As Variant
arr = Application.GetOpenFilename( _
"Excel files (*.xls), *.xls", _
MultiSelect:=True)
If Not IsArray(arr) Then
MsgBox "Nothing selected"
Else
MsgBox UBound(arr) - LBound(arr) + 1 _
& " items selected"
End If
End Sub

I have found the following:
1. If you run the code from the VBE it fails if you have a cell or cells
with conditional formats (cf's) that include a worksheet function (wf).
2. If there is no wf then it succeeds when called from the VBE.
3. If you scroll so that the cell(s) are no longer in view then it succeeds
when called from the VBE inclusive of wf's.
4. If you call it through Tools>Macro>Macros then it succeeds under all
conditions.
5. If you have a helper cell that holds the desired wf (I used the Today()
function) and the cf references this cell instead then it succeeds. The
helper cell can be in the visible range. This would appear to be the solution
of choice.
6. If you activate another worksheet then it suceeds under all conditions.
7. On the first of the computers listed it fails when the filter is
extended to include *.doc. However, this was tested from within my project
(lots of code including event code and public vars).
8. In contrast, on the second computer listed it succeeds when the filter
is extended to include *.doc.
9. I get inconsistant results when in my project - i.e. it fails after a
while even if NOT run through the VBE. However, the macro is fired through a
right-click event and not through Tools>Macro>Macros. The project is complex
and in the process of development and I'm still evaluating the situation.
Hoever, it does not appear to be caused by an simple error state. The problem
appears to be much more subtle.

Your post seems to imply that you don't corroborate the phenomenon. Is this
so?

Thanks again for responding.

Regards,
Greg

 

[Tom Ogilvy]

Based on your latest post specifying conditions, I can duplicate it if I run
it from the VBE. (xl97). It says nothing is selected when there is a
selection.

 

[Jim Cone]

Greg,

Ran Tom's macro on XL 97, 2000, 2002.
Used steps 1 3, 4 on all.
Also used step 2 on XL 97.
All of above on WindowsXP

My results agree with yours.
Failure is defined as the Msgbox showing "Nothing Selected"

Conclusions/opinions:
I think it's a "bug"
Change your code or scroll the sheet before calling the file open dialog.
MS won't fix it, but if they did issue a fix now, the problem
would still be there in the next release.

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Greg Wilson" <[email protected]>
wrote in message

Jim, thanks for responding.
I've tested this on 3 computers:
1. xl2000 (9.0.2720 SP3)
2. xl2000 (9.0.6926 SP3)
3. xl2000 (N/A)
I am using Tom's test code:
Sub Test()
Dim arr As Variant
arr = Application.GetOpenFilename( _
"Excel files (*.xls), *.xls", _
MultiSelect:=True)
If Not IsArray(arr) Then
MsgBox "Nothing selected"
Else
MsgBox UBound(arr) - LBound(arr) + 1 _
& " items selected"
End If
End Sub

I have found the following:
1. If you run the code from the VBE it fails if you have a cell or cells
with conditional formats (cf's) that include a worksheet function (wf).
2. If there is no wf then it succeeds when called from the VBE.
3. If you scroll so that the cell(s) are no longer in view then it succeeds
when called from the VBE inclusive of wf's.
4. If you call it through Tools>Macro>Macros then it succeeds under all
conditions.
5. If you have a helper cell that holds the desired wf (I used the Today()
function) and the cf references this cell instead then it succeeds. The
helper cell can be in the visible range. This would appear to be the solution
of choice.
6. If you activate another worksheet then it suceeds under all conditions.
7. On the first of the computers listed it fails when the filter is
extended to include *.doc. However, this was tested from within my project
(lots of code including event code and public vars).
8. In contrast, on the second computer listed it succeeds when the filter
is extended to include *.doc.
9. I get inconsistant results when in my project - i.e. it fails after a
while even if NOT run through the VBE. However, the macro is fired through a
right-click event and not through Tools>Macro>Macros. The project is complex
and in the process of development and I'm still evaluating the situation.
Hoever, it does not appear to be caused by an simple error state. The problem
appears to be much more subtle.

Your post seems to imply that you don't corroborate the phenomenon. Is this
so?
Thanks again for responding.
Regards,
Greg

 

[Greg Wilson]

Thanks Tom for your help. After developing the project further with more
evaluation, I supect I'll go the helper cell route. I have my project working
OK for now but it's been proven not reliable. Thanks to you both for taking
the time. I'm sure this will be of value to others.

Greg

 

[Greg Wilson]

Thanks Jim for taking the time. As I told Tom:
After developing the project further with more evaluation, I supect I'll go
the helper cell route. I have my project working OK for now but it's been
proven not reliable. Thanks to you both for taking the time. I'm sure this
will be of value to others.

Best Regards,
Greg