locked
Updating a named range value in an embedded Excel sheet contained in a PowerPoint Presentation hosted by VB.NET windows form

    Pregunta

  • Hi,

    My query is accompanied by relevant code snippets, which is a cut-down version of our original VB.NET application. The code from my sample application is written in VB.NET and includes office automation.

    My windows form contains a web browser control that enables vb.net windows form to host MS Office documents. The web browser control opens a powerpoint presentation that contains an embedded Excel sheet that has a named range "range1" on cell "A1" in "Sheet1". This presentation also contains a button "Update Range Value" and the macro behind this button is written in powerpoint vba, which writes the value of the range "range1" and displays this value. This form works fine and it updates the range value that can be shown on the embedded Excel sheet.

    However, my windows form does not show the updated value on the embedded Excel sheet. The following are the two aspects of testing the macro for a given powerpoint presentation:

    1. Selecting a value from the drop-down above. The event "ComboBox_SelectedValueChanged" calls the macro in powerpoint presentation document.
    2. Clicking the "Update Range Value" button on first slide of the opened powerpoint presentation.

    Both ways display the updated range value; but the value does not appear on the embedded Excel sheet for the named range "range1" in Sheet1 or the object window does not get updated for some reason.

    The objective is to update the range value corresponding to whatever is selected from the drop-down above. My code snippets are as follows:

    VB.NET code:

    code for "Form1_Load" event, where I'm opening the powerpoint presentation:

    PPTPres = PPTApp.Presentations.Open(sReportPath, WithWindow:=Microsoft.Office.Core.

    MsoTriState

    .msoFalse)

     

     

    Me.WebBrowser1.Navigate(Application.StartupPath & "\" & "Presentation1.pptm"

    )


     

    Code for "ComboBox1_SelectedValueChanged" event where I'm calling my PowerPoint VBA macro:

        Private Sub ComboBox1_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged
            Try
                Dim ExcelApp As Excel.Application
                Dim xlWBook As Excel.Workbook

                Dim Controls() As Control
                Dim DataGridViewComboBoxCell As ComboBox = CType(sender, ComboBox)
                Dim Controls1() As Control = Me.Controls.Find("SplitContainerParameters", True)
                Dim sRangeName As String = "range1" 'CType(DataGridViewCellTagInfo(1), String)

                For Each slide As PowerPoint.Slide In Me.PPTApp.Presentations(1).Slides
                    If slide.Shapes.Count > 0 Then
                        For Each oleobject As PowerPoint.Shape In slide.Shapes
                            If LCase(oleobject.OLEFormat.ProgID).Contains("excel.sheet") Then
                                xlWBook = oleobject.OLEFormat.Object
                                'oleobject.OLEFormat.Activate()
                                ExcelApp = xlWBook.Application
                                xlWBook.Sheets(1).Activate()
                                PPTApp.Run("'" & PPTApp.Presentations(1).Name & "'!UpdateRangeValue")
                            End If
                        Next
                    End If
                Next
            Catch ex As Exception

            End Try
        End Sub

     

    PowerPoint VBA macro sample code:

    Public Function UpdateRangeValue() As Integer
        On Error GoTo Errhandler
        Dim ExcelApp As Object
        Dim WBook As Object
        Set WBook = Application.Presentations(1).Slides(1).Shapes(1).OLEFormat.Object
       
        'WBook.Sheets(1).Range("range1").Value = Drp.List(Drp.ListIndex)
        Set ExcelApp = WBook.Application
        WBook.Sheets(1).Activate
        ExcelApp.screenupdating = True
        WBook.Sheets(1).Range("range1").Value = "This is a test value"
        WBook.Save
        'WBook.Sheets(1).Range("range1").Value = rangevalue
        MsgBox "Range value: " & WBook.Sheets(1).Range("range1").Value
        UpdateRangeValue = 1

    Errhandler:
        If Err.Number <> 0 Then
            MsgBox Err.Description
        End If
    End Function


    I hope, I have clarified my query. Could you please help me in this regard?

    Kind regards,

    Misbah

    miércoles, 08 de febrero de 2012 10:05

Todas las respuestas

  • Hi Misbah,

    Welcome to the MSDN Forum.

    Do you get some error exceptions during you call the macro?

    I noticed that you used the try catch block in the subroutine ComboBox1_SelectedValueChanged , but you didn't handle the exception if the application throws one. So I suggest you just use Console.writeline(ex.message) to view the exception if there is one. 

    I look forward you. 

    Best regards,

    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    viernes, 10 de febrero de 2012 9:29
    Moderador
  • Hi Mike,

    Thank you for your reply. This is to confirm that, I am not getting any error exception. The code just updates the named range value in a powerpoint presentation but does not show the updated value on the screen. For some reason, it is not updating the screen. Looking forward for your reply.

    Kind regards,

    Misbah

    viernes, 10 de febrero de 2012 11:37
  • Hi Misbah,

    Sorry for delay response.

    Did you finish this way:

    "I noticed that you used the try catch block in the subroutine ComboBox1_SelectedValueChanged , but you didn't handle the exception if the application throws one. So I suggest you just use Console.writeline(ex.message) to view the exception if there is one. "

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    martes, 21 de febrero de 2012 9:39
    Moderador
  • Hi Mike,

    Thank you for your reply, once again. I stepped into the code for "ComboBox1_SelectedValueChanged" while debugging and the code is not throwing any exception. Infact, it executes the entire event completely and does not jump to catch block. I could also access the updated value of the named range (in quick watch window); but I couldn't see the value on the screen. Is there any way to update the embedded excel sheet in a powerpoint presentation from vb.net during runtime. The value is updated to the named range but cannot be seen on the embedded excel sheet - even though, if I double-click the sheet to activate it. I hope, I have clarified it.

    Kind regards,

    Misbah

    miércoles, 29 de febrero de 2012 9:39
  • Hi Misbah,

    It would be better that you upload your test project on a website such as skydrive, then, I will download it and try my best to find the root cause.

    I look forward you.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    jueves, 01 de marzo de 2012 2:05
    Moderador