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

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Greg Wilson" wrote:

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

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Greg Wilson" wrote:

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

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.

--
Regards,
Tom Ogilvy

"Greg Wilson" wrote in message
...
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

"chijanzen" wrote:

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

--
???,???????
???,???????

http://www.vba.com.tw/plog/


"Greg Wilson" wrote:

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

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$1Today()"

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Greg Wilson" wrote in message
...
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

"chijanzen" wrote:

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

--
???,???????
???,???????

http://www.vba.com.tw/plog/


"Greg Wilson" wrote:

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

I couldn't reproduce that behavior in Excel 97.

--
Regards,
Tom Ogilvy


"Greg Wilson" wrote in message
...
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$1Today()"

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Greg Wilson" wrote in message
...
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

"chijanzen" wrote:

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

--
???,???????
???,???????

http://www.vba.com.tw/plog/


"Greg Wilson" wrote:

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

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Greg Wilson" wrote in message
...
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

"chijanzen" wrote:

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

--
???,???????
???,???????

http://www.vba.com.tw/plog/


"Greg Wilson" wrote:

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