Macro to refresh n disable query

[vbacoder12]

Hi I am not too familiar with Marcos. But is there a macro that can be written to refresh a single query then disable refreshing. If so can you please show me the code which would do this.i do not know where to begin

 

[Misca]

You can "sort of disable" the query refreshing by uncheking the "Refresh this connection on Refresh All" found in the Query Properties. If you protect the worksheet ( with or without a password ) your query is about as disabled as it gets without deleting the actual query.

When you're done this you can use simple macro to unprotect the sheet, refresh the query and protect the sheet again:

Dim PW As String

PW = "123"          ' Set the password

    ActiveSheet.Unprotect (PW)  ' Unprotect active sheet using the password
    ActiveWorkbook.Connections("Query - YourQueryName").Refresh      ' Refresh the Query
    ActiveSheet.Protect (PW)    'Protect active sheet using the password

If you don't want to use a password to protect / unprotect your worksheet remove the "(PW)" from the code.

Replace the ActiveSheet references with actual sheet references to make the macro more error proof. As it is the macro will crash if you're running the macro from a wrong sheet.

If you don't know what is the name of your query you can record a macro when you refresh the macro manually. You can't refresh the queries on protected sheets so do this before protecting the sheet or when the sheet is unprotected.

 

[vbacoder12]

You can "sort of disable" the query refreshing by uncheking the "Refresh this connection on Refresh All" found in the Query Properties. If you protect the worksheet ( with or without a password ) your query is about as disabled as it gets without deleting the actual query.

When you're done this you can use simple macro to unprotect the sheet, refresh the query and protect the sheet again:

Dim PW As String

PW = "123"          ' Set the password

    ActiveSheet.Unprotect (PW)  ' Unprotect active sheet using the password
    ActiveWorkbook.Connections("Query - YourQueryName").Refresh      ' Refresh the Query
    ActiveSheet.Protect (PW)    'Protect active sheet using the password

If you don't want to use a password to protect / unprotect your worksheet remove the "(PW)" from the code.

Replace the ActiveSheet references with actual sheet references to make the macro more error proof. As it is the macro will crash if you're running the macro from a wrong sheet.

If you don't know what is the name of your query you can record a macro when you refresh the macro manually. You can't refresh the queries on protected sheets so do this before protecting the sheet or when the sheet is unprotected.

Thanks. I used the delQueries sub.which removed the q
uery connections from the workbook.