Advice needed on Access-Excel interoperability 
Author Message
 Advice needed on Access-Excel interoperability

Hi,

As my resources (time & selfmotivation) are limited, I'd like to try to
benefit from your experience.

I'm building an accounting managment application in Access.
The information of about 1000 customers will be handled for several
fiscal years.
Reports, mailing and such are built with Access.

But, previous work has been carried out in Excel
pe. Calculation of Employee Tax Benefits for use of company car, housing
,...
The worksheet and its formulas are rather complex.

I would to integrate this workbook in Access, as the input data is now
stored in Access.
Up till now, the data was manually entered in the Excel sheet.

I hesitate to port the worksheet to Access, as the formulas cannot be
easily converted to VBA code.
But then, One user interface, uniform Reports & mailing

To keep them seperated, presents the user with two diferents
interfaces/programs to operate. It also makes for less transparent
reporting/mailing.

The choices are:

1. Port the sheet in VBA code
  :) One interface           :( loss of ease of adaptation

2. In Access, create export files to be parsed in Excel
  :( Separate programs       :) excel formulas can be used

3. Use DDE/OLE stuff to integrate Access/Excel into Axcell ;)
  :( bad experience in the past with DDE & stability
  :( capable of handling data transfer load ?
  :) Working more efficient with your data (microsoft ad)

Before I rush off head first in one of these directons, I would your
suggestions/experiences on this subject.

--



Tue, 21 Jan 2003 03:00:00 GMT  
 Advice needed on Access-Excel interoperability

Hi Glen,

Quote:
> The choices are:

> 1. Port the sheet in VBA code
>   :) One interface           :( loss of ease of adaptation

True. Worksheets are always more flexible and faster to get calculating than
laboriously programming VBA or creating queries.

Quote:
> 2. In Access, create export files to be parsed in Excel
>   :( Separate programs       :) excel formulas can be used

Quite a good solution.

Quote:

> 3. Use DDE/OLE stuff to integrate Access/Excel into Axcell ;)
>   :( bad experience in the past with DDE & stability
>   :( capable of handling data transfer load ?
>   :) Working more efficient with your data (microsoft ad)

You're talking about circa 1993 or something. We haven't used DDE for this
job for a long time and stability of running Access and Excel is higher in a
Win 32 environment, particularly an NT / Windows 2000 environment.

Nowadays, we program in VBA, from either Excel or Access and manipulate data
using the Excel, Access or DAO/ADO object models. The way the whole bundle
works, VBA is a controller and it does not really care whether it is
manipulating Excel or Access as in terms of intimacy.

You can use VBA/Access and manipulate Excel behind the scenes, non visible,
until the last moment or never.

Ananda
http://go.to/ananda



Thu, 23 Jan 2003 03:00:00 GMT  
 Advice needed on Access-Excel interoperability

Quote:
> You're talking about circa 1993 or something. We haven't used DDE for this
> job for a long time and stability of running Access and Excel is higher in a
> Win 32 environment, particularly an NT / Windows 2000 environment.

It scared/bored me stiff at the time and it took a long time to thaw me
out :)

Quote:
> Nowadays, we program in VBA, from either Excel or Access and manipulate data
> using the Excel, Access or DAO/ADO object models. The way the whole bundle
> works, VBA is a controller and it does not really care whether it is
> manipulating Excel or Access as in terms of intimacy.

> You can use VBA/Access and manipulate Excel behind the scenes, non visible,
> until the last moment or never.

Thanks for your input.
I'll give the DAO/ADO a good staring at
--



Fri, 24 Jan 2003 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Access-Excel Interoperability: Switching between two Application windows via automation (VBA)

2. More problems with Access/Excel interoperability

3. Problems with Access/Excel interoperability

4. Excel & Word Interoperability

5. Excel & Word Interoperability

6. Req advice on how to activate Excel Macro using Access Module (Msoffice 97)

7. Req advice on how to activate Excel Macro using Access Module (Msoffice 97)

8. Advice needed from Access experts

9. Need design advice for service mngmnt sftwre based on outlook - access integration

10. Access & VB5 / 6 - Advice needed

11. Access filesize/concur user advice needed...

12. Need Advice: FoxPro Access from Visual Basic

 

 
Powered by phpBB® Forum Software