Results 1 to 4 of 4

Thread: importing excel data to access

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2004
    Posts
    3

    importing excel data to access

    I am wanting to get some data out of excel into access. I have a database set up (C:\test.mdb), and an excel spreadsheet (C:\test.xls).

    The database has a table called 'person' with 2 fields (surname, forename) and this currently has no data.

    The spreadsheet is on a worksheet called 'Sheet1' and has 2 columns (A, B). Column A has a list of peoples surnames, and Column B has the corresponding persons forename.

    I am trying through VB to click a button which will search through column A and when the surname 'Norton' is found, it will copy the surname into the database table and correct field, then put in the corresponding forename from the spreadsheet into the forename field in the database table.

    I am sure that somebod will be able to help, so thanks in advance for the help.

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343
    Automation?
    Importing and filtering?
    Code importing and filtering?
    Joining via ado to the spreadsheet?

    Pick a method, search online then if you are still having difficulty, post up what you have at the moment.


    Vince

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2004
    Posts
    3

    Excel problems continued

    My main problem is getting connection to the excel spreadsheet and where i am a bit lost. The part for adding to the database is not a problem and is shown below. Obviously i have it hardcoded at the moment to add Matthew and Norton, which will need obviously replacing with any values that i would find through a search. I assume that this will then need putting in a do until EOF loop to go through each record.

    Set dbsMain = OpenDatabase("c:\test.mdb")
    Set RS1 = dbsMain.OpenRecordset("Person", dbOpenTable)


    Set RS1 = dbsMain.OpenRecordset("SELECT * FROM Person")
    RS1.AddNew

    RS1!Forename = "Matthew"
    RS1!Surname = "Norton"

    RS1.Update
    RS1.Close

  4. #4
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343
    VB Code:
    1. Set dbsMain = OpenDatabase("c:\test.mdb")
    2. Set RS1 = dbsMain.OpenRecordset("Person", dbOpenTable)
    3.  
    4.  
    5. Set RS1 = dbsMain.OpenRecordset("SELECT * FROM Person")
    6. RS1.AddNew
    7.  
    8. RS1!Forename = "Matthew"
    9. RS1!Surname = "Norton"
    10.  
    11. RS1.Update
    12. RS1.Close

    I was asking which way you wanted to do it because you can import the whole spreadsheet into a table and run filters on that... or you can open the spreadsheet via ado and treat it as a db (I think - haven't tried this nor will for now). Or Automation, which is opening excel hidden, opening the sheet and looping through searching for "whatever" to add...


    Vince

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

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



Click Here to Expand Forum to Full Width