23-VB%2C+Excel+and+VBA - VB Excel and VBA Manipulating...

VB, Excel and VBA

Manipulating Excel In VB
•
VB.NET allows you to write code to
manipulate Excel.
–
Create, open, and save Excel files.
–
Read data from Excel.
–
Write data to Excel.
•
Depends on the version of VB and Excel,
this method may or may not work

Configurations
•
To manipulate Excel from a VB program,
you must add a reference to Excel

•
Search for Microsoft Excel Object Library
•
The name may be different depending on
your Excel version

•
Check “Microsoft.Office.Interop.Excel”

Form

Create Excel Workbook
•
You can use xlBook.SaveAs("test.xlsx") to
save the file
•
Use xlAppObj.Quit() to close Excel
Private Sub btnCreate_Click(sender As Object , e As EventArgs ) Handles
btnCreate.Click
Dim xlAppObj As New Microsoft.Office.Interop.Excel. Application
xlAppObj.Visible = True
Dim xlBook As Workbook = xlAppObj.Workbooks.Add
Dim xlSheet As Worksheet = xlBook.Sheets.Add()
xlSheet.Name = "My Worksheet"
For i As Integer = 1 To 20
xlSheet.Cells(i, 1) = i
Next
End Sub
Set the Visible
property to
True, so that
user can see
Excel, otherwise
Excel window
would not
appear

Modifying Existing Excel File
Private Sub btnOpenExcel_Click(sender As Object , e As EventArgs ) Handles
btnOpenExcel.Click
Dim xlAppObj As New Microsoft.Office.Interop.Excel. Application
xlAppObj.Visible = True
OpenFileDialog1.InitialDirectory = My .Application.Info.DirectoryPath
If OpenFileDialog1.ShowDialog = System.Windows.Forms. DialogResult .OK Then
Dim xlBook As Workbook = xlAppObj.Workbooks.Open(OpenFileDialog1.FileName)
For i As Integer = 1 To 20
Dim xlSheet As Worksheet = xlBook.Sheets( "My Worksheet" )
xlSheet.Cells(i, 1) = 20 - i
Next
End If
End Sub
Set Initial Directory of
OpenFileDialog to the
directory of this
program

Creating An Excel File
Private Sub btnReadExcel_Click(sender As Object , e As EventArgs ) Handles
btnReadExcel.Click
Dim xlAppObj As New Microsoft.Office.Interop.Excel. Application
xlAppObj.Visible = True
OpenFileDialog1.InitialDirectory = My .Application.Info.DirectoryPath
If OpenFileDialog1.ShowDialog = System.Windows.Forms. DialogResult .OK Then
Dim xlBook As Workbook = xlAppObj.Workbooks.Open(OpenFileDialog1.FileName)
Dim i As Integer = 1
Dim xlSheet As Worksheet = xlBook.Sheets( "My Worksheet" )
Do Until xlSheet.Cells(i, 1).Value = Nothing
ListBox1.Items.Add(xlSheet.Cells(i, 1).Value)
i += 1
Loop
End If
End Sub
in the List

Excel VBA
•
VBA stands for Visual Basic for
Applications, it’s a subset of VB language
used inside Excel to extend the
functionalities of Excel
•
Widely used by many industrial engineers
and financial engineers
•
Though it’s not a good language to learn
programming
•
This course mainly focuses on
programming concepts and data structure,

VBA vs. VB.NET
•