SUM of selected cell range

[ria.arora]

Hi

I need to calculate SUM of selected cell range e.g.

PB YU Bud PBud Actual TC
Sam 120 130 190 130 150
James 130 150 110 120 130
Raj 150 120 120 100 120
=================================
Total 400 400 420 350 400

How to calculate the total using Ms Access VBA? Also to how draw "=====" line?

 

[Rx_]

Selected cells how? Cells in Excel? Cells in a form?
More details please.

 

[ria.arora]

Hi Rx,

I'm trying to automate Excel from Access VBA. I have all the data in Access and executing the SQL in Access writing data in particular cells in Excel Sheet and after that I need to sum these cells. And these number of columns need to dynamic e.g. it can be =SUM(B3:B7) or =SUM(B3:B10) depends on number of records fetched by SQL.

Thanks for the help

 

[Isskint]

ria,

Are you just exporting to Excel to sum the value? if so use the Dsum function in Access and save yourself a lot of hassle

 

[ria.arora]

Hi

I'm using following method to populate the data in Excel Sheet

    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![Banker]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueYTD]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuRevenue]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueTCY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenuePFY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsBud]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsPFYRev]
        ColumnStart = ColumnStart + 1
        ..............
        ..............
        ..............
        ..............
        ..............
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuActRatio]

        If rsTmp.EOF Then Exit For
        RowStart = RowStart + 1
        rsTmp.MoveNext
    Next count

Data will be populated in around 80 columns and I need the SUM for all these columns. I don't want to SUM one by one cell using below function

.Range("B11").Formula = "=Sum(B6:B10)"

Pls let me the better way to do this....

 

[Isskint]

OK so essentially you want the sum totals of:
rsTmp![Banker]
rsTmp![RevenueYTD]
rsTmp![AnnuRevenue]
rsTmp![RevenueTCY]
rsTmp![RevenuePFY]
rsTmp![AnnuVsBud]
rsTmp![AnnuVsPFYRev]
..............
..............
..............
If this is the case, just run a totals query on the underlying table and it will give you the totals of each field (column) without the need to export to excel

 

[Rx_]

Assuming the .Range in your example is within a With (object variable) and End Width
My example will use the verbose object qualifier of ObjXL as the Excel instance for demonstration.

Here is an example of a subtotal for one column starting at T6:T (however many records the recordset count returned + 6)
In my code, the 6 would also be replaced with a variable ExcelWorksheetStartRow.
And the column T might also be a variable.

ObjXL.Range("T4").Select
ObjXL.ActiveCell.Formula = "= SUBTOTAL(101, T6:T" & intMaxRecordCount + 6 & ")"

The Subtotal offeres more options. In this case, if Row 5 had the titles, with a filter applied, the subtotal would only include the rows visible.
My preference is to start the data title on row5, the data on row 6 to the end of the recordcount. Then place the formulas on Row 3. This way, the user can filter columns and not have to search for the forumula.

By use of variables, custom formulas can get very powerful. In the attachment, the repeted values are shown in bold, not-bold. As you can see, the SubTotal can be exapanded to become very powerful. The custom formulas are designed for each individual data pull from an Access user interface.

My suggestion is to start simple and build on your experience. The suggestion above in the previous post is also very good depending on your goal. You can express your thanks to Isskint for offering a solution by clicking on the Thanks.

 

[Rx_]

MSACCESS VBA Excel Automation - format cells double line to indicate Sum

The second part of your question: Here is a very basic way
With ObjXL
.Range("A11:G11").Select
With .Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.Weight = xlThick
End With
End With

 

[ria.arora]

Hi Rx,

I'm getting below error:

Compile error:
Method or data member not found.

    objXLSheet.range("RowEnd").Select
    objXLSheet.ActiveCell.Formula = "= SUBTOTAL(101, B" & RowEnd & ":B" & recount + 6 & ")"

 

[Rx_]

post your instance of creating objXLSheet.
What is RowEnd? - is it a Named Range?
Display your value for recount as you set it in a variable
Those are the 80% probilities - if you can attach the code module, it might prove useful.

 

[ria.arora]

Hi Rx,

Please find below the code used.

    Dim TempString As String

    Set objXLApp = CreateObject("Excel.Application")
    wb = "F:\MyDoc\Weekly MI Automation\" & sTeamRegionName & Left(Date, 2) & Mid(Date, 4, 2) & Right(Date, 4) & "_" & Left(Time, 2) & Mid(Time, 4, 2) & ".xlsx"
    
    Set objXLWorkbook = objXLApp.Workbooks.Add 'Will Create a new workbook
    Set objXLSheet = objXLWorkbook.Worksheets(1) 'Will create a new worksheet
    'objXLSheet.Name = sTeamRegionName 'Rename the worksheet
    'Set Wkb = AppExcel.Workbooks.Open(excelname)
    
    objXLApp.Visible = True 'Makes the spreasheet visible. False will let you open it behind the scenes

    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![Banker]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueYTD]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuRevenue]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueTCY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenuePFY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsBud]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsPFYRev]
        ColumnStart = ColumnStart + 1
        ..............
        ..............
        ..............
        ..............
        ..............

        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuActRatio]

        If rsTmp.EOF Then Exit For
        RowStart = RowStart + 1
        rsTmp.MoveNext
    Next count

    rsTmp.Close
    Set rsTmp = Nothing
    
    'For double top line and single bottom line for Subtotal
    TempString = "A" & RowStart & ":" & "T" & RowStart
    boldFontSize (TempString)
    applyDoubleTotalLine (TempString)

objXLSheet.range("RowEnd").Select
    objXLSheet.ActiveCell.Formula = "= SUBTOTAL(101, B" & RowEnd & ":B" & recount + 6 & ")"

Pls help

 

[Rx_]

Get rid of the two set objects.
Set objXLWorkbook = objXLApp.Workbooks.Add 'Will Create a new workbook
Set objXLSheet = objXLWorkbook.Worksheets(1) 'Will create a new worksheet

put objXLApp.Visible = True right after creating your Excel object and see.

objXLApp.Workbooks.Add 'Will still Create a new workbook - with visible on, doesn't your default Excel object already create a workbook with worksheets? Do you really need to create one?
objXLWorkbook.Worksheets(1).Name = "MyName"


objXLApp.Range("T4").Select
objXLApp.ActiveCell.Formula = "= SUBTOTAL(101, T6:T" & intMaxRecordCount + 6 & ")"