Linking an Excel worksheet to an Access table

[Sitara Lal]

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.

 

[Ken Snell]

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.

 

[Sitara Lal]

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]

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>

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

 

[Rob Schneider]

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

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

 

[Tonín]

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

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

 

[Tonín]

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.

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