locked
Ms Excel and MS-Power Point Automation in VB.Net RRS feed

  • 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 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
    Tuesday, April 8, 2008 3:50 PM
  • Hi ,

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

    Best regards,
    Riquel

    Friday, April 11, 2008 6:29 AM
    Moderator

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 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
    Tuesday, April 8, 2008 3:50 PM
  • Hi ,

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

    Best regards,
    Riquel

    Friday, April 11, 2008 6:29 AM
    Moderator