Solved: How to read Excel data into a userform in PPT

[TrippyTom]

Hi gang,

I recently bought an add-on from Steve Rindsberg's PPTOOLS website that does data merges in PPT wonderfully! http://www.pptools.com/merge/index.html

But I want to know how to do it myself via code. I made a form and figured out how to bring up the file browser window and put the choice into a textbox on my form. But now I have to figure out how to read in the data. Do I need to know SQL for this, or where should I start hunting?

[RolfJ]

Reference the Microsoft Excel 11.0 (or 12.0) Object Library in your VBA project (using Tools | References from the VBE main menu) and then use the following skeleton code to connect your PowerPoint macro to an Excel workbook (you need to provide the full path name to the constant WB_FILEPATH):

 
Const WB_FILEPATH As String = "C:\Users\...\???.xls"
 
Sub ReadingDataFromExcelWorkbook()
    Dim xCel As Excel.Application
    Dim wb As Excel.Workbook
    Set xCel = GetObject(, "Excel.Application.11")
    Set wb = xCel.Workbooks.Open(WB_FILEPATH)
 
    'Now use syntax like
    'wb.Sheets("{sheet name}").Range("A1").value to obtain the cell values
    ' and assign them to your form variables
    '
    wb.Close (False)
End Sub

In and by itself this macro does not do much, but if you were to place a breakpoint on the End Sub line you could explore the workbook you just opened by using, in the Immediate Window of the VB Editor, commands like

? wb.Sheets("{sheetname}").Range("A1").Value

Once you understand how to get to the specific cells whose value you would like to transfer to your user form in your PowerPoint application you just assign those form varibles to the intended cell values.

Hope this helped,
Rolf

[TrippyTom]

Thanks Rolf, that will be very helpful.

[John Wilson]

Hi Tom

I could be wrong here but I think GetObject will fail if Excel is not open. If it does you could try something like this

[vba]On Error Resume Next
Err.Clear
Set xCel = GetObject(Class:="Excel.Application")
If Err <> 0 Then
Set xCel = CreateObject(Class:="Excel.Application")
End If[/vba]