GetOpenFileName - no array?

[Guest]

A very weird problem...

I've got a few fairly busy workbooks, lots of sheets, lots of code. I added
the following code:

sub temp
dim x as variant
x=application.getopenfilename(,,,,true)
stop
end sub

When I go into immediate mode, x has a single value, one of the files
selected. If I try to look at x(1) I get an error as x does not seem to be
an array.

I spent over an hour trying the following:
- got rid of all of the code, except for my temp sub (still failed)
- got rid of all names in the workbook (still failed)
- got rid of all but one sheet (aha!)
- etc.

Eventually I found that if I deleted one sheet in particular, the code
returned an array. Then I re-loaded, left the sheet in, but deleted all rows
and columns. It worked. I then moved, just moved, another sheet -- it
failed. I moved that other sheet around, and eventually it succeeded and
returned an array.

Can anyone give me any clues as to what's going on?
I'm running Excel 2003(11.8134.8132) SP2

 

[Jim Rech]

Can anyone give me any clues as to what's going on?

You're pulling our collective legs?<g>

Might be fun to figure out what on the sheet in question is behind this.
Not in the hopes of learning any great lesson though. I've seen workbooks
that do weird things that were traced to the presence on one particular,
perfectly valid formula. Delete it and recreate it and all was well. What
do you do with that information? This could fall into that category, but
maybe not.

 

[Chip Pearson]

The following works fine for me in Excel97 through Excel2007.

Sub AAA()
Dim FNames As Variant
Dim N As Long
FNames = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls),*.xls", MultiSelect:=True)
If IsArray(FNames) = True Then
For N = LBound(FNames) To UBound(FNames)
Debug.Print FNames(N)
Next N
Else
Debug.Print "No file selected"
End If
End Sub

x=application.getopenfilename(,,,,true)

You'll find life much simpler if you use named parameters rather than
positional parameters. It makes things MUCH easier to debug.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Guest]

Jim,

I'm ashamed to say that I had kind of hit a road block. I actually tried
this on 3 different, but related workbooks -- and rebuilding it seemed really
daunting. That's what I was planning on doing when I started deleting
worksheets and copying others.

I got in touch with MS. It took me over 1 hour to get to someone that knew
what VBA was. However, when I finally got to someone knowledable they were
very helpful. I think they ran into the same problem with my workbook and
eventually suggested changing the code to:


Sub temp()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.AllowMultiSelect = True
fd.Show
Set x = fd.SelectedItems
Stop
End Sub

which at least works in my test workbook on my home computer. I'm guessing
this will work in my production file as well. Anyway I hope it does, I
really don't want to reassemble this stuff.

Thanks for taking a look!

 

[Guest]

Chip,

Thanks for the suggestion, but it didn't seem to be a problem with not
getting an array when I wasn't supposed to get one. If you're interested I
left more details in my response to Jim. I suppose it was just one of those
things.

By the way, thanks for your suggestion about using named parameters, that
does make it simpler.

 

[Jim Rech]

Hi Art-

The MS guy took a very practical approach and gave you a workaround. It's
practical because it addressed the immediate problem of getting the result
you want and quickly. But it doesn't address why you had a problem that
never 'should' have occurred in the first place. That would leave me a
little uncomfortable because, when a workbook starts doing things it
shouldn't, I really can't trust it anymore. All I can say is, keep your
eyes pealed<g>.

 

[Peter T]

Greg Wilson went into some detail about what turned out to be "real" bug
with GetOpenFileName in this thread (see his last post)

http://tinyurl.com/qbr2n

and also a further follow-up here

http://tinyurl.com/ywndxd

Perhaps the sheet, which the OP says if deleted solves the problem, is
similar to Greg's as is the way the function is called.

Briefly, IsFormula type CF's that also include a worksheet function
'intermittently' cause problems (not always recreatable). One workaround,
call the function from a toolbar button.

FWIW, similar known problem with InputBox type:=8

Regards,
Peter T

 

[Guest]

Fascinating! I took a look at the threads you referred me to. I went back
to my flawed workbook.
-- Tried the code, it failed.
-- Activated a sheet w/o CFs
-- The code worked.
-- Went back and activated sheet with CFs
-- The code failed.
-- Removed the CFs
-- The code worked!

I can easily have a sheet w/o CFs activated when I use this code, that's no
problem for this project. Or, I can use the code that MS supplied me with.

Thanks very much. I will forward a link to this thread to the technician
that worked on my problem.

 

[Peter T]

I thought the scenario sounded vaguely familiar !

Might be worth doing something like this -

On Error Resume Next
n =
Windows(1).VisibleRange.SpecialCells(xlCellTypeAllFormatConditions).Count
On Error GoTo 0

If n = 0 Then
' no problem
Else
' potential problem if CF's are IsFormula with w/s function
' activate another sheet or goto bottom of usedrange
' or trap the range of CF's and Goto row below (or col to right) of the
last one with Scroll
End If

Or, I can use the code that MS supplied me with.

As I think I pointed out in one of the threads, only if all your users have
XL2002+

Regards,
Peter T

 

[Guest]

Sounds like a good solid work around. In my case it will be easier to
directly control the active sheet -- it won't be an issue with the function
the users will be performing at that point. Also, everyone is on XL2003, so
the MS work around might be simplest.

Thanks again for your help.