Integrate spreadsheets in Access

[Trond Hoyem]

Hi

I am in the beginning of creating a DB for advanced calcuation of project-offers. I am fully aware of the existence of these kind of things, but so far I have not been able to find anything that really support all my needs, and so I am doing it the hard way.....

Today's calculations consists of a lot of different Excel-sheets for different kind of calculations within the project, and some of them ar getting big and difficult to both use and maintain. BUT spreadsheets do have some really nice flexibility....

Is there a way to integrate a spreadsheet from Excel into Access? Inside the sheet I would like to do free calculations, and then the result can be just presented as a line in the offer with simple text and price (like if one adds a product). And when opnening the line, the spreadsheet shuold be opened for review and recalculation -> closing it updates the line in the offer.

Anyone have some pointers on where to start?

 

[isladogs]

The structure of Excel files is very different from typical Access tables.
Although you can link to an Excel file from Access, it is not editable.
The only ways of editing Excel files from Access are
a) automation using VBA. Doing this is powerful but may be complex if you are a beginner
b) using a web browser control and opening the spreadsheet within that. For example:

 

[theDBguy]

Hi. Welcome to AWF!

Also, try to look up Excel Automation.

 

[Trond Hoyem]

Thx for answers.

I will have a look at this and then there will be more questions, I am sure....

 

[RogerCooper]

You should also review the spreadsheets and see if you can emulate them entirely in Access. You can do many Excel-like things in Access by using dlookup and formatted Reports.

 

[Pat Hartman]

You sound like someone who understands Excel but not Access. You will be veeeeeeeeeeeeeeerry unhappy with Access if you assume it is like Excel. To convert your spreadsheets, the first thing you need to do is to stand back and analyze the data so you can normalize it. In Excel, your data and presentation layer are merged. That means that when you expand it vertically or horizontally, you end up having to modify formulas. You will find, if you give it a chance, that a relational database is far better at handling data and will NOT require modification to anything if you need to add new customers/products/ or whatever your workbook is all about. Calculations work on sets of data and sets are defined by using queries with or without a select clause.

Do not attempt to keep the Excel workbooks as the "master" data. Access must be the "master" for the whole app to work. Do your data entry in Access. Then if you can't get the look you want with an Access report, export the data to a spreadsheet where you will have more flexibility. You can do this either by creating templates with a tab that contains replaceable data or you can create the whole workbook using VBA in Access. The first method is far simpler if you are not experienced with VBA.

 

[Isaac]

Hi

I am in the beginning of creating a DB for advanced calcuation of project-offers. I am fully aware of the existence of these kind of things, but so far I have not been able to find anything that really support all my needs, and so I am doing it the hard way.....

Today's calculations consists of a lot of different Excel-sheets for different kind of calculations within the project, and some of them ar getting big and difficult to both use and maintain. BUT spreadsheets do have some really nice flexibility....

Is there a way to integrate a spreadsheet from Excel into Access? Inside the sheet I would like to do free calculations, and then the result can be just presented as a line in the offer with simple text and price (like if one adds a product). And when opnening the line, the spreadsheet shuold be opened for review and recalculation -> closing it updates the line in the offer.

Anyone have some pointers on where to start?

My thoughts on what you might want to consider:

1) If you really want to take advantage of a database due to its efficiency, data integrity, relational data, scalability, multi-user, and you've realized that your business data belongs in a place where it can be correctly managed, protected and shared, and the numerous Excel workbooks are getting too difficult to manage: Then perhaps you should still go ahead and use Excel for what it's best at: Number crunching. But, then have a mechanism to import certain 'finalized' data into the actual database. Continue to use both tools.......Separately, with a little bit of interoperability that you'll build into the Access side of things to occasionally add data from your Number Cruncher xlsx's into the official system of record...the database.

2) If you do not really need the database, but you just need to clean up the mess in Excel somewhat while still continuing to use Excel primarily, there are certainly use cases where that is appropriate. A database is not necessarily the answer. There are numerous ways to provide automation, enforce business rules, validation, convenience and even GUI interface using just Excel.

Both paths require a fair amount of learning - one with a stronger emphasis on relational database principles, one with an emphasis on VBA coding.

Neither will be quick and easy to get to where you want to be if you are new to coding, VBA, and Access.

What worked for me when I was starting out this journey was to begin by making "enhancements" to the Excel scenario. I knew Excel was probably not going to be the final, down-the-road business solution.....But I found it a very effective learning path to begin there, getting a feel for what could be done. It was also easier to deploy such changes in a realistic work scenario - with people trusting my 'enhancements' little by little, rather than a brand new person to Access trying to create a full-fledged, rich-featured Database+Frontend tool all in one "go".

Without knowing exactly what your pain points are, that's about as specific as I can get but hopefully gets you thinking. I spent a year or two making Excel Automation enhancements to business processes (major improvements), before worrying about an Access database. There's nothing wrong with going straight to Access if you definitely feel this is where you need to be, just plan for it to take time - take an academic approach to it, never a quick 'n dirty shortest path to "it works without an error". (no matter which you do).

 

[Trond Hoyem]

Pat and Isaax, thanks for your replies!

I have put some thoughts into this matter over the years. I have also created a few similar tools in Access before, but for the one I am considering now, I would like to add something new because of big changes in the type of projects we are calculating now, opposed to earlier.

Before, the Access tool more or less needed a customer table, articles, offers and offer lines (with of course some added things around this). Now, I also want to do a much more advanced project calculation, where there are system devices and a lot of additional devices that will be supplied by others in the project, but that I still need to add information about in my own offer. It is simply put a lot more complicated to arrive at the final price for my offers, and I need a tool that help me keep track of everything that I must consider for the grand total on the bottom of my offer-letter.

The spreadsheet integration was more or less a thought to save me some VBA coding, if there was an easy way to use a spreadsheet inside the DB. I guess I will have to check a little more on the different options you guys mention, and see what will be the best way for me.

To quickly explain the way it is done today:
- I have a massive Excel tempplate from one supplier that helps calculate their products (but not the work hours).
- I have an Excel template for calculating the work hours for one of the systems.
- For another system (used in same project normally), I use a separate tool
- Finally, I need to summarize all the different tools in one Excel sheet to get the grand total.

As you can imagine, there is a very good chance that I miss some sums from one tool into the grand total. In my quality check before I send out the offer, I many times find errors in the summary, and I dread the day I miss the errors and loose tens of thousand Euros on a project due to summary-faults.

 

[Pat Hartman]

The spreadsheet integration was more or less a thought to save me some VBA coding, if there was an easy way to use a spreadsheet inside the DB.

NO.

As you can imagine, there is a very good chance that I miss some sums from one tool into the grand total.

That is a major reason for doing this in Access. You don't have to modify every single row/column to fix up expressions.