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

 

Answer 1

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 Snippet

Public 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 Snippet

    Private 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")

NOTE: Use Application.StartupPath if you are adding the template/workbook file to your project so it is installed on a computer when your project is. If you need another fixed directory (eg My Documents), you should be able to find the code for that easily on Google.

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 Snippet

    Private 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 Snippet

    Private 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.
Bort

Answer 2

Hi ,

About automating PowerPoint application, read this article for your reference.

Best regards,
Riquel