If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking an Excel worksheet to an Access table
Using Office 2003, what would be the best way to accomplish this:
I have an Access table which has fields such as Product_ID, Product_Description, Product_Image etc. To generate a quotation for a customer in Excel, I would like to create a worksheet where I enter the Product_ID in one cell so that the Product_Description, Product_Price etc. loads automatically into adjacent cells on the same row of the Excel worksheet. What is the best way to accomplish this? Looking at the Excel Help online, I only found a 'copy and paste' solution, which is quite impractical especially if I do not wish to grant access to the entire database to users of the spreadsheet. Thanks for your help. |
#2
|
|||
|
|||
Linking an Excel worksheet to an Access table
You want the spreadsheet to get its values from the ACCESS table, but the
users are not to see the database? Why not let the users use ACCESS and have a form that they use in it? That would be a preferred way to do this. Otherwise, you're going to need significant VBA code in the spreadsheet that will have to obtain the data from the ACCESS tables "under the covers". This is a hard way to go. -- Ken Snell MS ACCESS MVP "Sitara Lal" wrote in message ... Using Office 2003, what would be the best way to accomplish this: I have an Access table which has fields such as Product_ID, Product_Description, Product_Image etc. To generate a quotation for a customer in Excel, I would like to create a worksheet where I enter the Product_ID in one cell so that the Product_Description, Product_Price etc. loads automatically into adjacent cells on the same row of the Excel worksheet. What is the best way to accomplish this? Looking at the Excel Help online, I only found a 'copy and paste' solution, which is quite impractical especially if I do not wish to grant access to the entire database to users of the spreadsheet. Thanks for your help. |
#3
|
|||
|
|||
Linking an Excel worksheet to an Access table
Ken,
Not allowing users to see the Access database is a minor issue and can be ignored if need be. However, I would prefer to use Excel and have the spreadsheet get its values from Access because (a) not all users have Office Pro and hence do not have Access on their computers, (b) calculations have to be performed which are easier and more intuitive in Excel and (c) most users are far more familiar with Excel than Access I would have thought there would be a fairly simple way to link an Excel spreadsheet to an Access database without having to invest in significant VBA code! Can you help if keeping the Access table private is no longer an issue? Thanks "Ken Snell" wrote in message ... You want the spreadsheet to get its values from the ACCESS table, but the users are not to see the database? Why not let the users use ACCESS and have a form that they use in it? That would be a preferred way to do this. Otherwise, you're going to need significant VBA code in the spreadsheet that will have to obtain the data from the ACCESS tables "under the covers". This is a hard way to go. -- Ken Snell MS ACCESS MVP |
#4
|
|||
|
|||
Linking an Excel worksheet to an Access table
The only method of which I know for EXCEL to get its data from ACCESS when
in EXCEL would be to open a recordset in EXCEL VBA that is based on the ACCESS table (by the way, will the ACCESS database be placed on a server where all people can access it?) and then to find the appropriate record in the recordset and write values to the EXCEL cells. It's not "significant" VBA code, but it's more than just a few lines. Perhaps you might post your question in an EXCEL newsgroup (microsoft.public.excel) first (I am more skilled in ACCESS than EXCEL) and see if the MVPs there have an easier suggestion. If not, post back here (microsoft.public.access) and we'll see what we can do. -- Ken Snell MS ACCESS MVP "Sitara Lal" wrote in message ... Ken, Not allowing users to see the Access database is a minor issue and can be ignored if need be. However, I would prefer to use Excel and have the spreadsheet get its values from Access because (a) not all users have Office Pro and hence do not have Access on their computers, (b) calculations have to be performed which are easier and more intuitive in Excel and (c) most users are far more familiar with Excel than Access I would have thought there would be a fairly simple way to link an Excel spreadsheet to an Access database without having to invest in significant VBA code! Can you help if keeping the Access table private is no longer an issue? Thanks "Ken Snell" wrote in message ... You want the spreadsheet to get its values from the ACCESS table, but the users are not to see the database? Why not let the users use ACCESS and have a form that they use in it? That would be a preferred way to do this. Otherwise, you're going to need significant VBA code in the spreadsheet that will have to obtain the data from the ACCESS tables "under the covers". This is a hard way to go. -- Ken Snell MS ACCESS MVP |
#5
|
|||
|
|||
Linking an Excel worksheet to an Access table
You can use the Access Runtime to distribute the application so that
users don't have to have Access to run the app, and you have a better development platform for development/distribution than Excel. If users want to use Excel for their own, that's fine; but as a application environment, you'll have lots more problems and complexity than if you just do the whole think in Access. Hope this is useful to you. Let us know. rms Sitara Lal wrote: Ken, Not allowing users to see the Access database is a minor issue and can be ignored if need be. However, I would prefer to use Excel and have the spreadsheet get its values from Access because (a) not all users have Office Pro and hence do not have Access on their computers, (b) calculations have to be performed which are easier and more intuitive in Excel and (c) most users are far more familiar with Excel than Access I would have thought there would be a fairly simple way to link an Excel spreadsheet to an Access database without having to invest in significant VBA code! Can you help if keeping the Access table private is no longer an issue? Thanks "Ken Snell" wrote in message ... You want the spreadsheet to get its values from the ACCESS table, but the users are not to see the database? Why not let the users use ACCESS and have a form that they use in it? That would be a preferred way to do this. Otherwise, you're going to need significant VBA code in the spreadsheet that will have to obtain the data from the ACCESS tables "under the covers". This is a hard way to go. -- Ken Snell MS ACCESS MVP |
#6
|
|||
|
|||
Linking an Excel worksheet to an Access table
I'm a Spanish Excel and Access user and I'm hardly sure about the actual
words or names in the English Excel menus, but please let you try next steps in Excel: Open "Data" menu Select "Getting external data" (just the option under "Dinamic tables and graphs" option - I'm not sure about the names in English as I told you) Select "New query to a data base" It will open the MS Query wizard (so easy to use). Just select "MS Access Database" in the list that it will appear. Then indicate where is your mdb file. Finally, select that table or query you are interested. Select the fields ... and not much more. Format and other options can be established as you will see. Try different options till you get what you need. Hope I helped you ':-) Kind regards Tony "Sitara Lal" escribió en el mensaje ... Ken, Not allowing users to see the Access database is a minor issue and can be ignored if need be. However, I would prefer to use Excel and have the spreadsheet get its values from Access because (a) not all users have Office Pro and hence do not have Access on their computers, (b) calculations have to be performed which are easier and more intuitive in Excel and (c) most users are far more familiar with Excel than Access I would have thought there would be a fairly simple way to link an Excel spreadsheet to an Access database without having to invest in significant VBA code! Can you help if keeping the Access table private is no longer an issue? Thanks "Ken Snell" wrote in message ... You want the spreadsheet to get its values from the ACCESS table, but the users are not to see the database? Why not let the users use ACCESS and have a form that they use in it? That would be a preferred way to do this. Otherwise, you're going to need significant VBA code in the spreadsheet that will have to obtain the data from the ACCESS tables "under the covers". This is a hard way to go. -- Ken Snell MS ACCESS MVP |
#7
|
|||
|
|||
Linking an Excel worksheet to an Access table
I'm sorry. I forgot just this part of your former question:
To generate a quotation for a customer in Excel, I would like to create a worksheet where I enter the Product_ID in one cell so that the Product_Description, Product_Price etc. loads automatically into adjacent cells on the same row of the Excel worksheet Regarding to that. Once you got your query in Excel as I explained you below, you can "modify/edit" the query (click the secondary mouse button on the query cells). Open the query by means of MS Query [unselect wizard option] and add a parameter to the field "Product_ID" (same way as Access). After that, exit and return data to Excel. Again on the spreadsheet, click once more the secondary mouse button on the query cells and select "Parameters". Then you will be able to indicate Excel that read the parameter value from a certain cell. This cell should be that one where you expect the users write the Product_ID. It will work just as a parameter query in Access. By the way, remember to hide the Product_ID field in MS Query. This is because user will write the value of the Product_ID cell and you do not need to show the Product_ID in another additional cell. [I'm explaining all that to you just remembering the steps. If I missed something I would like appologize in advance :-)]. Definitely, my suggestion is you make trials by yourself and you will find the best solution for your needs, I'm totally sure. :-) "Tonín" escribió en el mensaje ... I'm a Spanish Excel and Access user and I'm hardly sure about the actual words or names in the English Excel menus, but please let you try next steps in Excel: Open "Data" menu Select "Getting external data" (just the option under "Dinamic tables and graphs" option - I'm not sure about the names in English as I told you) Select "New query to a data base" It will open the MS Query wizard (so easy to use). Just select "MS Access Database" in the list that it will appear. Then indicate where is your mdb file. Finally, select that table or query you are interested. Select the fields ... and not much more. Format and other options can be established as you will see. Try different options till you get what you need. Hope I helped you ':-) Kind regards Tony "Sitara Lal" escribió en el mensaje ... Ken, Not allowing users to see the Access database is a minor issue and can be ignored if need be. However, I would prefer to use Excel and have the spreadsheet get its values from Access because (a) not all users have Office Pro and hence do not have Access on their computers, (b) calculations have to be performed which are easier and more intuitive in Excel and (c) most users are far more familiar with Excel than Access I would have thought there would be a fairly simple way to link an Excel spreadsheet to an Access database without having to invest in significant VBA code! Can you help if keeping the Access table private is no longer an issue? Thanks "Ken Snell" wrote in message ... You want the spreadsheet to get its values from the ACCESS table, but the users are not to see the database? Why not let the users use ACCESS and have a form that they use in it? That would be a preferred way to do this. Otherwise, you're going to need significant VBA code in the spreadsheet that will have to obtain the data from the ACCESS tables "under the covers". This is a hard way to go. -- Ken Snell MS ACCESS MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
Keeping the Pivot View from Access in Excel | Ferd | General Discussion | 0 | June 25th, 2004 02:22 AM |
Copy table text to Excel worksheet | Regina | New Users | 1 | June 23rd, 2004 04:51 PM |
access table from an excel spreadsheet | trey braid | General Discussion | 2 | June 16th, 2004 01:30 PM |
Copying a table from Word 2000 to Excel 2000 | Mike | General Discussion | 1 | June 15th, 2004 08:54 AM |