Results 1 to 3 of 3
  1. #1
    sgtpsychosis is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2012
    Posts
    2

    Importing from multiple spreadhseets across multiple workbooks

    Assume there are three personnel who process data, and this data is stored on their individual Excel workbooks. Each workbook is formatted exactly the same.
    Each person has a different number of individual spreadsheets within their workbooks, but again: the spreadhseet format is uniform across every workbook.
    So Person A has 15 spreadsheets, Person B has 25 spreadsheets, and Person C has 34 spreadsheets.

    Is there a way that I can have Access pull ALL of the data from ALL of the spreadsheets?



    The "unique key" for the primary data table in Access would be related to the spreadsheets: each spreadsheet contains data one ONE SPECIFIC item or location that has a unique serial code.

    Here is my "OH DEAR GOD, I'D LOVE TO BE ABLE TO DO THIS!" statement:
    My desired endstate:
    o I will have a common database that all three can access and input data
    o All three will be able to have the database open simultaneously
    o Queries can be run from this information to produce reports indicating amount of data that was processed over an established time period
    o Reports could be published (and here is a fun one) that would feed into Excel, which then in turn be utilized to produce .kml files and shape files
    ---
    I have used Access in the past, but it was roughly 9 years ago. I was the only person in my office using it, as I was manipulating a mass amount of information (again, in Excel) and needed to be able to provide multiple reports and data analysis based on this information...and I was the only person in the office this information was given to. I was self taught, and found Access to be extremely useful...even though it was VERY unweildy for someone who had barely started using a computer two months prior. Yes. I was a very, VERY late starter to the world of automation.

    I am now in an entirely DIFFERENT office, doing an entirely DIFFERENT job, and I am wondering if I can manuever my personnel from Excel to Access...but I will have to establish the database, create the front end, and develop the report formats first...not to mention the links, formula, setting it so that it produces reports on its own that overwrite existing reports.

    However, if I can't import & export from multiple spreadhseets across multiple workbooks...well, at this point I am going to have to figure out a new way to approach the problem. And compiling all the data onto a single spreadsheet per workbook? That isn't going to work.

    If this concept IS possible, well...I suppose I'll be looking for Access for Dummies as soon as I can!

    (The number of personnel and number of spreadsheets is just there as an example; at any given time there will be SIX workbooks, used by one to five people per workbook, with workbooks containing between 18 and 45 spreadsheets. THOSE numbers are more realistic.)

    Thanks in advance!
    Last edited by sgtpsychosis; 06-08-2012 at 01:37 AM. Reason: Identied a typographical error.

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    o I will have a common database that all three can access and input data
    o All three will be able to have the database open simultaneously
    o Queries can be run from this information to produce reports indicating amount of data that was processed over an established time period
    o Reports could be published (and here is a fun one) that would feed into Excel, which then in turn be utilized to produce .kml files and shape files
    You can do all of the above if you split your Database into a front end [Forms, Queries, . . .] and a back end [Tables (Data)].
    Google 'MS Access split database' & Microsoft has information that will make this part easy. Just be sure to follow directions 'to ... the ... letter'.

    I'll try importing multiple spreadsheets from one Workbook and get back with you on that. What have you tried on this so far?

  3. #3
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I don't have the time to play with this right now - perhaps you can give it a whirl!!
    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

    I was hoping there'd be an easier way - but this may be as easy as it gets.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export Table in Access 2007 to Multiple Workbooks in Excel 2007
    By hutchinsm in forum Import/Export Data
    Replies: 5
    Last Post: 03-01-2012, 05:23 PM
  2. Replies: 4
    Last Post: 01-31-2012, 12:42 PM
  3. Replies: 1
    Last Post: 09-07-2011, 01:56 PM
  4. Importing Excel datasheets into multiple tables
    By FishMT in forum Import/Export Data
    Replies: 3
    Last Post: 02-08-2011, 07:44 AM
  5. Importing multiple files at once
    By NoiCe in forum Import/Export Data
    Replies: 1
    Last Post: 04-01-2009, 10:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums