Printable View
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.
Hi,
I can not see the attachment but I am willing to help.
Maybe you can put the snippet on the form
Pieter
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("A1:D500").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("D1:D500").ColumnWidth() = 15
67 SheetObj.Range("A5:D500").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
Code: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
Again, I truly thank you for your quick respond and help.
But I still have errors:(ambiguous 'application' in space name...')
Just a question: have you run the application after modifying the code?
Or does the application work fine for you?
Again, I truly thank you for your quick respond and help.
But I still have errors:(ambiguous 'application' in space name...')
Just a question: have you run the application after modifying the code?
Or does the application work fine for you?
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