List all Items in Named Range in MsgBox

[johnny51981]

Howdy,
I'm trying to update the following VBA to be dynamic based on the population of a table, rather than being written in the vba.

Public Sub RefreshAll()
' Macro to Refresh All Queries
' Keyboard Shortcut: Ctrl+r

' Refreshes the File Date queries
    Application.CommandBars("Queries and Connections").Visible = True
    Application.CommandBars("Queries and Connections").Width = 700 'Change width as suits.
    DoEvents
         
' Message Box to begin Refresh

        
    Dim answer1 As Integer
    answer1 = MsgBox("Would you like to refresh all the Data?" & vbNewLine & vbNewLine &"Note: Please make sure you added the following updated files to the Raw Data Folder:" & vbNewLine & vbNewLine & "File1.csv" & vbNewLine & vbNewLine & "File2.xlsx" & vbNewLine & vbNewLine & "File3.xlsx" & vbNewLine & vbNewLine & "File4.xlsx" & vbNewLine & vbNewLine & "File5.xlsx" & vbNewLine & vbNewLine & "File6.xlsx" & vbNewLine & vbNewLine & "File7.xlsx" & vbNewLine & vbNewLine & "File8.xlsx" & vbNewLine & vbNewLine & "File9.xlsx" & vbNewLine & vbNewLine & "File10.xlsx", vbQuestion + vbYesNo + vbDefaultButton2, "Refresh All Data")
    If answer1 = vbYes Then
 
' Refreshes all queries, provides Refresh Start and Stop Times, and Refresh Status
    Call Refresh_QueriesOnly
    answer1 = MsgBox("Data will now refresh.", vbOKOnly)
    Else: answer1 = MsgBox("Data refresh has been cancelled.", vbOKOnly)
    End
    End If
' Changes Report Enviornment to "Production"
    Call ReportEnvironment_Production
    
End Sub

Instead of the File names being written, I would like to use the Named Range of "SourceDataFiles" that is already scoped for the Workbook. And it references a Table that has a column of File Names.

 

[johnny51981]

Ok, then I'm getting a type mismatch error with this:

Public Sub RefreshAll()

' Macro to Refresh All Queries
' Keyboard Shortcut: Ctrl+r

' Refreshes the File Date queries
    Application.CommandBars("Queries and Connections").Visible = True
    Application.CommandBars("Queries and Connections").Width = 700 'Change width as suits.
    DoEvents
         
' Message Box to begin Refresh

        
    Dim answer1 As Integer
        answer1 = MsgBox("Would you like to refresh all the Data?" _
                    & vbNewLine & vbNewLine & _
                    "Note: Please make sure you added the following updated files to the Raw Data Folder:" _
                    & vbNewLine & vbNewLine & Join([transpose(SourceDataFiles)], vbLf))
    If answer1 = vbYes Then
 
' Refreshes all queries, provides Refresh Start and Stop Times, and Refresh Status
    Call Refresh_QueriesOnly
    answer1 = MsgBox("Data will now refresh.", vbOKOnly)
    Else: answer1 = MsgBox("Data refresh has been cancelled.", vbOKOnly)
    End
    End If
' Changes Report Enviornment to "Production"
    Call ReportEnvironment_Production
    
End Sub

 

[Engberg]

It would be best if you could censor any sensitive information and share the file via Dropbox/Google Drive/whatever, it's hard to troubleshoot like this.

 

[johnny51981]

I am not able to do that, due to work related restrictions.

 

[Engberg]

This should work?

Public Sub RefreshAll()

' Macro to Refresh All Queries
' Keyboard Shortcut: Ctrl+r

' Refreshes the File Date queries
    Application.CommandBars("Queries and Connections").Visible = True
    Application.CommandBars("Queries and Connections").Width = 700 'Change width as suits.
    DoEvents
        
' Message Box to begin Refresh

        
    Dim answer1 As Integer
        answer1 = MsgBox("Would you like to refresh all the Data?" _
                    & vbNewLine & vbNewLine & _
                    "Note: Please make sure you added the following updated files to the Raw Data Folder:" _
                    & vbNewLine & vbNewLine & Join([transpose(SourceFileNames)], vbLf))
    If answer1 = vbYes Then
 
' Refreshes all queries, provides Refresh Start and Stop Times, and Refresh Status
    Call Refresh_QueriesOnly
    answer1 = MsgBox("Data will now refresh.", vbOKOnly)
    Else: answer1 = MsgBox("Data refresh has been cancelled.", vbOKOnly)
    End
    End If
' Changes Report Enviornment to "Production"
    Call ReportEnvironment_Production
    
End Sub

 

[rlv01]

Ok, then I'm getting a type mismatch error with this:

In your earlier post the named range was SourceFileNames, but your code references SourceDataFiles

Join([transpose(SourceDataFiles)], vbLf)

You will have to change either the code (as per @Engberg's last post) or else change the name of the named range to SourceDataFiles

 

[johnny51981]

This should work?

Public Sub RefreshAll()

' Macro to Refresh All Queries
' Keyboard Shortcut: Ctrl+r

' Refreshes the File Date queries
    Application.CommandBars("Queries and Connections").Visible = True
    Application.CommandBars("Queries and Connections").Width = 700 'Change width as suits.
    DoEvents
       
' Message Box to begin Refresh

       
    Dim answer1 As Integer
        answer1 = MsgBox("Would you like to refresh all the Data?" _
                    & vbNewLine & vbNewLine & _
                    "Note: Please make sure you added the following updated files to the Raw Data Folder:" _
                    & vbNewLine & vbNewLine & Join([transpose(SourceFileNames)], vbLf))
    If answer1 = vbYes Then
 
' Refreshes all queries, provides Refresh Start and Stop Times, and Refresh Status
    Call Refresh_QueriesOnly
    answer1 = MsgBox("Data will now refresh.", vbOKOnly)
    Else: answer1 = MsgBox("Data refresh has been cancelled.", vbOKOnly)
    End
    End If
' Changes Report Enviornment to "Production"
    Call ReportEnvironment_Production
   
End Sub

It does! Thank you!