GetOpenFilename MultiSelect failure

Discussion in 'Microsoft Excel Programming' started by Guest, Jan 1, 2006.

  1. Guest

    Guest Guest

    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
     
    Guest, Jan 1, 2006
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    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
     
    Guest, Jan 1, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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
     
    Guest, Jan 1, 2006
    #3
  4. Guest

    Tom Ogilvy Guest

    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
    news:...
    > 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 Ogilvy, Jan 1, 2006
    #4
  5. Guest

    Guest Guest

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

    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
    > news:...
    > > 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

    >
    >
    >
     
    Guest, Jan 1, 2006
    #5
  6. Guest

    Guest Guest

    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
    > news:...
    > > 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

    >
    >
    >
     
    Guest, Jan 1, 2006
    #6
  7. Guest

    Guest Guest

    Simply scrolling so that the conditional formatted cells are off-screen also
    works.

    Greg

    "Greg Wilson" wrote:

    > 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
     
    Guest, Jan 1, 2006
    #7
  8. Guest

    Jim Cone Guest

    Greg,
    I wonder if the problem is restricted to a particular XL version?
    What version are you using?
    Regards,
    Jim Cone
    San Francisco, USA
    http://www.realezsites.com/bus/primitivesoftware


    "Greg Wilson" <>
    wrote in message
    news:
    Simply scrolling so that the conditional formatted cells are off-screen also
    works.
    Greg
     
    Jim Cone, Jan 1, 2006
    #8
  9. Guest

    Tom Ogilvy Guest

    I couldn't reproduce that behavior in Excel 97.

    --
    Regards,
    Tom Ogilvy


    "Greg Wilson" <> wrote in message
    news:...
    > 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$1>Today()"
    >
    > 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
    > > news:...
    > > > 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 Ogilvy, Jan 1, 2006
    #9
  10. Guest

    Guest Guest

    Jim, thanks for responding.

    I've tested this on 3 computers:
    1. xl2000 (9.0.2720 SP3)
    2. xl2000 (9.0.6926 SP3)
    3. xl2000 (N/A)

    I am using Tom's test code:

    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

    I have found the following:
    1. If you run the code from the VBE it fails if you have a cell or cells
    with conditional formats (cf's) that include a worksheet function (wf).
    2. If there is no wf then it succeeds when called from the VBE.
    3. If you scroll so that the cell(s) are no longer in view then it succeeds
    when called from the VBE inclusive of wf's.
    4. If you call it through Tools>Macro>Macros then it succeeds under all
    conditions.
    5. If you have a helper cell that holds the desired wf (I used the Today()
    function) and the cf references this cell instead then it succeeds. The
    helper cell can be in the visible range. This would appear to be the solution
    of choice.
    6. If you activate another worksheet then it suceeds under all conditions.
    7. On the first of the computers listed it fails when the filter is
    extended to include *.doc. However, this was tested from within my project
    (lots of code including event code and public vars).
    8. In contrast, on the second computer listed it succeeds when the filter
    is extended to include *.doc.
    9. I get inconsistant results when in my project - i.e. it fails after a
    while even if NOT run through the VBE. However, the macro is fired through a
    right-click event and not through Tools>Macro>Macros. The project is complex
    and in the process of development and I'm still evaluating the situation.
    Hoever, it does not appear to be caused by an simple error state. The problem
    appears to be much more subtle.

    Your post seems to imply that you don't corroborate the phenomenon. Is this
    so?

    Thanks again for responding.

    Regards,
    Greg



    "Jim Cone" wrote:

    > Greg,
    > I wonder if the problem is restricted to a particular XL version?
    > What version are you using?
    > Regards,
    > Jim Cone
    > San Francisco, USA
    > http://www.realezsites.com/bus/primitivesoftware
    >
    >
    > "Greg Wilson" <>
    > wrote in message
    > news:
    > Simply scrolling so that the conditional formatted cells are off-screen also
    > works.
    > Greg
    >
    >
     
    Guest, Jan 1, 2006
    #10
  11. Guest

    Tom Ogilvy Guest

    Based on your latest post specifying conditions, I can duplicate it if I run
    it from the VBE. (xl97). It says nothing is selected when there is a
    selection.

    --
    Regards,
    Tom Ogilvy

    "Tom Ogilvy" <> wrote in message
    news:...
    > I couldn't reproduce that behavior in Excel 97.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Greg Wilson" <> wrote in message
    > news:...
    > > 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$1>Today()"
    > >
    > > 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
    > > > news:...
    > > > > 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 Ogilvy, Jan 1, 2006
    #11
  12. Guest

    Jim Cone Guest

    Greg,

    Ran Tom's macro on XL 97, 2000, 2002.
    Used steps 1 3, 4 on all.
    Also used step 2 on XL 97.
    All of above on WindowsXP

    My results agree with yours.
    Failure is defined as the Msgbox showing "Nothing Selected"

    Conclusions/opinions:
    I think it's a "bug"
    Change your code or scroll the sheet before calling the file open dialog.
    MS won't fix it, but if they did issue a fix now, the problem
    would still be there in the next release.

    Regards,
    Jim Cone
    San Francisco, USA
    http://www.realezsites.com/bus/primitivesoftware



    "Greg Wilson" <>
    wrote in message
    news:

    Jim, thanks for responding.
    I've tested this on 3 computers:
    1. xl2000 (9.0.2720 SP3)
    2. xl2000 (9.0.6926 SP3)
    3. xl2000 (N/A)
    I am using Tom's test code:
    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

    I have found the following:
    1. If you run the code from the VBE it fails if you have a cell or cells
    with conditional formats (cf's) that include a worksheet function (wf).
    2. If there is no wf then it succeeds when called from the VBE.
    3. If you scroll so that the cell(s) are no longer in view then it succeeds
    when called from the VBE inclusive of wf's.
    4. If you call it through Tools>Macro>Macros then it succeeds under all
    conditions.
    5. If you have a helper cell that holds the desired wf (I used the Today()
    function) and the cf references this cell instead then it succeeds. The
    helper cell can be in the visible range. This would appear to be the solution
    of choice.
    6. If you activate another worksheet then it suceeds under all conditions.
    7. On the first of the computers listed it fails when the filter is
    extended to include *.doc. However, this was tested from within my project
    (lots of code including event code and public vars).
    8. In contrast, on the second computer listed it succeeds when the filter
    is extended to include *.doc.
    9. I get inconsistant results when in my project - i.e. it fails after a
    while even if NOT run through the VBE. However, the macro is fired through a
    right-click event and not through Tools>Macro>Macros. The project is complex
    and in the process of development and I'm still evaluating the situation.
    Hoever, it does not appear to be caused by an simple error state. The problem
    appears to be much more subtle.

    Your post seems to imply that you don't corroborate the phenomenon. Is this
    so?
    Thanks again for responding.
    Regards,
    Greg



    "Jim Cone" wrote:

    > Greg,
    > I wonder if the problem is restricted to a particular XL version?
    > What version are you using?
    > Regards,
    > Jim Cone
    > San Francisco, USA
    > http://www.realezsites.com/bus/primitivesoftware >
    >
    > "Greg Wilson" <>
    > wrote in message
    > news:
    > Simply scrolling so that the conditional formatted cells are off-screen also
    > works.
    > Greg
     
    Jim Cone, Jan 1, 2006
    #12
  13. Guest

    Guest Guest

    Thanks Tom for your help. After developing the project further with more
    evaluation, I supect I'll go the helper cell route. I have my project working
    OK for now but it's been proven not reliable. Thanks to you both for taking
    the time. I'm sure this will be of value to others.

    Greg
     
    Guest, Jan 2, 2006
    #13
  14. Guest

    Guest Guest

    Thanks Jim for taking the time. As I told Tom:
    After developing the project further with more evaluation, I supect I'll go
    the helper cell route. I have my project working OK for now but it's been
    proven not reliable. Thanks to you both for taking the time. I'm sure this
    will be of value to others.

    Best Regards,
    Greg


    "Jim Cone" wrote:

    > Greg,
    >
    > Ran Tom's macro on XL 97, 2000, 2002.
    > Used steps 1 3, 4 on all.
    > Also used step 2 on XL 97.
    > All of above on WindowsXP
    >
    > My results agree with yours.
    > Failure is defined as the Msgbox showing "Nothing Selected"
    >
    > Conclusions/opinions:
    > I think it's a "bug"
    > Change your code or scroll the sheet before calling the file open dialog.
    > MS won't fix it, but if they did issue a fix now, the problem
    > would still be there in the next release.
    >
    > Regards,
    > Jim Cone
    > San Francisco, USA
    > http://www.realezsites.com/bus/primitivesoftware
     
    Guest, Jan 2, 2006
    #14
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tarun Mathur

    Excel/VBA GetOpenFileName/MultiSelect Problem

    Tarun Mathur, Jul 22, 2003, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    606
    Tarun Mathur
    Jul 22, 2003
  2. Daniel

    GetOpenFilename

    Daniel, Jul 26, 2003, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    442
    Heiko
    Jul 27, 2003
  3. Shunt

    GetOpenFilename and arrays

    Shunt, Jul 31, 2003, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    218
    Shunt
    Jul 31, 2003
  4. felze84

    VBA prob-GetOpenFilename with multiselect=true returns string

    felze84, Sep 3, 2004, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    835
    Guest
    Oct 18, 2004
  5. Guest
    Replies:
    9
    Views:
    688
    Peter T
    Jul 20, 2006
Loading...

Share This Page