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