Hi

As a Consultant in Business Data Management I often have to develop applications in Excel to replace applications developed in Access.

Are you planning to UPGRADE to Access?
Are you working with Access and why?
Do you know the SUMPRODUCT function in Excel to automate all your reports?
Do you know about the VARIANT in VBA for Excel that allows you to work with millions of data and execute millions of calculations in seconds?
Do you know about SQL in VBA that allows you to extract whatever data from whatever central database, ERP or finance and accounting applications?

See my article on Access and Excel at:

http://excel-vba.com/excel-access.htm

Looking forward to reading you thoughts

Howdy, Pierre. One caution about Variant in VBA is that it can easily become unmanageable, taking on the latest "value" of what precedes, or may acquire an unexpected value. So, sparing use of Variant can work well, overuse can lead to serious troubleshooting issues.

Shades

I use the VARIANT to avoid working on the worksheet itself.

I bring the whole worksheet into a variable of the Variant type with:

varMySheet = Sheets("SOansSO").CurrentRegion

I then do what I have to do and bring the results back to the worksheet:

Sheets("SOansSO").CurrentRegion = varMySheet

And I never had a problem.

I have just finished two complex applications for Bombardier (Financial Analysis) and KPMG (Reporting application generating 2,600 reports using 26,000 text files) using this approach.

For example, going down 50,000 cells to enter a "1" in each with a For/Next on the worksheet takes 33 seconds in a Variant it takes less than one.

I always look @ what the Customer Want them decide what product to use

I have just finish one

I use MSaccess get the Data
them MSaccess Creates Excel file
msacess Send the Excel files out the user to there customer who Full them In
send back Msaccess them read the excel file and put the Data back in the data base

my rules
excel does the Number
and
MSaccess does the Databasing

Myle

I am certified (MOUS) in both Excel and Access.

My client has the final word and I have to make sure that he is well informed before making a decision.

Developing in Access is more expensive then developing in Excel and the client becomes more dependant on the COnsultant if the development is made in Access.

In most of my projects the data is in some central database.

I have developed a sophisticated costing application for SCI (Houston) where millions of records are extracted from the central database using multiple criteria in VBA for Excel.

The pricing workbooks are then sent to 500 directors who do the costing and who send back their workbooks to the head office.

The data is then reformated to be uploaded in the central database (HMIS)

There are so many ways to get the data directly in Excel from any central database, ERP and other central applications. In some of the applications I even work with stored procedures executed on the server through VBA.

Basically when it can be done in Excel I avoid Access and this occurrence has never happened in the last 15 years.

The great aspect of using Excel is that I can mentor my clients in Excel and they can develop reports themselves after a short while.

They are better than me at developing great reports because they know their data better than I.

They call me only if the applications are too complex for them and yet I am overloaded with complex and interesting projects