Vb loan calculator with schedule into excel

[Majink]

Hi guys
When I put code into the button "excel" to generate schedule in a loan calculator,
I had, and still have errors, mostly: " excel.sheet,excel application not defined..., excel not declared".
I wrote the imports line, and I added the appropriate reference to excel.However, there's errors.
I don't know how to fix the problem. See attched file.
Please help.

[pietercdevries]

Hi,
I can not see the attachment but I am willing to help.
Maybe you can put the snippet on the form
Pieter

[Majink]

Thank you very much.
As a matter of fact I had a crash when posting the attachment file.




here's the code:


E:\CreditCalc\LoanProject\LoanProject\Form1.vb 1
1 Imports Microsoft.Office.Interop.Excel
2
3 Public Class Form1
4 Dim dLoan, dRate, dPayment As Double
5 Dim iYears As Integer
6
7 Private Sub btnCompute_Click(ByVal sender As System.Object, ByVal e As System.
EventArgs) _
8 Handles btnCompute.Click
9 dLoan = CDbl(txtLoan.Text.Trim)
10 dRate = CDbl(txtRate.Text.Trim)
11
12 txtLoan.Text = dLoan.ToString("N2")
13 If dRate > 1 Then dRate = dRate / 100
14 iYears = CInt(nudYear.Value)
15 dPayment = Pmt(dRate / 12, _
16 iYears * 12, -1 * dLoan)
17 txtPayment.Text = _
18 Format(dPayment, "#,##0.00")
19 txtPayment.Refresh()
20
21 End Sub
22
23 Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.
EventArgs) _
24 Handles btnExcel.Click
25 Dim iMonth, iYear As Integer
26 Dim iRow, iPayment As Integer
27 Dim iStartRow, iEndRow As Integer
28 Dim dTotInterest, dTotPrinciple As Double
29 Dim SheetObj As excel.sheet (here error: excel.sheet not defined)
30
31 Dim ExcelObj As New excel.application (here error:excel.application not defined)
32 ExcelObj.DisplayAlerts = False
33
34 ExcelObj.Workbooks.Close()
35
36 ExcelObj.Quit()
37
38 ExcelObj = Nothing
39
40 SheetObj = Nothing
41 ExcelObj.Workbooks.Add()
42
43
44 If SheetObj Is Nothing Then
45 SheetObj = ExcelObj.Workbooks(1).Worksheets(1)
46 End If
47
48 btnCompute.PerformClick()
49 ExcelObj.Visible = False
50 SheetObj.Range("A1500").ClearContents()
51 SheetObj.Cells(1, 1).Value = "Loan Amount:"
52 SheetObj.Cells(1, 3).Value = Format(dLoan, "#,###.00")
53 'Interest Rate
54 SheetObj.Cells(2, 1).Value = "Interest Rate:"
55
56 SheetObj.Cells(2, 3).Value = Format(dRate, "Percent")
57 SheetObj.Cells(3, 1).Value = "Years of Loan:"
58 SheetObj.Cells(3, 3).Value = iYears
59 SheetObj.Cells(4, 1).Value = "Monthly Payment:"
60 SheetObj.Cells(4, 3).Value = Format(dPayment, "#,###.00")
61 SheetObj.Range("A1:C4").Font.Bold = True
62 SheetObj.Range("A1:A500").ColumnWidth() = 10
63 SheetObj.Range("B1:B500").ColumnWidth() = 13
64 SheetObj.Range("C1:C500").ColumnWidth() = 13
65
66 SheetObj.Range("D1500").ColumnWidth() = 15
67 SheetObj.Range("A5500").AutoFormat( _
68 Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting2) (here error:Excel not declared)
69
70 iRow = 5
71 SheetObj.Cells(iRow, 1).Value = "Date"
72 SheetObj.Cells(iRow, 2).Value = "Interest"
E:\CreditCalc\LoanProject\LoanProject\Form1.vb 2
73 SheetObj.Cells(iRow, 3).Value = "Principle"
74 SheetObj.Cells(iRow, 4).Value = "Balance"
75
76 For iYear = 1 To iYear
77 iRow += 1
78 iStartRow = iRow
79 For iMonth = 1 To 12
80 SheetObj.Cells(iRow, 1).Value = DateSerial(Year(Now), _
81 Month(Now) + iPayment, 1)
82 SheetObj.Cells(iRow, 2).Value = dLoan * (dRate / 12)
83 SheetObj.Cells(iRow, 3).Value = dPayment - _
84 SheetObj.Cells.Item(iRow, 2).Value
85 dLoan -= SheetObj.Cells.Item(iRow, 3).Value
86 SheetObj.Cells(iRow, 4).Value = dLoan
87 iRow += 1
88
89 iRow += 1
90 iPayment += 1
91 Next iMonth
92 iEndRow = iRow - 1
93
94 SheetObj.Cells(iRow, 1).Value = "Sub Total"
95 SheetObj.Cells(iRow, 2).Value = "=SUM(B" & iStartRow & ":B" & iEndRow & ")
"
96 SheetObj.Cells(iRow, 3).Value = _
97 "=SUM(C" & iStartRow & ":C" & iEndRow & ")"
98
99 dTotInterest += SheetObj.Cells.Item(iRow, 2).Value
100 dTotPrinciple += SheetObj.Cells.Item(iRow, 3).Value
101
102 Next iYear
103 dLoan -= SheetObj.Cells.Item(iRow, 3).Value
104 dLoan -= SheetObj.Cells(iRow, 3).Value
105 SheetObj.Cells(iRow, 1).Value = "Grand Total"
106
107 SheetObj.Cells(iRow, 2).Value = dTotInterest
108 SheetObj.Cells(iRow, 3).Value = dTotPrinciple
109 ExcelObj.CommandBars("Formatting").Visible = False
110 ExcelObj.CommandBars("Standard").Visible = False
111 ExcelObj.DisplayFormulaBar = False
112
113 ExcelObj.WindowState = _
114 Excel.XlWindowState.xlNormal (here error: excel not declared)
115
116 ExcelObj.Width = 325
117
118 ExcelObj.Height = 350
119 ExcelObj.Left = Me.Left + 140
120 ExcelObj.Top = Me.Top - 24
121 Me.Left = 100
122 Me.Top = 100
123 ExcelObj.Visible = True
124
125 End Sub
126
127 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
_
128 Handles MyBase.Load
129
130 End Sub
131
132 Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.
EventArgs) _
133 Handles btnExit.Click
134 Me.Close()
135 End Sub
136 End Class
137

[pietercdevries]

Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class Form1
    Dim dLoan, dRate, dPayment As Double
    Dim iYears As Integer

    Private Sub btnCompute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnCompute.Click
        dLoan = CDbl(txtLoan.Text.Trim)
        dRate = CDbl(txtRate.Text.Trim)

        txtLoan.Text = dLoan.ToString("N2")
        If dRate > 1 Then dRate = dRate / 100
        iYears = CInt(nudYear.Value)
        dPayment = Pmt(dRate / 12, _
        iYears * 12, -1 * dLoan)
        txtPayment.Text = _
            Format(dPayment, "#,##0.00")
        txtPayment.Refresh()

    End Sub

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnExcel.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("C:\Users\Pieter\Documents\Pieter.xls")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 2).value)
        'edit the cell with new value
        xlWorkSheet.Cells(1, 1) = txtLoan.Text
        xlWorkSheet.Cells(1, 2) = txtRate.Text
        xlWorkSheet.Cells(1, 3) = nudYear.Text
        xlWorkSheet.Cells(1, 4) = txtPayment.Text

        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles MyBase.Load

    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnExit.Click
        Me.Close()
    End Sub

[Majink]

Again, I truly thank you for your quick respond and help.
But I still have errorsambiguous 'application' in space name...')
Just a question: have you run the application after modifying the code?
Or does the application work fine for you?

[Majink]

Again, I truly thank you for your quick respond and help.
But I still have errorsambiguous 'application' in space name...')
Just a question: have you run the application after modifying the code?
Or does the application work fine for you?

[pietercdevries]

The app works fine for me.
Maybe you should recreate the from and implant the code.
You do have to have the excel file already existing in the given path.
Pieter