VBA Queries refresh and Save data as TXT

radonwilson

Board Regular
Joined
Jun 23, 2021
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try changing the Workbook variable Set statement to:
VBA Code:
Set wb = Workbooks("Book1.xlsx") 'Change name to suit your needs
 
Upvote 0
In the properties of each query, uncheck next to “Enable background refresh”.

Artik
 
Upvote 0
Solution

Forum statistics

Threads
1,224,543
Messages
6,179,427
Members
452,914
Latest member
echoix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top