CODEWITHSUNDEEP
excelvbapowerpoint
rcmv
rcmv

Reputation: 151

Manage workbooks from a Powerpoint macro (VBA)

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 adapt that 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

Can someone help me?

Thanks in advance

Upvotes: 0

Views: 92

Answers (1)

The_Intern 2.0
The_Intern 2.0

Reputation: 5

Good morning instead of doing that why don't you create a hyperlink in powerpoint from a button or something and then you can manage your workbooks.

Upvotes: 0

Related Questions