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

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.