Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Excel Application in VB

hi, below is a Sub that works with Excel. Now, I don't have problem writing details into Excel file. I would like to do some calculation on Adjustment, CurrentCharges and TaxAmount after all details (like using sum function). How to do that?


Public Sub tryExcel()
    Dim i As Integer
    Dim strSQL As String
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    xlSheet.Cells(1, 1).Value = "Invoice Summary Report"
    xlSheet.Cells(2, 1).Value = "Date: {09/01/2002}"
    xlSheet.Cells(4, 1).Value = "Account No"
    xlSheet.Cells(4, 2).Value = "Invoice No"
    xlSheet.Cells(4, 3).Value = "Adjustment (RM)"
    xlSheet.Cells(4, 4).Value = "Current Charges (RM)"
    xlSheet.Cells(4, 5).Value = "Govt. Tax (RM)"
    xlSheet.Cells(4, 6).Value = "Total Amount (RM)"
    strSQL = "Select * From aInvoice_SummaryReport Order By InvoiceNo Asc"
    Call OpenRS(Rs, strSQL)
    If Not (Rs.BOF Or Rs.EOF) Then
        nTotalRec = Rs.RecordCount
        For i = 0 To nTotalRec - 1 Step 1
            xlSheet.Cells(i + 5, 1).Value = Rs.Fields("AccNo")
            xlSheet.Cells(i + 5, 2).Value = Rs.Fields("InvoiceNo")
            xlSheet.Cells(i + 5, 3).Value = Rs.Fields("Adjustment")
            xlSheet.Cells(i + 5, 4).Value = Rs.Fields("CurrentCharges")
            xlSheet.Cells(i + 5, 5).Value = Rs.Fields("TaxAmount")
            xlSheet.Cells(i + 5, 6).Value = Rs.Fields("Adjustment") + _
                                        Rs.Fields("CurrentCharges") + _
        Next i
    End If
    Call CloseRS(Rs)
    xlSheet.Cells(nTotalRec + 5 + 1, 2).Value = "Grand Total"
    'xlSheet.Cells(nTotalRec + 5 + 1, 6).Value
    xlSheet.SaveAs CurrentDirectory & "\Test.xls"
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub
  • 2
1 Solution
I assume that you want the commented line ('xlSheet.Cells(nTotalRec + 5 + 1, 6).Value)  to have a sum of the previous rows.

xlSheet.Cells(nTotalRec + 5 + 1, 6).FormulaR1C1 = "=SUM(R[-" & CStr(nTotalRec - 5) & "]C:R[-1]C)"

This will use the standard Excel Sum() function and set the range to be the records you have just inserted to the spreadsheet.
icecoolAuthor Commented:
hi, TimCottee,

I tried that but an error occured Run-Time error 1004, Application-defined or object-defined error.

What is the problem?
I didn't really test it out, just modified an example I have previously used. You could try a variant of this that doesn't use the R1C1 method:

xlSheet.Cells(nTotalRec + 6,6).Formula = "=Sum(F5:F" & CStr(nTotalRec)

This is just another way of doing it (I think is often easier to use than the R1C1 notation as it is more familiar).

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now