I am currently working on a problem that I have seen many partial solutions for but nothing that does what I am particularly interested in solving.
The problem: I have an excel file that I use to create a pdf that I send out as an informative brochure. This file contains cells that calculate data from other cells and then certain portions have graphs that plot data from other cells. However, this brochure must be able to be created for many (greater than 30) different sets of data, however the data may change and I may need to add new data so this is not a 1-and-done situation. So, I want to have a seperate database (Access, SQL, InfoPath, anything) that stores all of the data sets and then be able to select one set, click a button and then have individual data records from the database populate individual cells in the excel file and then let the linked calculations do the rest.
Attempts: I have tried to create a database in both Access and SQL so that I can pull/push data to excel, but whenever I do that, I get a the full table or a full row of data and it all just stacks in one corner of the excel file. I can't grab "Row 3 Field 7" from Access and put it directly into "Cell D13" of excel. Also, I've tried to write an internal array to keep the data in excel through vba, but the person that I am setting this up for may not have the technical expertise to fix the code if something eventually goes wrong.
Help: If someone could help me figure out how to make that 1-record to 1-cell data transfer I would appreciate it or if you have any other suggestions, I would welcome them as well.
Thanks!
Bookmarks