In PowerPoint VBA: Would like to write data to an Excel File (via Excel App.Object)

Question

I'm using a PowePoint Macro-enabled client (.PPTM) to gather survey data. When a user completes the survey, I'd like the .PPTM file to write the survey-complete date to an Excel file (via an Excel .App object).

The .PPTM and .XLSX files are both in the same SharePoint folder (access permissions are all correct). The .PPTM client can access the .XLSX file and load data, but attempts to write data back have no effect. The .XLSX file is merely a blank file with only 1 worksheet.

Have used both Excel .app objects and Excel worksheet objects to try to write data via '[object].Value = variable' syntax. Have tried both .Range and .Cell methds, but no luck with either one. The statements execute without error, but no data shows up in the target (addressed) cell.

I'm attaching a demo code block with all key aspects of this task. Is there something that's missing here? or is there some idio-syncratic limit that keeps an Excel .App object from writing data to an Excel file? All comments and feedback greatly appreciated.

Sub TEST()
       
Debug.Print Chr(10) & "|--TEST--|" & Chr(10)

Dim filePath As String, fileName As String

Dim fileTabName As String

Dim DataFile As Excel.Application  ' process support var
Dim DataFile_Tab As Worksheet      ' process support var

Dim results_Row As Integer, results_Col As Integer

Dim date_Stamp As String

'---------------------------------------------
' Assign FilePath, FileName, FileTabName

filePath = "https://[ SharePoint folder path here ]/"
        
fileName = "Test_Target.xlsx?web=1"

fileTabName = "Target"

'---------------------------------------------
' Establish connection to Excel Data File

Set DataFile = New Excel.Application

DataFile.Workbooks.Open _
  filePath & fileName, _
  True, _
  True                       ' filePath & fileName & fileTabName, _

Set DataFile_Tab = DataFile.Worksheets(fileTabName)
    
date_Stamp = Format(Now(), "dd/mm/yyyy")

results_Row = 2
results_Col = 2
            
Debug.Print "DataFile_Tab.Cells(" & results_Row & ", " & results_Col & ").Value = '" & _
    date_Stamp & "'"

DataFile_Tab.Cells(results_Row, results_Col) = date_Stamp
DataFile_Tab.Cells(results_Row, results_Col).Value = date_Stamp
            
'---------------------------------------------
' Terminate connection to Excel Data File

Set DataFile = Nothing
Set DataFile_Tab = Nothing

End Sub

score 1 · Answer 1 · answered Nov 17 '22 at 22:00

You're opening the workbook in read-only mode (3rd parameter of Workbooks.Open), and never saving it. Try the following (untested).

Dim app As Excel.Application
Set app = New Excel.Application

Dim dataFile As Excel.Workbook
Set dataFile = app.Workbooks.Open(filePath & fileName, True)

Dim tabName As String
tabName = "Target"

Dim resultsRow As Long, resultsCol As Long
resultsRow = 2
resultsCol = 2

dataFile.Worksheets(tabName).Cells(resultsRow, resultsCol).Value = Date
dataFile.Save
dataFile.Close

app.Quit

Thanks for the prompt response, BigBen. Will try the refined object assignment approach you suggest. — Jef Knutson, Nov 17 '22 at 22:06

In PowerPoint VBA: Would like to write data to an Excel File (via Excel App.Object)

1 Answers1