+ Reply to Thread
Results 1 to 2 of 2

Refresh Query then save as CSV

  1. #1
    Registered User
    Join Date
    04-30-2015
    Location
    Birmingham,England
    MS-Off Ver
    2016
    Posts
    11

    Refresh Query then save as CSV

    Hello,
    I am trying to write a macro in Excel 2016 to refresh a Get & Transform Query on opening a file, then SaveAs .csv (UTF8), then close the application.
    This is designed to run overnight to capture all the changes made to a separate 'Work in Progress' spreadsheet used by a team which is then uploaded to a website. I have a batch file which opens my file with an entry in Task Scheduler that works fine. The query is set to refresh on opening with 'Enable Background Refresh' unticked.
    My code is below (I am no coder this has been cobbled together from various sources). When the file opens, the macro runs ok, csv file is written and the application closes, but the query has not updated. So the question is, how can I delay the 'SaveAs' part of the macro to the point where the query has completed refreshing? FYI query takes about 5 seconds to update at current size.

    Thank You

    Public Sub Auto_Open()

    Dim wbkExport As Workbook
    Dim shtToExport As Worksheet

    Set shtToExport = ThisWorkbook.Worksheets("Sheet2") 'Sheet to export as CSV
    Set wbkExport = Application.Workbooks.Add

    shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
    Application.DisplayAlerts = False 'overwrite without asking
    wbkExport.SaveAs Filename:="S:\Files\MyFile" & ".csv", FileFormat:=xlCSVUTF8, Local:=True
    Application.DisplayAlerts = True
    wbkExport.Close SaveChanges:=False
    ThisWorkbook.Save
    Application.Quit
    End Sub

  2. #2
    Registered User
    Join Date
    04-30-2015
    Location
    Birmingham,England
    MS-Off Ver
    2016
    Posts
    11

    Re: Refresh Query then save as CSV

    I have found a solution of adding this code before the SaveAs functions.

    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Save old data on query refresh to 1st empty cell in next col (done). Remove last 4 chars.
    By MartinS13X in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-19-2015, 06:21 AM
  2. Save old data on web query refresh...
    By outbackid84 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2015, 04:54 PM
  3. Refresh all query & save. not enough time for query to complete.
    By mortphil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-08-2015, 04:26 AM
  4. Replies: 2
    Last Post: 09-29-2013, 06:15 AM
  5. Replies: 1
    Last Post: 09-28-2013, 07:11 PM
  6. [SOLVED] save & close after web query refresh
    By JVLin in forum Excel General
    Replies: 0
    Last Post: 09-28-2005, 12:05 PM
  7. Auto Open file VBA Refresh MS Query Save Close
    By gcutter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1