Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Posts
    47

    Unanswered: Open Excel in Background, Update vLookUps, and Write to Access DB

    Would very much appreciate some guidance... At the click of a button in Access 2007 I would like to 1) open a number of Excel source files that contain data without the user seeing them; 2) open another Excel file with many vLookUps that reads the data from the source files and consolidates it into one place; 3) update my Access DB with the Excel file that gathers (i.e. with the vLookUps) the data. Any code examples would be very helpful.
    Thanks,
    Jack Kent
    Lavallette, NJ

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by jackkent View Post
    Would very much appreciate some guidance... At the click of a button in Access 2007 I would like to 1) open a number of Excel source files that contain data without the user seeing them; 2) open another Excel file with many vLookUps that reads the data from the source files and consolidates it into one place; 3) update my Access DB with the Excel file that gathers (i.e. with the vLookUps) the data. Any code examples would be very helpful.
    Thanks,
    Jack Kent
    Lavallette, NJ
    Assuming you don't have to make any changes to the vLookup spreadsheet functions, it's pretty staightforward:

    1 - Use the Shell() function to open this file.
    2 - Ditto
    3 - Use the DoCmd.TransferSpreadsheet method to do this. You can link to or import the Excel file, your choice. (I prefer deleting the old link and linking to the new. That way your Excel file doesn't take up any space at all in the Access db. Otherwise you have to delete the old table and import a new table which can be time- and space-consuming.)

    You would then have a make-table or append query to either overwrite or add the data to your current table, respectively.

    Please consult the Help file if you're unfamiliar with anything I've mentioned.

    Sam

Posting Permissions

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