johnny51981
Active Member
- Joined
- Jun 8, 2015
- Messages
- 398
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.
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.
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.
VBA Code:
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.