GetOpenFilename returns a string rather than an array

[Alan]

I invoke GetOpenFilename() with the multi file select
option set to True. It should return an array, but I
receive a single string. The code is:

WorkBookL = Application.GetOpenFilename _
("Excel Files (*.xls), *.xls", , _
"Add Form to Report", , True)

It was returning an array earlier and working properly.

I use the file selector elsewhere in my application.
Could that cause a problem?

I also noticed the "ChDrive" and "ChDir" appear to work
on the first invocation for GetOpenFilename, but later
GetOpenFilename invocations will pickup where the
previous GetOpenFilename stopped. Is there a way to
specify the initial folder for each invocation of
GetOpenFilename?

thanks.

 

[Frank Kabel]

Hi Alan
the following works for me

Sub foo()
Dim workbookl
Dim i
workbookl = Application.GetOpenFilename _
("Excel Files (*.xls), *.xls", , _
"Add Form to Report", , True)
For i = 1 To UBound(workbookl)
MsgBox workbookl(i)
Next
End Sub

 

[Alan]

Thanks for the response.

Unfortunately, this fails on mine. workbookl has a type
of String and fails on the UBound().

I think there may be a bug in GetOpenFilename(). Even if
you set Multi-Select to True, I receive a String value
rather than an array. You can select multiple files in
the file selector, but a single file is returned as a
string.

I'm opening / closing files, updating excel names, etc.
Something else I'm doing may be causing the issue.

As a workaround, I check the GetOpenFilename() result
type, if it is a String then I convert it to an array.
Tacky, but it appears to work:

Dim TmpWorkbookL(0) As String
Dim WorkBookL as Variant

WorkBookL = Application.GetOpenFilename _
("Excel Files (*.xls), *.xls", , _
"Add Form to Report", , True)

If TypeName(WorkBookL) = "String" Then
TmpWorkbookL(0) = WorkBookL
WorkBookL = TmpWorkbookL
End If

 

[Frank Kabel]

Hi Alan
just define the variable as varinat. It should work either for a single
selection or a multiple selection (at least it does for me). Now need
for a 'string check'

 

[Alan]

Thanks for the suggestion.

I've tried this and it doesn't work at this time. I have
Workbookl defined as a variant in another function and
passed as an argument.

I've been using GetOpenFilename() successfully for
several months, then it stopped working for multi file
selection a few days ago. I don't know why. I've been
adding other things to my VBA, I think this is a side
effect from something else.

I'll use the "tacky" workaround until I can track down
the source of the problem.

thanks.

 

[Lew]

I have this same problem. Spreadsheets with macros that used to work
no longer work. I have tried different machines (win2k, winxp, and
even Mac Excel X) all now have this problem. The
Application.GetOpenFilename() is configured for multiselect and now
all of sudden only returns a single file in a string. I need the
multiple file selection function!

Anyone have a clue as to what broke this class?

Lew

 

[Lew]

-- More on this issue ---

GetOpenFilename() appears to work on the PC if and only if you call it
with Multiselect set to "false" first.

If I comment out the lines in the sniplet bleow to do the
singlefileselect first, the multiselect quits working.

This used to work right. Does anyone know what the problem is??

Lew
--- sniplet --

Sub TestIt()
Dim SingleFileSelect As String
Dim MultiFileSelect As Variant
Dim i As Integer

SingleFileSelect = Application.GetOpenFilename(MultiSelect:=False)
MsgBox "No multi select " & SingleFileSelect

MultiFileSelect = Application.GetOpenFilename(MultiSelect:=True)

If IsArray(MultiFileSelect) Then
For i = LBound(MultiFileSelect) To UBound(MultiFileSelect)
MsgBox "Multi select " & MultiFileSelect(i)
Next i
Else
MsgBox "User pressed cancel"
End If
End Sub

 

[Geoff Kelly]

This is weird. I copied your code into a macro and saw similar behaviour -
with the singlefileselect lines commented out, I got the user cancel
message. Re-inserting them let multiselect work fine.
Now for the really weird part - it then continued to work fine after
commenting them out again, even after rebooting my PC, even re-creating the
macro in another workbook!
Following code works every time now (ie f is an array if one or more files
selected)

Sub test1()
Dim f As Variant
f = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(f) Then
MsgBox "count=" & Format(UBound(f)) & ", f(1)=" & f(1)
Else
MsgBox "not array, " & f
End If
End Sub

Geoff

 

[Claritech]

I was having the exact same problem and was able to track it down t
Conditional Formatting with a function in the "Formula Is" field.
Don't ask me why, but if you try to do a Multi-select and one of th
cells in any open workbook has Conditional Formatting using th
"Formula Is" option and then a function in the formula cell, it fail
to return the array of filenames. Clearly this is a bug and it may als
be caused by something else equally as obscure.

Try it and let me know what you come up with.

The next trick is to try to find all of the conditional formatting i
my worksheet!

Dan Frederick
Claritech Solutions

*This is weird. I copied your code into a macro and saw simila
behaviour -
with the singlefileselect lines commented out, I got the user cancel
message. Re-inserting them let multiselect work fine.
Now for the really weird part - it then continued to work fine after
commenting them out again, even after rebooting my PC, eve
re-creating the
macro in another workbook!
Following code works every time now (ie f is an array if one or mor
files
selected)

Sub test1()
Dim f As Variant
f = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(f) Then
MsgBox "count=" & Format(UBound(f)) & ", f(1)=" & f(1)
Else
MsgBox "not array, " & f
End If
End Sub

Geoff



-
Claritec

 

[Dan Frederick]

I lost the original message for this, but I've found the
source of this problem.

It's related to Conditional Formats with a function used
in the "Formula Is" selection.

The kicker is that any cell in any open worksheet can have
it set this way and the getopenfilename will break.

To find your conditional formats, select
Edit/GoTo/Special/Conditional Formats.

The workaround is to use a separate cell to evaluate the
function and then set your Formula Is to that cell. It's
a pain, but it works.

Cheers,

Dan Frederick
Claritech Solutions Corp.