VBA Queries refresh and Save data as TXT

[radonwilson]

I wrote this code to refresh all my queries first and save the "Schedule" table as a .txt file. (Table: "Schedule" is coming from query)
However, the problem with the code is that it does not refresh the queries; it just saves the table's existing/outdated data, which is not required.

I want to perform the refresh first when the refresh gets done completely, then I want to save it as a .txt file.

Sub Refresh_And_Save()
    Dim wb As Workbook
    Dim mySheet As Worksheet
    Dim myTable As ListObject
    Dim currentDate As String
    Dim directory As String
    Dim myFileName As String

    Set wb = ThisWorkbook
    Set mySheet = wb.Sheets("Schedule")
  
    ' Refresh all connections and queries
    wb.RefreshAll
  
    On Error Resume Next
    Set myTable = mySheet.ListObjects("Schedule")
    On Error GoTo 0 ' Reset error handling
  
    If myTable Is Nothing Then
        MsgBox "The 'Schedule' table was not found!"
        Exit Sub
    End If
  
    ' Prepare file name with current date
    currentDate = Format(Date, "(dd-mm-yyyy)")
    directory = wb.Path
    myFileName = directory & "\Bulk_Schedule_" & currentDate & ".txt"
  
    ' Disable screen updating and alerts to avoid UI flicker
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
  
    ' Save the sheet as a .txt (tab-delimited) file
    mySheet.SaveAs Filename:=myFileName, FileFormat:=xlTextWindows
  
    ' Rename the sheet to "Schedule"
    On Error Resume Next
    mySheet.Name = "Schedule"
    On Error GoTo 0 ' Reset error handling
  
    ' Restore screen updating and alerts
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
  
    ' Notify the user about the successful save
    MsgBox "The 'Schedule' sheet has been successfully saved as: " & myFileName
  
End Sub

Here is the google drive link to my Excel Files
MyFiles

 

[Skyybot]

Try changing the Workbook variable Set statement to:

Set wb = Workbooks("Book1.xlsx") 'Change name to suit your needs

 

[Artik]

In the properties of each query, uncheck next to “Enable background refresh”.

Artik