Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Data From an Excel SS using VBA (Access 2000 SR1)

    Hello,

    I am trying to write a procedure that would allow the user to navigate out to an excel spreadsheet and import it into the DB. The spreadsheet has a consistent format that matches the table that it will be imported into. What is the best way to do this.

    In Excel, you can use the "Application.GetOpenFilename" function to navigate and select files.

    Any suggestions will be appreciated.

    Regards,

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing Data From an Excel SS using VBA (Access 2000 SR1)

    There is a TransferSpreadsheet command which should do what you want.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Importing Data From an Excel SS using VBA (Access 2000 SR1)

    Access didn't use to have the equivalent of GetOpenFilename (I think I read somewhere that Access XP has one, but I'm not sure). I use API calls to present an "Open" dialog to the user. See for instance <post#=188202>post 188202</post#>.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data From an Excel SS using VBA (Access 2000 SR1)

    I often use the File Import function to import a spreadsheet into Access. I always give it the same name. (ok) Then run an append Query to attach it to the existing table.
    Is this anything like what you are trying to do?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data From an Excel SS using VBA (Access 2000 SR1)

    Yes, this is exactly what I am trying to do. But I want to provide an interface so that the user can navigate to the file that will be imported. Once the file is selected, the Import should be automatic.

    Thanks,

Posting Permissions

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