Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automating Importing Data from Excel (2002/SP2)

    Following up on my <post#=532639>post 532639</post#>. I'm exporting information from Simply into an Excel file, and now want to automate importing it into Access.
    Posts like Han's <post#=228,920>post 228,920</post#>, explain how to use the Transfer Spreadsheet method and I'm fine with that.
    But now, I want to find a way to allow the user to browse to the file (as there is no guarantee where it is or where it will be stored).

    Any suggestions?

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

    Re: Automating Importing Data from Excel (2002/SP2)

    If all users will be on Access 2002 (XP) or higher, you can use the new Application.FileDialog object. Here is a simple example:

    Sub GetFilename()
    Dim strFilename As String
    With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel files", "*.xls"
    If .Show = True Then
    strFilename = .SelectedItems(1)
    MsgBox "You selected " & strFilename
    End If
    End With
    End Sub

    You must set a reference to the Microsoft Office 10.0 Object Library (10.0 for Office XP, 11.0 for Office 2003).

    If you also have Access 2000 users, you can't use the FileDialog object. The database attached to <post:=304,810>post 304,810</post:> contains a class module and a standard module that provide a wrapper about the Windows API calls for the File Open dialog; this will work in any version of Access (in fact, the database is in Access 97 format). The form in the database demonstrates how to use the code.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating Importing Data from Excel (2002/SP2)

    Works like a charm!

Posting Permissions

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