The following class excellibrary is a very common class that uses VBA to manipulate excel. It is my summary, arrangement, and reference in my work (I have used some examples from Microsoft) I have made many improvements and usage, and I will share them with you here.
At first, the program had no comments. For ease of understanding, I just added some comments.
'Manipulate the Excel general class library VB. NET
Imports Excel
Public class excellibrary
'Excel application indicates Excel itself
Private mexcelapp as Excel. Application
'Workbook is the workbook in Excel.
Private mworkbook as Excel. Workbook
'Worksheet is the sheet1, sheet2...
Private mworksheet as Excel. Worksheet
Public sub new ()
'Start an Excel application
Mexcelapp = new excel. Application
End sub
Public sub open ()
'New workbook
Mworkbook = mexcelapp. workbooks. Add ()
End sub
Public sub open (byval template as string)
'Create a workbook using the template
Mworkbook = mexcelapp. workbooks. Add (Template)
End sub
Public sub setactivesheet (byval sheetname as string)
'Set the sheet of the current activity
Mworksheet = mworkbook. worksheets (sheetname)
End sub
Public sub addnewsheet (byval sheetname as string)
'Add a new sheet.
Mworksheet = mworkbook. worksheets. Add ()
Mworksheet. Name = sheetname
End sub
Public sub hidesheet (byval sheetname as string)
'Hide a sheet (sometimes this function is also required)
Mworkbook. worksheets (sheetname). Visible = false
End sub
Public sub copyrange (byval sheetname as string, byval cell1 as string, byval cell2 as string)
'Copy in Excel
Setactivesheet (sheetname)
Mworksheet. Range (cell1, cell2). Copy ()
End sub
Public sub pasterange (optional byval sheetname as string = "")
'Regional paste in Excel
If sheetname <> "" then
Setactivesheet (sheetname)
End if
Mworksheet. paste ()
End sub
Public sub changesheetname (byval sheetoldname as string, byval sheetnewname as string)
'Change Sheet Name
Mworkbook. worksheets (sheetoldname). Name = sheetnewname
End sub
Public sub setvalue (byval cellposition as string, byval value as string)
'Set the value of a cell.
Mworksheet. Range (cellposition). formular1c1 = Value
End sub
Public sub setvalue (byval rangex as string, byval rangey as string, byval value as string (,))
'Specifies the value of a region. This function is very useful.
Dim RG as range
Rg = mworksheet. Range (rangex, rangey)
RG. value = Value
End sub
Public sub saveas (byval filename as string)
'Save
Mworkbook. saveas (filename)
End sub
Public sub close ()
'Close
Mworksheet = nothing
Mworkbook = nothing
Mexcelapp. Quit ()
Mexcelapp = nothing
GC. Collect ()
End sub
End Class