Interacting with Microsoft Excel

Microsoft Excel is a great program for playing what-if scenarios with data because it lets you plug data and formulas into cells in whatever manner you want. Excel isn't good, however, at managing large volumes of data. For large volumes of data, you need a database like Microsoft Access.

Microsoft Access can certainly do any math calculations that Excel can do. Playing with what-if scenarios with data in Access isn't so easy, though, because you need to get queries and/or forms involved. It's just plain difficult to experiment with what-if scenarios in Access.

Sometimes, the data you need for your worksheet might come from an Access database. For example, you might manage all your orders in an Access database. Every now and then you want to grab the total sales from all your orders into a worksheet and use that value to play around with your data.

You could, of course, just open Excel and type in the total sales value — or even copy and paste it from some form in Access. Optionally, you could automate the whole thing by creating a button on some Access form that opens the worksheet and plugs in the total sales amount for you. Look at an example of Automation that does just that.

Creating the Worksheet

The first step is to create an Excel worksheet that contains a blank cell that gets its values from Access. Give the cell a name so that you can refer to that cell by name in VBA code. For example, in Figure 14-10, we create a worksheet named MySheet.xlsx. Cell B3 in that worksheet is named FromAccess.

Figure 14-10:

Sample Excel worksheet with a cell named FromAccess.

Figure 14-10:

Sample Excel worksheet with a cell named FromAccess.

Cell named FromAccess

To name a cell or range in Excel, click the cell or select the cells that you want to name. Then type a name into the Name box (where FromAccess appears in Figure 14-10) and press Enter. For more information, search the Excel Help for name cells.

For the sake of example, we save that worksheet in the root of our C: drive with the name MySheet.xlsx. The complete path to that worksheet is

C:\MySheet.xlsx

That's important to know because VBA can't find the worksheet without the complete path and filename. When you're creating files that you'll open from VBA, make sure that you know where they're located.

Creating a query and a form

After you create and save the worksheet, you can close Excel and open Access. In Access, you need to create a query that can do the calculations and also create a form that can display the appropriate value to copy to the Excel sheet. For this example, we create a totals query in Access that totals all the sales for each product from tables named Products and Order Details in a sample database. Figure 14-11 shows that query, named Order Summary Totals Qry, in Design view (left) and Datasheet view (right).

[?p Order Summary Totals Qry

- a x

Products

Order Details

9

*r

*

*

^ PrüducüD

-V

OrderlD

OurProductID

=

ProductID

ProductName

Qty

PrüductPhoto

UnitPrice

Selling Price

Taxable

■r

< LÜLI

■ShoWv Criteria

ProducEWa.me Prosta^. ' Group By Ascending 0

iTotSiSafes: [QtyHUnitFrtce]

0 0

Post a comment