Answered by:
Ms Excel and MS-Power Point Automation in VB.Net

Question
-
Pls let me know how to Automate MS-Excel and MS-Power Point Applications in MS Visual Basic .Net
Tuesday, April 8, 2008 10:34 AM
Answers
-
Hi Emcube,
Here are samples of automating in Excel. I don't have these for Powerpoint, but I imagine it should be fairly similar to Excel.
First, you need to add a reference to the Excel Object Library. I used version 9.0 because I use Excel 2000. The version changes depending on what version of Excel you have, so do not worry about the number. Go to Project > Add Reference. Select the COM tab. Select Microsoft Excel Object Library x.0 and click Add. That has added the reference for you.
Now, the code:
At the top of your project:
Code SnippetPublic Class Form1
'Added Microsoft Excel 9.0 Object Library to the COM references.
'You may need to add a different object library to get this working with your current version of Excel.
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
You need to tell your program to open Excel and (if needed) a template or workbook file. I usually put this in the Form1 Load event, but really it can go just about anywhere as long as this bit gets done before the next bit:
Code SnippetPrivate Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
oExcel = CType(CreateObject("Excel.Application"), Excel.Application)
oBook = oExcel.Workbooks.Open(Application.StartupPath & "\Template File.xlt")
The automating part of your project (does not have to be a button, and this only covers basics of automation. A lot more is possible):
Code SnippetPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'This is a small example of the Excel automation I'm using for my project.
'The template (PO Template.xlt) is a copy of the actual template being used.
'There is no save code here because I do not want the purchase order saved as an Excel file,
'After being created using this method, it will be printed as a PDF file then emailed to a supplier.
'I do not yet have the code to do this, it will be the next thing I will be working on.
'If you need any more info, please let me know.
Try
With oExcel
.Visible = True
'Add supplier details
.Range("E12").Value = TextBox1.Text
.Range("E13").Value = TextBox2.Text
.Range("E14").Value = TextBox3.Text
.Range("E15").Value = TextBox4.Text
End With
Catch ex As Exception
MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "Error!")
End Try
End Sub
And finally, add the following event to your project, so whenever the program is closed, it closes Excel too:
Code SnippetPrivate Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
oExcel.Quit()
End
End Sub
Hope this gives you an idea. If you need more info, just let me know.
BortTuesday, April 8, 2008 3:50 PM -
Hi ,
About automating PowerPoint application, read this article for your reference.
Best regards,
RiquelFriday, April 11, 2008 6:29 AMModerator
All replies
-
Hi Emcube,
Here are samples of automating in Excel. I don't have these for Powerpoint, but I imagine it should be fairly similar to Excel.
First, you need to add a reference to the Excel Object Library. I used version 9.0 because I use Excel 2000. The version changes depending on what version of Excel you have, so do not worry about the number. Go to Project > Add Reference. Select the COM tab. Select Microsoft Excel Object Library x.0 and click Add. That has added the reference for you.
Now, the code:
At the top of your project:
Code SnippetPublic Class Form1
'Added Microsoft Excel 9.0 Object Library to the COM references.
'You may need to add a different object library to get this working with your current version of Excel.
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
You need to tell your program to open Excel and (if needed) a template or workbook file. I usually put this in the Form1 Load event, but really it can go just about anywhere as long as this bit gets done before the next bit:
Code SnippetPrivate Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
oExcel = CType(CreateObject("Excel.Application"), Excel.Application)
oBook = oExcel.Workbooks.Open(Application.StartupPath & "\Template File.xlt")
The automating part of your project (does not have to be a button, and this only covers basics of automation. A lot more is possible):
Code SnippetPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'This is a small example of the Excel automation I'm using for my project.
'The template (PO Template.xlt) is a copy of the actual template being used.
'There is no save code here because I do not want the purchase order saved as an Excel file,
'After being created using this method, it will be printed as a PDF file then emailed to a supplier.
'I do not yet have the code to do this, it will be the next thing I will be working on.
'If you need any more info, please let me know.
Try
With oExcel
.Visible = True
'Add supplier details
.Range("E12").Value = TextBox1.Text
.Range("E13").Value = TextBox2.Text
.Range("E14").Value = TextBox3.Text
.Range("E15").Value = TextBox4.Text
End With
Catch ex As Exception
MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "Error!")
End Try
End Sub
And finally, add the following event to your project, so whenever the program is closed, it closes Excel too:
Code SnippetPrivate Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
oExcel.Quit()
End
End Sub
Hope this gives you an idea. If you need more info, just let me know.
BortTuesday, April 8, 2008 3:50 PM -
Hi ,
About automating PowerPoint application, read this article for your reference.
Best regards,
RiquelFriday, April 11, 2008 6:29 AMModerator