Type mismatch selecting >5 files with Windows File Open/Save

[PRMiller2]

I am using the code referenced over at

http://access.mvps.org/access/api/api0001.htm

to allow users to select multiple files. The code calling the dialog box is as follows:

Private Sub Test_Click()
On Error GoTo Err_Handler
    
    Dim intCounter As Integer
    Dim intFileCount As Integer
    Dim lngFlags As Long
    Dim strFilter As String
    Dim strInitialDir As String
    Dim strFiles() As String
    
    strInitialDir = "C:\Documents and Settings\prmiller\My Documents\Master Docs"
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
    lngFlags = ahtOFN_ALLOWMULTISELECT Or ahtOFN_EXPLORER
        
    strFiles = ahtCommonFileOpenSave(InitialDir:=strInitialDir, _
               Filter:=strFilter, OpenFile:=True, _
               DialogTitle:="Please select one or more input files:", _
               Flags:=lngFlags)
        
    If Not IsNull(strFiles) Then
        If IsArray(strFiles) Then
            intFileCount = UBound(strFiles)
            For intCounter = 0 To intFileCount
'                Call PricingQA(intCounter)
            Next intCounter
        Else
'            Call PricingQA(intCounter)
        End If
    End If

    
Exit_Handler:
    Exit Sub

Err_Handler:
    Call LogError(Err.Number, Err.Description, "mdlPricingQA.Test_Click()")
    Resume Exit_Handler
    
End Sub

The code works except with particular files. In other words, a user can select multiple files without an error. However, when selecting certain files, they receive an error: "Error 13: Type mismatch."

The files to be selected are:
COMPANYMAILAUDIT_07_01_074036_001.XLS
COMPANYMAILAUDIT_07_01_074036_002.XLS
COMPANYMAILAUDIT_07_01_074036_003.XLS
COMPANYMAILAUDIT_07_01_074036_004.XLS
COMPANYMAILAUDIT_07_01_074036_005.XLS
COMPANYMAILAUDIT_07_01_074036_006.XLS
COMPANYMAILAUDIT_07_01_074036_007.XLS
COMPANYMAILAUDIT_07_01_074036_008.XLS
COMPANYMAILAUDIT_07_01_074036_009.XLS
COMPANYMAILAUDIT_07_01_074036_010.XLS
COMPANYMAILAUDIT_07_01_074036_011.XLS
COMPANYMAILAUDIT_07_01_074036_012.XLS
COMPANYMAILAUDIT_07_01_074036_013.XLS
COMPANYMAILAUDIT_07_01_074036_014.XLS
COMPANYMAILAUDIT_07_01_074036_015.XLS

All files are formatted exactly the same, and files 001 - 014 are 55,000 KB. The 15th one is 14 KB.

The type mismatch occurs when selecting any one file by itself. However, when more than one file is not selected, the error does not occur... unless more than 5 files are selected, in which case it does.

Stepping through the code, VB executes ahtCommonFileOpenSave and appears to exit the function normally. However, as soon as it returns to the calling sub, it generates the error.

I'm baffled as to what's going on... any suggestions?

 

[PRMiller2]

Ok, after all that typing, I tried one other thing and solved it... but I'm hoping someone can tell me why that solved it.

I had originally dimmed strFiles as "strFiles() as String." I changed that to "Dim varFiles as Variant" and that solved all of my problems.

Thoughts as to why that makes a difference? I am a bit new to arrays...

 

[PRMiller2]

Revisiting this, with a slightly different problem. This code works well, except that I am unable to select more than 5 files. Here's the function in question (from the link above):

Function ahtCommonFileOpenSave( _
            Optional ByRef Flags As Variant, _
            Optional ByVal InitialDir As Variant, _
            Optional ByVal Filter As Variant, _
            Optional ByVal FilterIndex As Variant, _
            Optional ByVal DefaultExt As Variant, _
            Optional ByVal FileName As Variant, _
            Optional ByVal DialogTitle As Variant, _
            Optional ByVal hWnd As Variant, _
            Optional ByVal OpenFile As Variant) As Variant

    ' This is the entry point you'll use to call the common
    ' file open/save dialog. The parameters are listed
    ' below, and all are optional.
    '
    ' In:
    ' Flags: one or more of the ahtOFN_* constants, OR'd together.
    ' InitialDir: the directory in which to first look
    ' Filter: a set of file filters, set up by calling
    ' AddFilterItem. See examples.
    ' FilterIndex: 1-based integer indicating which filter
    ' set to use, by default (1 if unspecified)
    ' DefaultExt: Extension to use if the user doesn't enter one.
    ' Only useful on file saves.
    ' FileName: Default value for the file name text box.
    ' DialogTitle: Title for the dialog.
    ' hWnd: parent window handle
    ' OpenFile: Boolean(True=Open File/False=Save As)
    ' Out:
    ' Return Value: Either Null or the selected filename
    Dim OFN As tagOPENFILENAME
    Dim strFileName As String
    Dim strFileTitle As String
    Dim fResult As Boolean

    ' Give the dialog a caption title.
    If IsMissing(InitialDir) Then InitialDir = CurDir
    If IsMissing(Filter) Then Filter = ""
    If IsMissing(FilterIndex) Then FilterIndex = 1
    If IsMissing(Flags) Then Flags = 0&
    If IsMissing(DefaultExt) Then DefaultExt = ""
    If IsMissing(FileName) Then FileName = ""
    If IsMissing(DialogTitle) Then DialogTitle = ""
    If IsMissing(hWnd) Then hWnd = Application.hWndAccessApp
    If IsMissing(OpenFile) Then OpenFile = True
    ' Allocate string space for the returned strings.
    strFileName = Left(FileName & String(256, 0), 256)
    strFileTitle = String(256, 0)
    ' Set up the data structure before you call the function
    With OFN
        .lStructSize = Len(OFN)
        .hwndOwner = hWnd
        .strFilter = Filter
        .nFilterIndex = FilterIndex
        .strFile = strFileName
        .nMaxFile = Len(strFileName)
        .strFileTitle = strFileTitle
        .nMaxFileTitle = Len(strFileTitle)
        .strTitle = DialogTitle
        .Flags = Flags
        .strDefExt = DefaultExt
        .strInitialDir = InitialDir
        ' Didn't think most people would want to deal with
        ' these options.
        .hInstance = 0
        '.strCustomFilter = ""
        '.nMaxCustFilter = 0
        .lpfnHook = 0
        'New for NT 4.0
        .strCustomFilter = String(255, 0)
        .nMaxCustFilter = 255
    End With
    ' This will pass the desired data structure to the
    ' Windows API, which will in turn it uses to display
    ' the Open/Save As Dialog.
    If OpenFile Then
        fResult = aht_apiGetOpenFileName(OFN)
    Else
        fResult = aht_apiGetSaveFileName(OFN)
    End If

    ' The function call filled in the strFileTitle member
    ' of the structure. You'll have to write special code
    ' to retrieve that if you're interested.
    If fResult Then
        ' You might care to check the Flags member of the
        ' structure to get information about the chosen file.
        ' In this example, if you bothered to pass in a
        ' value for Flags, we'll fill it in with the outgoing
        ' Flags value.
        If Not IsMissing(Flags) Then Flags = OFN.Flags
        If Flags And ahtOFN_ALLOWMULTISELECT Then
            ' Return the full array.
            Dim items As Variant
            Dim value As String
            value = OFN.strFile
            ' Get rid of empty items:
            Dim i As Integer
            For i = Len(value) To 1 Step -1
              If Mid$(value, i, 1) <> Chr$(0) Then
                Exit For
              End If
            Next i
            value = Mid(value, 1, i)

            ' Break the list up at null characters:
            items = Split(value, Chr(0))

            ' Loop through the items in the "array",
            ' and build full file names:
            Dim numItems As Integer
            Dim result() As String

            numItems = UBound(items) + 1
            If numItems > 1 Then
                ReDim result(0 To numItems - 2)
                For i = 1 To numItems - 1
                    result(i - 1) = FixPath(items(0)) & items(i)
                Next i
                ahtCommonFileOpenSave = result
            Else
                ' If you only select a single item,
                ' Windows just places it in item 0.
                ahtCommonFileOpenSave = items(0)
            End If
        Else
            ahtCommonFileOpenSave = TrimNull(OFN.strFile)
        End If
    Else
        ahtCommonFileOpenSave = vbNullString
    End If
End Function

The code functions fine through these lines:

    If OpenFile Then
        fResult = aht_apiGetOpenFileName(OFN)
    Else
        fResult = aht_apiGetSaveFileName(OFN)
    End If

However, if I have selected more than 5 files, then fResult = false and Access executes the last line, "ahtCommonFileOpenSave = vbNullString".

Is there a setting I can change to allow more than 5 files?

 

[PRMiller2]

Found the solution over at

http://www.pcreview.co.uk/forums/open-file-dialog-access-2000-a-t1645512.html.

About half-way down the page, Dirk Goldgar posts:

One further note: if your users are going to be choosing large numbers of files using this dialog, you're going to need a larger buffer. The code in ahtCommonFileOpenSave allows only 256 characters for the path and file names. You can expand this to, say, 512, by changing these lines in the posted code:

strFileName = Left(FileName & String(256, 0), 256)
strFileTitle = String(256, 0)

to

strFileName = Left(FileName & String(512, 0), 512)
strFileTitle = String(512, 0)

Due to the length of the filepaths and number of files I'm accessing, I changed the characters from 256 to 1500. Problem solved!