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