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.

 

[Engberg]

Replace your "answer1 = ...." row with all of this. Make sure to edit the range and name of "fileNamesRange" in the code to fit your sheet.

    Dim fileNamesRange As Range
    Set fileNamesRange = Range("SourceDataFiles")
    
    fileNamesString = ""
    For i = 1 To fileNamesRange.Rows.Count()
        If i = 1 Then
            fileNamesString = fileNamesString & fileNamesRange.Rows(i).Value
        Else
            fileNamesString = fileNamesString & vbNewLine & fileNamesRange.Rows(i).Value
        End If
    Next
    
    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 & _
    fileNamesString)

 

[JEC]

How about

MsgBox Join([transpose(SourceDataFiles)], vbLf)

 

[Engberg]

How about

MsgBox Join([transpose(SourceDataFiles)], vbLf)

    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))

Clean! What is this syntax even? The Join function I get, but what about the transpose in [brackets] and the SourceDataFiles without telling it to look for a range with that name?

 

[JEC]

If your put a sheet range in brackets, it actually refers to a range. You can also call sheetfunctions within these brackets. It is actually an evaluation.

So [A1:A20] is the same as range("A1:A20")

Here two examples, all the same result

Sub jec()
 'Sample 1
 a = Application.CountA(Range("A1:A3"))
 b = [counta(a1:a3)]
 c = Evaluate("counta(a1:a3)")
 
 'Sample 2
 d = Application.Transpose(Range("A1:A3"))
 e = [transpose(a1:a3)]
 f = Evaluate("transpose(a1:a3)")
End Sub

 

[Engberg]

Cool, thanks

 

[johnny51981]

Thank you for the collective help. I'm a VBA plagiarist, so can you help me better understand what you mean by and how to "Make sure to edit the range and name of "fileNamesRange" in the code to fit your sheet."?

 

[Engberg]

You replace your current "answer1 =" line with the one below, and the named ranged in the code (SourceDataFiles) should be a range consisting of one column and multiple rows. As you described it in the original post it sounded like there might be more information than the file names in your range, in that case you will have to create another named range that only covers the file names and adjust the name in the code as well.

    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))

 

[johnny51981]

You replace your current "answer1 =" line with the one below, and the named ranged in the code (SourceDataFiles) should be a range consisting of one column and multiple rows. As you described it in the original post it sounded like there might be more information than the file names in your range, in that case you will have to create another named range that only covers the file names and adjust the name in the code as well.

    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))

Oh yes, I already have it in the Name Manager as "SourceDataFiles" and see the file names (single column) listed from the selection in the Value column.

I have updated the full VBA to this and I'm receiving a "Method 'Range' of object '_Global' failed.

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 fileNamesRange As Range
    Set fileNamesRange = Range("SourceDataFiles")
   
    fileNamesString = ""
    For i = 1 To fileNamesRange.Rows.Count()
        If i = 1 Then
            fileNamesString = fileNamesString & fileNamesRange.Rows(i).Value
        Else
            fileNamesString = fileNamesString & vbNewLine & fileNamesRange.Rows(i).Value
        End If
    Next
   
    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]

Thanks to JEC you don't need the for loop stuff from my first answer, only the most recent code snippet.