I’m trying to use
Private Sub Workbook_Open()
Dim WeekNum As String
Dim QueryName As Variant
WeekNum = WorksheetFunction.WeekNum(Now, vbSunday)
QueryName = ("2024Week" & WeekNum)
ActiveWorkbook.Queries("QueryName").Refresh
End Sub
Essentially I want the apporiate query to refresh based on the name of the query containing the same weeknumber as the current week of the year. I cannot seem to find any information about this.
Here is the error I receive:
Run-time error Cannot find a query by the name ‘QueryName’
1
Remove the quotes and moop through the existing connections to find the query that matches the name you are providing :
Private Sub Workbook_Open()
Dim WeekNum As String
Dim QueryName As String
Dim conn As WorkbookConnection
WeekNum = WorksheetFunction.WeekNum(Now, vbSunday)
QueryName = "2024Week" & WeekNum
For Each conn In ActiveWorkbook.Connections
If conn.Name = QueryName Then
conn.Refresh
Exit For
End If
Next conn
End Sub
2
Add code to validate the existence of the query.
Private Sub Workbook_Open()
Dim WeekNum As String
Dim QueryName As String
Dim objQuery As Object
WeekNum = WorksheetFunction.WeekNum(Now, vbSunday)
QueryName = "2024Week" & WeekNum
On Error Resume Next
Set objQuery = ActiveWorkbook.Queries(QueryName)
On Error GoTo 0
If objQuery Is Nothing Then
MsgBox "The query [" & QueryName & "] does not exist."
Else
objQuery.Refresh
End If
End Sub