ironj32
05-25-2007, 06:11 AM
firstly, i want to thank everyone for all of the help that i have recieved in the creation of the complex survey that i have. it works great! this forum is awesome.
now...here is my situation.
i am going to be receiving a couple thousand seperate workbooks over the next few months. all of the workbooks have one sheet all formated the same. i would like to set something up to put the data (http://www.tek-tips.com/viewthread.cfm?qid=1371557&page=1#) into my access database. i do have a table in access with all of the same corresponding fields. the thing is that the workbook/sheet does not have the info all in one row. it is all over a1, b3, a5, etc... i am looking for some suggestions on how to accomplish this.
i'm thinking of maybe having a an Access Form where i can click a button, prompt me to select a file click, click "ok" then it puts all of the data in my table. it would also be nice to have this automatically move the excel to a new folder (Completed Survey).
example:
vendor1.xls cells a1, b3, b6, c7 would be row 1 in the access table
vendor2.xls cells a1, b3, b6, c7 would be row 2 in the access table
vendor3.xls cells a1, b3, b6, c7 would be row 3 in the access table
i attached a copy of one of the workbooks to make things clearer.
Thanks for your suggestions!
now...here is my situation.
i am going to be receiving a couple thousand seperate workbooks over the next few months. all of the workbooks have one sheet all formated the same. i would like to set something up to put the data (http://www.tek-tips.com/viewthread.cfm?qid=1371557&page=1#) into my access database. i do have a table in access with all of the same corresponding fields. the thing is that the workbook/sheet does not have the info all in one row. it is all over a1, b3, a5, etc... i am looking for some suggestions on how to accomplish this.
i'm thinking of maybe having a an Access Form where i can click a button, prompt me to select a file click, click "ok" then it puts all of the data in my table. it would also be nice to have this automatically move the excel to a new folder (Completed Survey).
example:
vendor1.xls cells a1, b3, b6, c7 would be row 1 in the access table
vendor2.xls cells a1, b3, b6, c7 would be row 2 in the access table
vendor3.xls cells a1, b3, b6, c7 would be row 3 in the access table
i attached a copy of one of the workbooks to make things clearer.
Thanks for your suggestions!