ACC: Parsing File Names Selected from Common Dialog Control

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

The Common Dialog OLE custom control enables you to select multiple file names from the File Open dialog box. This article demonstrates how to determine which files the user selects and to fill a list box with the selected file names.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

When using the Common Dialog control to select multiple files from the File Open dialog box, the FileName property of the Common Dialog control will return both the path and names of the selected files. If the Common Dialog control only allows long file names to be selected, the path and file names are returned in a null character delimited format, with the path listed first, and the files listed subsequently as follows:

   {Path}<NULL>{File1.EXT}<NULL>{File2.EXT}<NULL>...{FileN.EXT}

   C:\MSOffice\Access\Samples Northwind.mdb Solutions.mdb Orders.mdb

   {Path}<Space>{File1.EXT}<SPACE>{File2.EXT}<SPACE>...{FileN.EXT}

1. Open the sample database Northwind.mdb.

2. Create a new table with the following structure:

   Table: SelectedFiles -------------------- Field Name: FileName

             Data Type: Text
             Field Size: 255
             Indexed: No
   
   

3. Save the table as SelectedFiles.

4. Create a new form in Design view.

5. On the Insert menu, click Custom Control, and select the Common Dialog control.

6. Set the Name property of the Common Dialog control to CmnDlg.

7. Add the following controls to the form:

   Form: FileList --------------

   Command button:

             Name: Command0
             Caption: Select Files
             OnClick: [Event Procedure]
   
          List box:
             Name: FileList
             RowSource: SelectedFiles
   
   

8. On the View menu, click Code, and type the following text into the Form module:

   Option Compare Database Option Explicit

   Private Sub Command0_Click()

              Dim MyString As String
              Dim Path As String
              Dim FileName As String
              Dim I As Integer, J As Integer
              Dim s As String
              ReDim filearray(1) As String
   
              '=============================================================
              '                    LONG FILE NAME USAGE
              '
              ' Use the following two lines of code for LONG file names only.
              ' If you will be using long file names, please unremark the
              ' following two lines and remark out the two lines under the
              ' Short File Name Usage section below.
              '
              Me!CmnDlg.flags = cdlOFNAllowMultiselect _
                 + cdlOFNFileMustExist + cdlOFNHideReadOnly + _
                 cdlOFNNoDereferenceLinks + cdlOFNLongNames + cdlOFNExplorer
              s = Chr(0)
   
   
              '=============================================================
              '                    SHORT FILE NAME USAGE
              ' Use the following two lines of code for SHORT file names only.
              ' If you will be using short file names, please unremark the
              ' following two lines and remark out the two lines under the
              ' Long File Name Usage section above.
              '
              '  Me!CmnDlg.flags = cdlOFNAllowMultiselect _
                   + cdlOFNFileMustExist + cdlOFNHideReadOnly
              ' s = Chr(32)
              '==============================================================
   
              Me!CmnDlg.DialogTitle = "Select 1 or More Files"
              Me!CmnDlg.Filter = "All Files(*.*)|*.*|"
              Me!CmnDlg.FileName = ""
              Me!CmnDlg.CancelError = True
              On Error GoTo SampleFiles_Err
              Me!CmnDlg.MaxFileSize = 1024
              Me!CmnDlg.ShowOpen
              MyString = Me!CmnDlg.FileName
   
              If InStr(1, MyString, s) > 0 Then
                  If (InStr(MyString, "\") + 1) = InStr(MyString, s) Then
                     Path = Trim(Left$(MyString, InStr(1, MyString, s) - 1))
                  Else
                     Path = Trim(Left$(MyString, InStr(1, MyString, s) - 1))_
                        & "\"
                  End If
                  FileName = Right$(MyString, Len(MyString) _
                      - InStr(1, MyString, s))
                  MyString = FileName
                  J = 0
   
                  For I = 1 To Len(FileName)
                      If Mid$(FileName, I, 1) = s Then
                          J = J + 1
                      End If
                  Next
                  J = J + 1
                  ReDim filearray(1 To J) As String
                  For I = 1 To UBound(filearray)
                      If I < UBound(filearray) Then
                         FileName = Trim(Left$(MyString, InStr(1, MyString, _
                            s) - 1))
                         MyString = Trim(Right$(MyString, Len(MyString) _
                             - InStr(1, MyString, s)))
                      Else
                          FileName = MyString
                      End If
                      filearray(I) = FileName
                  Next
              Else
                  For I = 1 To Len(MyString)
                     If Mid$(MyString, I, 1) = "\" Then
                         Path = Left$(MyString, I)
                         FileName = Right$(MyString, Len(MyString) - I)
                     End If
                  Next
                  filearray(UBound(filearray)) = FileName
              End If
              DBEngine.Workspaces(0).BeginTrans
              For I = 1 To UBound(filearray)
                 CurrentDb.Execute "INSERT INTO SelectedFiles(FileName)" _
                 & " VALUES ('" & Path & filearray(I) & "');"
              Next
              DBEngine.Workspaces(0).CommitTrans
              ReDim filearray(1) As String
              Me!FileList.Requery
   
          SampleFiles_Exit_Sub:
              Exit Sub
   
          SampleFiles_Err:
              If Err.Number <> cdlcancel Then MsgBox Err.Description
              Resume SampleFiles_Exit_Sub
          End Sub
   
   

9. Save the form as FileList.

10. Open the form in Form view and click the Select Files button.

11. Select multiple files from the OpenFile dialog box. Note that the selected files are added to the list box.

Keywords          : IntpCstm kbinterop kbprg PgmParse
Version           : 7.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo