PDA

View Full Version : Code failure to calculate totals.



krishnak
04-10-2013, 10:38 AM
When the Form is triggered, the Access application will open the Excel worksheet and populate the data in the specified columns. I have no problems in writing the code for it.
Finally I want to add a row at the end of the data to display totals of the numerical columns on the Excel worksheet (ws). The code is as shown below:

With ws
.Cells(rw, 8).Value = "TOTAL"
.Cells(rw, 8).Font.Bold = True
For k = lastCol(ws) To 9 Step -1
Set calcRng = .Range(.Cells(4, k), .Cells(rw - 1, k))
.Cells(rw, k).Value = WorksheetFunction.Sum(calcRng)
.Cells(rw, k).Font.Bold = True
Next k
End With

‘rw’ is the first blank row after the data and numerical data columns start from Column 9. ‘lastCol’ is a function that calculates the last column of the worksheet.
The above code works fine with the Excel worksheet (ws) opened during the run time. However, if any other Excel workbook is already open prior to running the code, the Totals fail to compile with the above code.
With a second workbook on the computer, I believe that the code fails to set the range correctly. I am not getting any error message – only the cells where totals are to appear remain blank.
I cannot post the database or Excel files because the data is not to be published.
Can someone please advise how to get the focus on the worksheet (ws)?
Thanks in advance.