GetOpenFilename returns a string rather than an array
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.
-----Original Message-----
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
"Alan" wrote in
message ...
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.
-----Original Message-----
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'
--
Regards
Frank Kabel
Frankfurt, Germany
Alan wrote:
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
-----Original Message-----
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
--
Regards
Frank Kabel
Frankfurt, Germany
Alan wrote:
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.
.
.
.
|