Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Torn between Kent & Essex, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FileOpen Common Dialog (Access 2K)

    I am (trying to) building an app that imports an excel file to a temp table, adds columns, does a few calcs, then exports it as a text file with a few bells and whistles on the way.
    There's no way to nail down the excel filename so I'd like to use the common dialog just to get the filename (i.e. browse for the file) - I have another button to do the import but for this I need the filename.
    Any help or pointers would be most appreciated.
    TIA
    Gavin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileOpen Common Dialog (Access 2K)

    I use the Windows API functions to display the Open and Save As dialogs. I have attached the code needed for that as a text file. You can paste the code into a standard module. This code should work in all Windows versions.

    It contains two public functions: OpenDialog and SaveDialog. Here is example code to get the name of an Excel file, as OnClick routine for a command button named cmdBrowse. You must replace "C:Excel" by the folder you want to display (if you leave it empty, you'll get the default Access folder).

    Private Sub cmdBrowse_Click()
    On Error GoTo Err_cmdBrowse_Click

    Dim OFN As OPENFILENAME
    Dim strFileName As String

    ' Set options for Open dialog.
    With OFN
    .lpstrTitle = "Open Excel Workbook"
    .lpstrInitialDir = "C:Excel"
    .lpstrFile = "*.xls"
    .flags = &H1804 ' OFN_FileMustExist + OFN_PathMustExist + OFN_HideReadOnly
    .lpstrFilter = MakeFilterString("Excel workbooks", "*.xls")
    End With

    ' Display Open dialog box.
    If OpenDialog(OFN) Then
    strFileName = Trim(OFN.lpstrFile)
    ' Do something with file name, e.g.
    MsgBox strFileName
    End If

    Exit_cmdBrowse_Click:
    Exit Sub

    Err_cmdBrowse_Click:
    MsgBox Err.Description, vbExclamation
    Resume Exit_cmdBrowse_Click
    End Sub
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    Torn between Kent & Essex, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileOpen Common Dialog (Access 2K)

    It works a treat - many thanks, Hans. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    I can now get the filename, path etc. for the file to import. However, whereas this is normally to be an excel file, it may on occassion be a text delim. (*.txt, *.csv, etc.) depending on what source it comes from.
    Is there an easy way to import either file type or do I have to run a check on the file type and then use the appropriate docmd.Transfer~ with a case select?

    Cheers!
    Gavin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileOpen Common Dialog (Access 2K)

    AFAIK, there is not a single instruction that imports all types, so you will have to distinguish several cases.

    DoCmd.Transferspreadsheet is for Excel (and Lotus) files.

    DoCmd.TransferText is for text files (extensions .txt., .csv, .tab and .asc). You have to provide a TransferType parameter, i.e. you must know whether the text file is fixed width or delimited. It's easy for .csv and .tab - those are always delimited, but .txt might be either. You may also have to create and save import specifications for several types.

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    Torn between Kent & Essex, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import file types (Access 2K)

    Hmm...
    I was hoping there was some alternative, but I suppose I can test for the extension and use this result in a Select Case...End Select. I'm assured that if the import file is text then it will be either tab or comma delimited, but thanks for bringing the problem of fixed width to my attention - I'll just tell them to make sure the file is in the correct format first.
    Many thanks,
    Gavin

  6. #6
    Lounger
    Join Date
    Feb 2001
    Location
    Torn between Kent & Essex, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileOpen Common Dialog (Access 2K)

    Quick question (I hope):

    In the code

    ' Set options for Open dialog.
    With OFN
    .lpstrTitle = "Open Excel Workbook"
    .lpstrInitialDir = "C:Excel"
    .lpstrFile = "*.xls"
    .flags = &H1804 ' OFN_FileMustExist + OFN_PathMustExist + OFN_HideReadOnly
    .lpstrFilter = MakeFilterString("Excel workbooks", "*.xls")
    End With

    how do I include multiple file types?

    I've tried

    With OFN
    .lpstrTitle = "Open Retest File"
    .lpstrInitialDir = GetCurrDBPath
    .lpstrFile = "*.xls, *.csv"
    .flags = &H1804 'OFN_FileMustExist + OFN_PathMustExist + OFN_HideReadOnly
    .lpstrFilter = MakeFilterString("Retest files", "*.xls, *.csv, *.txt")
    End With

    but it didn't work.

    Anyone any suggestions?

    Regards,
    Gavin

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileOpen Common Dialog (Access 2K)

    Sorry for the tardy reply. My Internet connection went down.

    There are two options:
    <UL><LI>Provide separate items for each file type. For instance, this will present *.csv as default type:

    Dim OFN As OPENFILENAME
    With OFN
    .lpstrFilter = MakeFilterString("Excel workbooks", "*.xls", _
    "Comma separated values", "*.csv", "Text files", "*.txt")
    .nFilterIndex = 2
    .lpstrFile = "*.csv"
    End With

    <LI>Provide one item for all types:

    Dim OFN As OPENFILENAME
    With OFN
    .lpstrFilter = MakeFilterString("All kinds of files", "*.xls;*.csv;*.txt")
    .lpstrFile = "*.csv;*.csv;*.txt"
    End With

    Note that the extensions are separated by semi-colons.[/list]

  8. #8
    Lounger
    Join Date
    Feb 2001
    Location
    Torn between Kent & Essex, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileOpen Common Dialog (Access 2K)

    Superb! Thanks again, Hans!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •