Excel to Access

[Bob]

I have an Excel spreadsheet with formulas that I would like to convert into
an Access db. The formulas are the important aspect of this. I have
imported the data (which loses the formulas) and tried linking the two which
works nicely except that data updates are only performed by entering into
Excel (at least that's how it works for me).

I could use the link arrangement if it's possible to update in Access as I
want to use the entry form benefits of Access. Is this possible and I just
can't link correctly?

Is it possible to import the formulas found in Excel to Access via
import/export? All the literature I read says it isn't.

Thanks!

 

[Ken Snell \(MVP\)]

Microsoft has turned off the ability to edit linked EXCEL spreadsheets from
ACCESS 2002 and 2003 and 2007 -- MS lost a lawsuit over the technology. So
you cannot edit data directly any more in these versions.

I don't know of any way to import worksheet formulas from EXCEL worksheets
into ACCESS, but then it's not a desirable thing to do in ACCESS anyway. One
uses queries in ACCESS to calculate the values that EXCEL would do with
those formulas. So what you really want to do is to write queries in ACCESS
that include calculated fields to replace the cells/columns with formulas in
the EXCEL worksheets.

 

[Bob]

Thanks Ken. Leave it to me to want something blocked by a lawsuit!

I guess I have to learn how to make complicated Queries now.

Thanks again!

 

[MH]

What functions are used in the formulas?

Excel has functions that Access does not have natively but there's always a
way.

MH

 

[Jeff Boyce]

In addition to Ken's observations about blocked updating of Excel data, and
the use of queries in Access to do calculation, be aware that Access is not
a big spreadsheet. If you haven't done so already, spend some time
reviewing the topic of normalization. The way you'd organize data in Excel
will cause both you and Access considerable headache if you try to organize
your data in Access the same way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

And one more thing...

If there is a function in Excel that you want to use in Access, you can
reference the Excel objects. But the way the data is organized will still
be an issue.

Regards

Jeff Boyce
Microsoft Office/Access MVP