Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Spreadsheet Import using explorer type dialogue (Access97 (SR2))

    I have a few stand alone, unshared databases on users PC's that rely on the importing of Excel Spreadsheets for data. They each have a large number of spreadsheets that do a variety of calculations, but each of the spreadsheets has a workbook that is formatted the same as all the others that summarizes the calculations in the same way.

    Right now I am pointing the import process to a specific folder/filename, and have the users put a copy of their spreadsheet in this folder, with the specified name ("C:WINDOWSDESKTOPXL2ACCESS.XLS"). The users would like to be able to navigate to their existing spreadsheets as needed, without having to make a copy, rename it and place it in the desktop folder (can't say that I blame them, either).

    I want to allow users to import spreadsheet with different file names and locations easily. Can the TransferSpreadsheet Action in visual basic be tweaked to allow the "FILENAME" argument to be added by the user through an explorer-type dialogue, rather than hard-coded in code? Or is there another better way to go about this?

    I hope this makes sense; I am obviously fairly new to writing code, but sure enjoy the challenge ! Thanks in advance.

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spreadsheet Import using explorer type dialogue (Access97 (SR2))

    On the CD for Volume 1 of Access 2000 Developer's Handbook by Getz, Litwin and Gilbert, there is a code module named CommonDlg that allows you to do just the thing you're looking to do via the Windows File -> Open dialog box. As it is copyrighted material, and rightly so, I hesitate to post their code, but will post code that I wrote to use their module, and commend the book to you. In this function, I invoke Getz, et al's module, pass it some parameters to configure the dialog box to my diabolical specifications, and import the chosen text file using an established import specification. Best of luck!

    <pre>Public Function ImportFile()

    Dim cdl As CommonDlg
    Dim intPos As Integer
    Dim strImport As String
    'instantiate the CommonDlg class module
    Set cdl = New CommonDlg

    On Error GoTo HandleErrors
    'set the options for the Open common dialog
    With cdl
    'The types of file to display
    .Filter = "Text files (*.txt)" & "*.txt"
    'The directory in which to begin
    .InitDir = "C:Program FilesProcomm PlusDownload"
    'You can seed the surfing with a file name if you choose
    .FileName = "src1.txt"
    'Hide files marked as Read Only
    .OpenFlags = cdlOFNHideReadOnly
    'If the user clicks cancel, raise a trappable error
    .CancelError = True
    End With
    'launch Open common dialog
    cdl.ShowOpen
    'Use selected file as source for import routine
    strImport = cdl.FileName
    DoCmd.TransferText acImportFixed, "Src1 Link Specification", _
    "src1_txt", strImport, False, ""

    ExitHere:
    Set cdl = Nothing
    Exit Function

    HandleErrors:
    Select Case Err.Number
    Case cdlcancel
    MsgBox "You cancelled, doughhead"
    Resume ExitHere
    Case Else
    MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
    End Select

    End Function
    </pre>

    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Spreadsheet Import using explorer type dialogue (Access97 (SR2))

    In addition to Shane's reference, you can find code for the FileOpenSave dialog in numerous places on the web, including Dev Ashish's site, http://www.mvps.org/access/. There are also sneaky ways to do it by hijacking the built-in capabilities of other Office apps like Word and Excel. Take a look at this thread and see if it's of any help.
    Charlotte

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spreadsheet Import using explorer type dialogue (Access97 (SR2))

    Thanks much, Shane & Charlotte! I'll check out your recommendations. Very Much appreciated!

Posting Permissions

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