Copy PowerPoint user input data to open Excel spreadsheet

Question

I have a macro in Powerpoint where the user inputs data. I want this data to be saved in a spreadhsheet that is already open.

How do I make PowerPoint communicate with the spreadsheet?

I get

error 9: Subscript out of range.

Sub SaveBatchNo()

Dim strResult As String
strResult = InputBox("Please enter batch number.")

Workbooks("PP test.xlsx").Worksheets(1).Range("A1").Value = strResult

SlideShowWindows(1).View.GotoSlide 2

End Sub

Answer 1

One approach is below:

Sub SaveBatchNo()
    Dim strResult As String
    Dim ExcelApp As Object, WB As Object
    
    strResult = InputBox("Please enter batch number.")
    
    On Error GoTo errHandler    'https://learn.microsoft.com/ru-ru/office/vba/language/reference/user-interface-help/on-error-statement
    
    Set ExcelApp = GetObject(, "Excel.Application") ' get Excel application if it's running
    Set WB = ExcelApp.Workbooks("PP test.xlsx") ' get Workbook "PP test.xlsx" if it's opened
    WB.Worksheets(1).Range("A1").Value = strResult
    
    On Error GoTo 0
    
    ActivePresentation.SlideShowSettings.Run    ' without this instruction the next instruction raises the error
  
    SlideShowWindows(1).View.GotoSlide 2
   
    Exit Sub
errHandler:
   MsgBox "Running Excel app. with opened Workbook 'PP test.xlsx' not detected", vbCritical + vbOKOnly, "Sub SaveBatchNo()"
End Sub