VBA to turn off web query connection

[gvillepa]

HI,

This runs a macro every 5 minutes in order to .SaveCopyAs.

The master file conducts a power query from web every 1 minute. I .SaveCopyAs to another file name/location every 5 minutes. Obviously, it saves an actual copy of the master file, including the webconnection (which is set to run on open for the master file - and it needs to stay that way)

Basically, I want the cloned copy to not have the web query connection and I'm hoping there is a way to disable the web query connection on the master file, then savecopyas, then re-enable web query connection so the master file can resume making clones every 5 minutes and refreshing every 1 minute.

Any thoughts or better approaches?

Public dTime As DateSub Workbook_Open()
    dTime = Now + TimeValue("00:00:05")
    Application.OnTime dTime, "MyMacro"
End Sub
Sub Workbook_BeforeClose(Cancel As Boolean)
   Application.OnTime dTime, "MyMacro", , False
End Sub


Sub MyMacro()
    dTime = Now + TimeValue("00:02:00")
    Application.OnTime dTime, "MyMacro"
     
    ActiveWorkbook.SaveCopyAs ("C:\Users\TheNameOfUser\Desktop\Currency\" & Format(Now(), "yyyy-mmdd-hhmm") & ".xlsm")
     


End Sub