Use VBA to refesh a query on a protected workbook

[rgrovier]

I have an Excel file that acts as an input form for a mailmerge operation. The Excel file contains an Auto-run VBA program designed to clean-up entries made by the previous user of the file. The worksheet is protected to prevent tampering in all cells except those designated for user input. However, the protected cells are the result of a query that I would like to refresh when the Auto-run VBA program is run upon opening the file.

I can get the query to refresh if I remove the protection but I can't get the VBA program to remove the protection, update the query and then restore the protection.

Any thoughts would be greatly appreciated.

 

[Smitty]

Have you tried:

ActiveSheet.Unprotect "PasswordHere"
   '  Query refresh code
ActiveSheet.Protect "PasswordHere"

You can do the same for WB protection, and/or chage "ActiveSheet" to the actual sheet name.

HTH,

Smitty

 

[rgrovier]

Smitty,

I think I've tried every possible method using "refresh" and every possible position of the command in the script. The rest of the code seems to work fine ... except the querytable refresh. I'm sure I'm missing something basic but I can't for the life of me figure it out. Here is what I have ...

Sub Auto_Open()
'
' Auto_Open Macro
'
'
Application.ScreenUpdating = False
Worksheets("Build-A-Label").Unprotect ("") 'currently the password is blank
    Worksheets("Build-A-Label").Range("b5").QueryTable.Refresh
Sheets("Label List").Select
    Worksheets("Label List").Range("A2:iv65536").ClearContents
    Selection.ClearContents
    Worksheets("Label List").Range("A2").Select
Sheets("Build-A-Label").Select
    Range("A5:A65536").Select
    Selection.ClearContents
    Worksheets("Build-A-Label").EnableCalculation = True
    Worksheets("Build-A-Label").EnableCalculation = False
With Worksheets("Build-A-Label")
    EnableSelection = xlUnlockedCells
    Protect AllowFiltering:=True
End With
Application.ScreenUpdating = True
Range("A5").Select
End Sub

Thanks!
Reich