populating spreadsheet with data from Access TLuebke 19.12.2005 18:50:03 Each semester a coworker needs to repopulate course data into a spreadsheet that contains various calculations. I would like to create a macro/function to pull data from an existing Access database and populate the spreadsheet with data such as course ID (primary key), Instructor name, enrollment count etc. The columns needing updating aren't adjacent to each other. Doing a query via Excel just puts all the data into the first column or two. How do I go about mapping the appropriate fields in access to the corresponding cells in excel? all this without whacking the underlying formulas. If someone could point me in the right direction with a framework of steps needed to do this, I think I could figure out the code part. Thanks, Todd

Re: populating spreadsheet with data from Access "Dick Kusleika" <dkusleika[ at ]gmail.com> 21.12.2005 01:40:07 TLuebke wrote: [Quoted Text] > Each semester a coworker needs to repopulate course data into a > spreadsheet that contains various calculations. I would like to create > a macro/function to pull data from an existing Access database and > populate the spreadsheet with data such as course ID (primary key), > Instructor name, enrollment count etc. The columns needing updating > aren't adjacent to each other. > > Doing a query via Excel just puts all the data into the first column > or two. How do I go about mapping the appropriate fields in access to > the corresponding cells in excel? all this without whacking the > underlying formulas. > > If someone could point me in the right direction with a framework of > steps needed to do this, I think I could figure out the code part. > Todd: The best way, in my opinion, is to create the external data table in another, possibly hidden, worksheet and use formulas to extract the data into other places. Absent that, you can try these links: http://www.dicks-blog.com/archives/2004/12/16/ado-recordset-basics/ http://www.dicks-blog.com/archives/2005/01/31/using-ado-in-functions/ http://www.dicks-blog.com/archives/2004/05/24/navigate-a-recordset-with-a-userform-part-i/ -- Dick Kusleika MVP-Excel www.dailydoseofexcel.com

Re: populating spreadsheet with data from Access TLuebke 21.12.2005 14:58:02 Thanks Dick, I'm new to excel so please bear with me. I hadn't thought of bringing the data into a hidden worksheet as an interim step. That does make sense. Thinking out loud here: 1. Create a userform that prompts for the semester code and a name for the new worksheet. ' as per tutorial 2. That information is used to create the hidden worksheet named "SemestercodeHidden" and pull data into it. 'as per ado tutorial 3. I suspect I should have a "template" worksheet with all the calcs on it. VBA code copies that and renames it " New Name" 4. ???? how do I move the essential data from the Hidden worksheet into the New worksheet, populating the appropriate columns/rows? Would you happen to have another tutorial hiding someplace ;) Thanks, Todd "Dick Kusleika" wrote: [Quoted Text] > TLuebke wrote: > > Each semester a coworker needs to repopulate course data into a > > spreadsheet that contains various calculations. I would like to create > > a macro/function to pull data from an existing Access database and > > populate the spreadsheet with data such as course ID (primary key), > > Instructor name, enrollment count etc. The columns needing updating > > aren't adjacent to each other. > > > > Doing a query via Excel just puts all the data into the first column > > or two. How do I go about mapping the appropriate fields in access to > > the corresponding cells in excel? all this without whacking the > > underlying formulas. > > > > If someone could point me in the right direction with a framework of > > steps needed to do this, I think I could figure out the code part. > > > > Todd: > > The best way, in my opinion, is to create the external data table in > another, possibly hidden, worksheet and use formulas to extract the data > into other places. Absent that, you can try these links: > > http://www.dicks-blog.com/archives/2004/12/16/ado-recordset-basics/ > http://www.dicks-blog.com/archives/2005/01/31/using-ado-in-functions/ > http://www.dicks-blog.com/archives/2004/05/24/navigate-a-recordset-with-a-userform-part-i/ > > -- > Dick Kusleika > MVP-Excel > www.dailydoseofexcel.com > > >

Re: populating spreadsheet with data from Access "Dick Kusleika" <dkusleika[ at ]gmail.com> 22.12.2005 00:57:19 TLuebke wrote: [Quoted Text] > Thanks Dick, > I'm new to excel so please bear with me. I hadn't thought of > bringing the data into a hidden worksheet as an interim step. That > does make sense. Thinking out loud here: > 1. Create a userform that prompts for the semester code and a name > for the new worksheet. ' as per tutorial > 2. That information is used to create the hidden worksheet named > "SemestercodeHidden" and pull data into it. 'as per ado tutorial > 3. I suspect I should have a "template" worksheet with all the calcs > on it. VBA code copies that and renames it " New Name" > 4. ???? how do I move the essential data from the Hidden worksheet > into the New worksheet, populating the appropriate columns/rows? > > Would you happen to have another tutorial hiding someplace ;) > Todd Here's what I'm thinking: Create a two sheet workbook. On the first sheet, manually create an external data table that has the information you need. If you need to limit the external data, or change it for different situations, but the underlying SQL stays the same, then use a parameter query as described here http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters. You can define a cell on the second sheet where the user would enter data, like a semester code, that changes the data in the external data table. On the second sheet, you use VLOOKUP formulas or INDEX/MATCH formulas to retrieve the data to the appropriate place on the sheet. It's hard to be more specific without knowing what the external data table looks like or what information you want out of it. This is a non-VBA solution which will be a lot better, I think, than code that creates queries or imports sheets. If you need more help on the formulas, or anything else, you can post some sample data. Also, feel free to email a sample workbook to me. -- Dick Kusleika MVP-Excel www.dailydoseofexcel.com