PDA

View Full Version : Solved: Sloved:Why GetOpenFilename not working



willin
07-19-2007, 09:19 AM
Hi all,

Why IsArray(Nfilename) always return false and Nfilename(1) get error msg "type dismatch)" in Beneath code at office2000:

Sub Macro1()
Dim Nfilename As Variant
Nfilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , , , True)
if not IsArray(Nfilename) then MsgBox "No files selected")
s=Nfilename(1)
End Sub

thks!

xld
07-19-2007, 09:23 AM
Apart from the superfluous ), it all works fine for me.

willin
07-19-2007, 09:27 AM
Apart from the superfluous ), it all works fine for me.

:(

xld
07-19-2007, 09:40 AM
Post an offending workbook so we can see it.

malik641
07-19-2007, 09:55 AM
s=Nfilename(1)
What is the 's' variable's type? Variant? String?

And as XLD said, please post a workbook with the error so we can replicate it.

p45cal
07-19-2007, 10:16 AM
change to


Sub Macro1()
Dim Nfilename As Variant
Nfilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , , , True)
If Not IsArray(Nfilename) Then
MsgBox "No files selected"
Exit Sub
End If
s = Nfilename(1)
End Sub
The error occurred if the user clicks Cancel in the dialogue box, NFilename isn't an array at that point, so NFilename(1) is meaningless.

p45cal

willin
07-19-2007, 11:00 PM
change to
The error occurred if the user clicks Cancel in the dialogue box, NFilename isn't an array at that point, so NFilename(1) is meaningless.
p45cal

No,
I mean even if I selected files, IsArray() still return false.


I had done lot of debug , now I got the root cause : If there hv error on condition format formula in opening any files, then it will be happen.

Pls find attached file, if you remove the Cell"G34" condition format or you change H34 from 0 to any digital but zero. then macro will fine. otherwise the code always return error whatever you select files or not.
But I don't know if it fine in office2003.

But I need the condition format remain allow h34=0,
How Can I do to solve this problem is macro?

Thks!

p45cal
07-20-2007, 01:54 AM
see:
www . excelforum.com/archive/index.php/t-497214.html

(note that the url above has had to be mangled by me as this is only my second post to this forum and until I've posted 5 times I'm not allowed to include links, so you'll have to unmangle it)
search for the text 'tested this' where a variety of solutions/workarounds are suggested for this bug.

p45cal

willin
07-20-2007, 02:02 AM
Thks, Let me try ....

willin
07-20-2007, 03:35 AM
Now I got the solution:
3. scroll cf's cell(s) are no longer in view.
4. activate another worksheet then it suceeds under all conditions.

so some code need added before "getopenfilename" to check if related action is required.

Helper cell isn't suit my situation. there some difference on cf's:
my case due to cf's formulas result is error (div 0) but not used wf's.

Thks p45cal given the good url!
Thks all of u for responding!