[RESOLVED] Write to Excel from PPT (Test)

[NicoL]

Hi all,



While working with VBA in PowerPoint, I stumbled upon a problem.

The variables I entered on the first slide aren't accessible on the other slides. Is there a way to remember it on all slides.

By clicking a button in PowerPoint, a cell in an Excelfile needs to be updated. Each slide writes a value to a different cell, every button on that slide writes a different value to the same cell.

To solve this, I found this way.
If the button is pressed:
1. The Excel-file is opened (hidden)
2. The value is exported
3. The Excel-file is closed (and saved)
The problem that remains is that I can only link to one file, while I want to select one of 22 in te first slide.
The only solution I can think of is to make 22 different presentations, with each a different linked Excel-file.

Is (t)here someone who can help me with this?
Or is there a code to "replace all" in the code while running the presentation, so I can change al links in the presentation by clicking a button at the start?

Public oXLApp As Excel.Application
Public oWB As Workbook

Private Sub CommandButton1_Click()
        Set oXLApp = New Excel.Application
        Set oWB = oXLApp.Workbooks.Open(ActivePresentation.Path & "\UGT.xls")
        If Not oWB Is Nothing Then
        oWB.Worksheets(3).Range("B3") = "a"
            oXLApp.Visible = False
        End If
                oWB.Close True
                oXLApp.Quit
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

[westconn1]

i am not sure exactly what you want to do, but you can open an instance of excel then keep it open indefinitely, open and close workbooks as required, then close excel when finished with, much quicker than opening and closing excel all the time

[NicoL]

i am not sure exactly what you want to do, but you can open an instance of excel then keep it open indefinitely, open and close workbooks as required, then close excel when finished with, much quicker than opening and closing excel all the time

That's the problem, I tried that at first. When you open your workbook on the first slide, you can't close it on another one. You have to close it on the same slide. That's because he won't remember the file he should write to.

This code doesn't work:

Slide 1

Dim oXLApp As Excel.Application
Dim oWB As Workbook

Private Sub OpenExcel_Click()
        Set oXLApp = New Excel.Application
        Set oWB = oXLApp.Workbooks.Open(ActivePresentation.Path & "\UGT.xls")
        oXLApp.Visible = False
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

Slide 2

Dim oXLApp As Excel.Application
Dim oWB As Workbook

Private Sub answer1a_Click()
        If Not oWB Is Nothing Then
            oWB.Worksheets(3).Range("B3") = "a"
            oXLApp.Visible = True
        End If
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

Private Sub answer1b_Click()
        If Not oWB Is Nothing Then
            oWB.Worksheets(3).Range("B3") = "b"
            oXLApp.Visible = True
        End If
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

Private Sub answer1c_Click()
        If Not oWB Is Nothing Then
            oWB.Worksheets(3).Range("B3") = "c"
            oXLApp.Visible = True
        End If
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

Private Sub answer1d_Click()
        If Not oWB Is Nothing Then
            oWB.Worksheets(3).Range("B3") = "d"
            oXLApp.Visible = True
        End If
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

Slide 3

Dim oXLApp As Excel.Application
Dim oWB As Workbook

Private Sub answer2a_Click()
        If Not oWB Is Nothing Then
            oWB.Worksheets(3).Range("B4") = "a"
            oXLApp.Visible = True
        End If
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

Private Sub answer2b_Click()
        If Not oWB Is Nothing Then
            oWB.Worksheets(3).Range("B4") = "b"
            oXLApp.Visible = True
        End If
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

Private Sub answer2c_Click()
        If Not oWB Is Nothing Then
            oWB.Worksheets(3).Range("B4") = "c"
            oXLApp.Visible = True
        End If
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

Private Sub answer2d_Click()
        If Not oWB Is Nothing Then
            oWB.Worksheets(3).Range("B4") = "d"
            oXLApp.Visible = True
        End If
            ActivePresentation.SlideShowWindow.View.Next
    Exit Sub
End Sub

... etc.

Slide 42

Dim oXLApp As Excel.Application
Dim oWB As Workbook

Sub closeit()
    SlideShowWindows(1).View.Exit
    Application.Quit
End Sub

Private Sub cmdCloseXL_Click()
    On Error GoTo Err_Handler
    
    If Not oXLApp Is Nothing Then
       oXLApp.Visible = True
    End If
    
        oWB.Close True
        oXLApp.Quit
        closeit

    Set oWB = Nothing
    Set oXLApp = Nothing
    Exit Sub

Err_Handler:
    MsgBox Err.Description, vbInformation, "Excel"
End Sub

When you use this code, nothing is written to the Excel document. Also the "closeXL button" won't work.

[westconn1]

i believe what you need, is to move the

Dim oXLApp As Excel.Application
Dim oWB As Workbook

to a module as public

Public oXLApp As Excel.Application
Public oWB As Workbook

make sure to remove from all slides

[NicoL]

I put

Public oXLApp As Excel.Application
Public oWB As Workbook

in a module, called "module1".

Now the buttons on slide 2 work as they should, the buttons on the next slides don't.

I guess I'm one step ahead (Thanks ) but the problem isn't solved yet

Thanks!! The problem is solved, there was a mix up with some slides (the 42'nd slide was called slide 3) and because of that, the buttons didn't work. Now they do.