8.2. Changing Data in an Existing Report

When changing data in an existing Excel report from Access, you have a few different scenarios to consider. In the first scenario, you fill in a few pieces of data in different places on the worksheet. In the second scenario, you replace data that resembles a recordset. In the final scenario, you do a combination of the two. A combination could be a situating in which you change certain variables based on the number of records in a recordset, or possibly a situation in which you bring in a recordset and take other values from the database. A good example of the combination scenario is a profitability model, where sales are loaded from a recordset and specific pricing is entered as separate data points.

Regardless of the scenario, the first few steps are the same when using VBA from Access. Just to open the existing Excel worksheet, you need an Excel Application object and a Workbook object, and I suggest using a string variable to hold the file name. You also need a reference to Excel using Tools References from Design View in the Access module. The code in Example 8-1 opens an existing workbook. Example 8-2 is designed as a procedure called from another procedure. I pass the variables ByRef, so this assumes that I have Excel Application and Workbook objects declared in the calling procedure. I would just use the code without calling a different procedure in a production environment, but this is just to demonstrate the concept of opening a workbook. See the calling procedure in Example 8-2 and note that the filename and path used in the GetXLWB call will be different on your computer.

Example 8-1. Generic procedure to open an existing workbook

 Public Sub GetXLWB(fname As String, ByRef xlapp As Excel.Application, _                    ByRef xlwb As Excel.Workbook) Set xlapp = New Excel.Application Set xlwb = xlapp.Workbooks.Open(fname) End Sub

Example 8-2. Procedure that calls the procedure in 8-1 and returns a result

 Public Sub xlCallingProc(  ) Dim xlapp As Excel.Application Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Call GetXLWB("C:\Devwork\Chapter8workbook.xls", xlapp, xlwb) Set xlws = xlwb.Sheets("Sheet1") MsgBox "The value in cell H5 is " & xlws.Range("H5").Value, vbInformation xlwb.Close xlapp.Quit Set xlws = Nothing Set xlwb = Nothing Set xlapp = Nothing End Sub

If you look back at Figure 8-1, it shows a worksheet that calculates a total margin housed in cell H5. The procedure in Example 8-2 opens this workbook and returns the value in cell H5 in a message box.

At this point, you know how to open a workbook and read a value from a worksheet. Let's look at a case, using the code in Example 8-3, where you want to open up each workbook in a directory of several workbooks and pull a recordset from the database based on the information in a cell. Then print the report if the PrintOpt variable's value is trUE.

Example 8-3. Processing multiple workbooks

 Public Sub MultiProc(pname As String, printopt As Boolean)   ' passes a parameter to a recordset to build a report Dim xlapp As Excel.Application Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim xlrng As Excel.Range Dim fname As String Dim param As String Dim x, y, z, a, b, c As Integer Dim db As DAO.Database Dim qry As DAO.QueryDef Dim rs As DAO.Recordset Set db = CurrentDb Set xlapp = New Excel.Application xlapp.Visible = True fname = Dir(pname & "*.xls") While fname <> ""   Set xlwb = xlapp.Workbooks.Open(pname & fname)   Set xlws = xlwb.Sheets("ReportParameters")   Set qry = db.QueryDefs("qry_ExcelReport")   param = xlws.Range("B2").Value   qry.Parameters(0).Value = param   Set rs = qry.OpenRecordset   Set xlrng = xlwb.Sheets("ReportData").Range("A2")   xlrng.CopyFromRecordset rs   x = rs.RecordCount   rs.Close   Set xlws = xlwb.Sheets("Report")   xlws.Cells(3, 1).Value = "Category"   xlws.Cells(3, 2).Value = "Units"   xlws.Cells(3, 3).Value = "Sales"   Set xlrng = xlws.Range(xlws.Cells(3, 1), xlws.Cells(3, 3))   xlrng.Font.Bold = True   Set qry = db.QueryDefs("qry_ExcelProducts")   qry.Parameters(0).Value = param   Set rs = qry.OpenRecordset   Set xlrng = xlws.Range("A4")   xlrng.CopyFromRecordset rs   y = rs.RecordCount   For b = 4 To y + 3     Set xlrng = xlws.Cells(b, 2)     xlrng.FormulaArray = "=Sum((ReportData!R2C2:R" & x + 1 & _                      "C2=Report!R" & b & "C1)*ReportData!R2C4:R" & _                      x + 1 & "C4)"     Set xlrng = xlws.Cells(b, 3)     xlrng.FormulaArray = "=Sum((ReportData!R2C2:R" & x + 1 & _                      "C2=Report!R" & b & "C1)*ReportData!R2C5:R" & _                      x + 1 & "C5)"   Next b   Set xlrng = xlws.Range(xlws.Cells(4, 2), xlws.Cells(y + 3, 2))   xlrng.NumberFormat = "#,##0"   Set xlrng = xlws.Range(xlws.Cells(4, 3), xlws.Cells(y + 3, 3))   xlrng.NumberFormat = "$0.00"   rs.Close   z = y + 5   xlws.Cells(z, 1).Value = "Center"   xlws.Cells(z, 2).Value = "Units"   xlws.Cells(z, 3).Value = "Sales"   Set xlrng = xlws.Range(xlws.Cells(z, 1), xlws.Cells(z, 3))   xlrng.Font.Bold = True   z = z + 1   Set qry = db.QueryDefs("qry_ExcelCenters")   qry.Parameters(0).Value = param   Set rs = qry.OpenRecordset   Set xlrng = xlws.Cells(z, 1)   xlrng.CopyFromRecordset rs   a = z + rs.RecordCount   For b = z To a - 1     Set xlrng = xlws.Cells(b, 2)     xlrng.FormulaArray = "=Sum((ReportData!R2C1:R" & x + 1 & _                      "C1=Report!R" & b & "C1)*ReportData!R2C4:R" & _                      x + 1 & "C4)"     Set xlrng = xlws.Cells(b, 3)     xlrng.FormulaArray = "=Sum((ReportData!R2C1:R" & x + 1 & _                      "C1=Report!R" & b & "C1)*ReportData!R2C5:R" & _                      x + 1 & "C5)"   Next b   Set xlrng = xlws.Range(xlws.Cells(z, 2), xlws.Cells(a - 1, 2))   xlrng.NumberFormat = "#,##0"   Set xlrng = xlws.Range(xlws.Cells(z, 3), xlws.Cells(a - 1, 3))   xlrng.NumberFormat = "$0.00"   xlws.Columns.AutoFit   If printopt Then xlws.PrintOut   xlwb.SaveAs "C:\Reports\" & fname   xlwb.Close   Set xlwb = Nothing   fname = Dir Wend Set xlrng = Nothing Set xlws = Nothing xlapp.Quit Set xlapp = Nothing rs.Close qry.Close Set rs = Nothing Set qry = Nothing Set db = Nothing End Sub

I should point out a few things here. First, I use an array function instead of the SumIf function. This technique was shown in Chapter 6. This example checks for only one criterion, so I could have easily used SumIf. However, by using Sum in an array function, you can check for multiple criteria by multiplying each Boolean result together and then multiplying by the range of values. For each row, if the Boolean result is FALSE, the row nets to zero because anything multiplied by zero is zero. If the value is trUE, it multiplies the result by 1, which doesn't change the result. If this seems difficult to follow, refer to Figure 6-7 and Example 6-6 to see how it works in more detail. It is important to note that in VBA, the value of vbTrue is -1, so you can't use this logic in VBA as you can in the Excel GUI. Also note that I use several Integer variables to keep track of where I am on the worksheet after pasting each recordset.

Here is how the procedure works. By passing a path name, the DIR function brings up a filename that matches the criterion. Move to the next result by calling the DIR function again without any parameters. So, this procedure loops through each Excel workbook in a directory and looks in a cell to determine which report needs to be run. It then passes this value as a parameter to several queries, the results of which are placed in one worksheet that houses data and another worksheet that is the actual report. Next, it builds the array formulas and formats the cells. Finally, if you said trUE to the print option, it prints the worksheet and saves the report in a directory called C:\Reports\. If you do not have this directory, you have to update it to one on your system. I show how to build a GUI to run a procedure like this in Access (Chapter 11) or Excel (Chapter 10), but for now, to check out how this works, type in the following line in the Immediate Window in the Visual Basic Editor in Access:

     Call multiproc("C:\DevWork\Chapter8\",TRUE)

Again, this assumes that you have Excel documents in a directory called C:\DevWork\Chapter8\. If there are no Excel files in that directory, it fails to do anything. It also assumes that the Excel workbooks have a sheet called "ReportParameters."

Note that I have reused the xlws variable several times; I have also referred to other sheets directly without setting the worksheet variable equal to the other worksheet. I could have just as easily created three separate worksheet variables, but it was unnecessary for this explanation. The result of this procedure is shown in Figure 8-2.

Because of the way this workbook is set up, you could pull the line of business names from the database and open one Excel Workbook as a template. Then just update the line of business names on the ReportParameters worksheet and run the rest of the code. I actually suggest using one Excel file as a template, but I wanted to introduce the DIR function. I use the DIR function to process Excel documents and load hundreds of lines of data in Access databases for budgeting and other business purposes. If you have a consistent set of workbooks to process, being able to cycle through all of the Excel files in a folder is very helpful.

You may also notice that while Example 8-2 uses a report template, the formulas are still created by Access. You can just as easily have a template where you only update data and do not create formulas. In this case, it is necessary to rewrite the formulas because the number of categories can change between each workbook.