Access / Excel data sharing - any opinions?

[kjclark]

I am working on a portfolio management system in which we have a really involved NPV calculation (3 different worksheets) in Excel to fill out for each project in Access. Not all of the NPV data needs to be seen in Access, only the summary data so we can chart our investments, revenue, etc.

Does anyone have suggestions on the best way to do this? I have been reading about automation, DDE, and linking to excel, but I am a fairly new user and I don't know the best way to go after it.

I will need to identify which NPV spreadsheet to pull data from by the project ID assigned in Access, so I need to get the Project ID over to Excel, fill out the NPV information, and then get the summary info back to Access.

Any opinions would be great!

Thanks!
Karla

 

[MichaelRed]

Being a huge fan of NOT Excel, I would almost always opt for the totally db version. If you use access or VB (or any? MS product?) you can add the Excel Library to the app and use the functions (like NPV) directly from the "Real" programmers' program. Fewer 'cooks' stiring the various pots.

An alternative which I often use for the simpler function in excel is to just re-vreate them as procedures. NPV (and variations) are particularly easy.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[kjclark]

Thanks for your thoughts MichaelRed!