Manage Excel workbooks from a Powerpoint macro (VBA)

[Adam Brave]

Hi!
I have the following function (VBA code) that I usually use within some Excel macros. It allows me to list the workbooks that I have open and select one of them. It works well.

Function PromptForWorkbook() As String    Dim N As Long
    Dim s As String
    Dim WB As Workbook
    For Each WB In Workbooks
        N = N + 1
        s = s & CStr(N) & " - " & WB.Name & vbNewLine
        'CStr deve converter o número N numa string
    Next WB
    
    N = Application.InputBox( _
    prompt:="Select the source data excel file, by the ID number:" & _
    vbNewLine & s, Type:=1)
    If N <= 0 Or N > Workbooks.Count Then
        PromptForWorkbook = vbNullString
    Else
        PromptForWorkbook = Workbooks(N).Name
    End If
End Function

Now, I would like to adapt the code above to run it from a PowerPoint macro with the same purpose, i.e. have a PowerPoint macro the allow me to select one of the several Excel workbooks I have open. This is what I've done so far but as you may guess it is not working properly. One of the things that work differently in a Powerpoint macro when compared to a Excel macro is the InputBox function which in Excel can return a number while in Powerpoint only returns strings. I've adapted the code but it still does not work. It doesn't even list the Excel files I have open.

Function PromptForWorkbook() As String    Dim N As Long
    Dim s As String
    Dim myString As String
    Dim WB As Workbook
    For Each WB In Workbooks
        N = N + 1
        s = s & CStr(N) & " - " & WB.Name & vbNewLine
        'CStr deve converter o número N numa string
    Next WB
    
    myString = InputBox( _
    prompt:="Select the source data excel file, by the ID number:" & _
    vbNewLine & s)
    N = CInt(myString)
    If N <= 0 Or N > Workbooks.Count Then
        PromptForWorkbook = vbNullString
    Else
        PromptForWorkbook = Workbooks(N).Name
    End If
End Function

I guess I need some kind of reference to an instance of Excel but I'm not sure how exactly how I can do it. Can someone help me?

Thanks in advance

[westconn1]

this should fix your code

Function PromptForWorkbook() As String
    Dim N As Long
    Dim s As String
    Dim myString As String
    Dim WB As Object
    Dim xl As Object
    Set xl = GetObject(, "excel.application")
    For Each WB In xl.workbooks
        N = N + 1
        s = s & CStr(N) & " - " & WB.Name & vbNewLine
        'CStr deve converter o número N numa string
    Next WB
    
    myString = InputBox( _
    prompt:="Select the source data excel file, by the ID number:" & _
    vbNewLine & s)
    N = CInt(myString)
    If N <= 0 Or N > xl.workbooks.Count Then
        PromptForWorkbook = vbNullString
    Else
        PromptForWorkbook = xl.workbooks(N).Name
    End If
End Function

this is tested to work correctly, but not from within powerpoint

[Adam Brave]

this should fix your code

Function PromptForWorkbook() As String
    Dim N As Long
    Dim s As String
    Dim myString As String
    Dim WB As Object
    Dim xl As Object
    Set xl = GetObject(, "excel.application")
    For Each WB In xl.workbooks
        N = N + 1
        s = s & CStr(N) & " - " & WB.Name & vbNewLine
        'CStr deve converter o número N numa string
    Next WB
    
    myString = InputBox( _
    prompt:="Select the source data excel file, by the ID number:" & _
    vbNewLine & s)
    N = CInt(myString)
    If N <= 0 Or N > xl.workbooks.Count Then
        PromptForWorkbook = vbNullString
    Else
        PromptForWorkbook = xl.workbooks(N).Name
    End If
End Function

this is tested to work correctly, but not from within powerpoint

Thanks. I give it a try with powerpoint anyway.
It doesn't like of the line:

Set xl = GetObject(, "excel.application")

Throws the following error:

Runtime error 429
"ActiveX component can't create Object"

Is this related to powerpoint or is something related with a missing reference?

Thanks

[westconn1]

the error would suggest there is no currently open instance of excel

Is this related to powerpoint or is something related with a missing reference?

there is no need for a reference when using late binding of objects (getobject or createobject) this is ideal when you may not know what version of excel is installed to add a correct reference