Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Excel and Access to import a single record from DB
friends,
I posted this in the "Excel Progromming" section but I didn;t get a response so I thought here. I have an Access database (Customers) and an Excel template (Quote Template). I would like to open the Quote template , click a command button within the workbook, ( I need a macro for this) and bring up a list of record (Customers) from the database. I would then like to be able to choose a single record from the list and have the appropriate info imported into the Excel template. I have searched the board rather extensively and not come across this scenario. Your help would be most appreciated. Thanks in advance Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Excel and Access to import a single record from DB
hi Steve,
I saw your post in programing. I was hoping someone more experience than me would respond. I have been transfering data between excel and access for about 8 years now and i am not aware of any way to do what your want. you can transfer data between the two quite easily but i have never been able to link the two at least not like linking 2 excel sheets. how would i do it? 1. Create a Microsoft Query (MSQ) to download the customer data(on a second sheet). You would need an access query for this and the MSQ would use the access query as it's data source. 2. Once the customer data has been downloaded, you would need a second MSQ, a second Access query and a second button. MSQ can use the contents of a cell as a selection parameter for the second access query. select the customer ID and run MSQ2. 3. once the selected customer data has been download (to a second area on the sheet), you would need an excel macro to populate your template which would transfer each field of the MSQ to the template one field at a time. the populate macro could be attached to the second command button to fire after the down load. except for the decision time to select the customer from MSQ1, the rest of the process should run quite quickly. Regards FSt1 "S Willingham" wrote: friends, I posted this in the "Excel Progromming" section but I didn;t get a response so I thought here. I have an Access database (Customers) and an Excel template (Quote Template). I would like to open the Quote template , click a command button within the workbook, ( I need a macro for this) and bring up a list of record (Customers) from the database. I would then like to be able to choose a single record from the list and have the appropriate info imported into the Excel template. I have searched the board rather extensively and not come across this scenario. Your help would be most appreciated. Thanks in advance Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Excel and Access to import a single record from DB
Steve -
I'd suggest a multi-step approach. Step 1 - Create an Excel validation list using your current customers. You can do it manually by copying data from Access or, if you like using VBA, you can code it. Search the MS Knowledge base for ADODB articles. There's plenty of examples showing you how to extract data from Access. The advantage to coding it is you can allow users to update the customer list at any time, simply by running your code, attached to a command button, for instance. Step 2 - Either code a query that uses the customer selected in the validation list or use the info at Nick Hodge's site to pull the remaining customer data via MS Query: http://www.nickhodge.co.uk/gui/datam...taexamples.htm Personally, I'd recommend coding the whole thing, since that gives you much better control over what happens and allows you to put in error handlers to deal with the unexpected. Good luck The code to do all of this is not difficult, but neither is it trivial "S Willingham" wrote: friends, I posted this in the "Excel Progromming" section but I didn;t get a response so I thought here. I have an Access database (Customers) and an Excel template (Quote Template). I would like to open the Quote template , click a command button within the workbook, ( I need a macro for this) and bring up a list of record (Customers) from the database. I would then like to be able to choose a single record from the list and have the appropriate info imported into the Excel template. I have searched the board rather extensively and not come across this scenario. Your help would be most appreciated. Thanks in advance Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Excel and Access to import a single record from DB
hi again,
had a thought. you would only need to run the first MSQ if you had new customers. if no new customers add to the access database then all you would need is to run the second MSQ to get the individual customer data. regards FSt1 "FSt1" wrote: hi Steve, I saw your post in programing. I was hoping someone more experience than me would respond. I have been transfering data between excel and access for about 8 years now and i am not aware of any way to do what your want. you can transfer data between the two quite easily but i have never been able to link the two at least not like linking 2 excel sheets. how would i do it? 1. Create a Microsoft Query (MSQ) to download the customer data(on a second sheet). You would need an access query for this and the MSQ would use the access query as it's data source. 2. Once the customer data has been downloaded, you would need a second MSQ, a second Access query and a second button. MSQ can use the contents of a cell as a selection parameter for the second access query. select the customer ID and run MSQ2. 3. once the selected customer data has been download (to a second area on the sheet), you would need an excel macro to populate your template which would transfer each field of the MSQ to the template one field at a time. the populate macro could be attached to the second command button to fire after the down load. except for the decision time to select the customer from MSQ1, the rest of the process should run quite quickly. Regards FSt1 "S Willingham" wrote: friends, I posted this in the "Excel Progromming" section but I didn;t get a response so I thought here. I have an Access database (Customers) and an Excel template (Quote Template). I would like to open the Quote template , click a command button within the workbook, ( I need a macro for this) and bring up a list of record (Customers) from the database. I would then like to be able to choose a single record from the list and have the appropriate info imported into the Excel template. I have searched the board rather extensively and not come across this scenario. Your help would be most appreciated. Thanks in advance Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Excel and Access to import a single record from DB
Thanks guys for the responses.
I guess I,ve got a bit more research to do. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Access into Excel | Excel Discussion (Misc queries) | |||
Obtaining a single Access record from Excel input | Links and Linking in Excel | |||
Single record | Excel Discussion (Misc queries) | |||
record an excel macro, save on network so that everyone can access | Excel Discussion (Misc queries) | |||
Export Access Record to Specific Cells in Excel | Excel Discussion (Misc queries) |