23-VB%2C+Excel+and+VBA - VB Excel and VBA Manipulating Excel In VB VB.NET allows you to write code to manipulate Excel Read data from Excel Create open

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

This preview shows page 1 - 12 out of 42 pages.

VB, Excel and VBA
Image of page 1
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
Image of page 2
Configurations To manipulate Excel from a VB program, you must add a reference to Excel
Image of page 3
Search for Microsoft Excel Object Library The name may be different depending on your Excel version
Image of page 4
Check “Microsoft.Office.Interop.Excel”
Image of page 5
Form
Image of page 6
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
Image of page 7
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
Image of page 8
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
Image of page 9
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,
Image of page 10
VBA vs. VB.NET
Image of page 11
Image of page 12

You've reached the end of your free preview.

Want to read all 42 pages?

  • Fall '08
  • SNADWICH
  • Visual Basic for Applications

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture