icecool
asked on
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?
Thanks.
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.Applic ation")
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" ) + _
Rs.Fields("TaxAmount")
Rs.MoveNext
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"
xlSheet.Application.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
Thanks.
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.Applic
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"
Rs.Fields("TaxAmount")
Rs.MoveNext
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"
xlSheet.Application.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
ASKER
hi, TimCottee,
I tried that but an error occured Run-Time error 1004, Application-defined or object-defined error.
What is the problem?
I tried that but an error occured Run-Time error 1004, Application-defined or object-defined error.
What is the problem?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.