Excel 2007 data refresh that used to work with sheet protected

Question

We have just moved from excel 2003 to 2007 and I wrote this code to refresh a data connection then use the results for various bits.
The problem is if I run through it by pressing F8 it works but when the macro is run by itself I get an error about the cells being protected even though my code contains a Unprotected and protect macro.

Sub code()
UnPro 'Macro to Unprotected worksheet 
Dim p As Integer
Dim q As Integer
Dim lastnew As Long
Dim LastOld As Long
Dim pctCompl As Single
Dim x As Integer
Dim y As Integer

pctCompl = 5 'Update Progress Bar
progress pctCompl 'Update Progress Bar

'Range("A6").QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Connections("Connection").Refresh

pctCompl = 10 'Update Progress Bar
progress pctCompl 'Update Progress Bar


p = 0
q = 0

Do Until q = 1
    If Sheets("Main").Range("G4").Offset(p, 0).Text <> "" Then
        p = p + 1
    Else
        q = 1
        lastnew = p + 3
    End If
Loop

x = 0
y = 0

pctCompl = 50 'Update Progress Bar
progress pctCompl 'Update Progress Bar

Do Until y = 1
    If Sheets("Main").Range("H4").Offset(x, 0).Value <> "" Then
        x = x + 1
    Else
        y = 1
        LastOld = x + 3
    End If
Loop

pctCompl = 70 'Update Progress Bar
progress pctCompl 'Update Progress Bar

If LastOld <> lastnew Then
    Range(Cells(LastOld - 1, 8), Cells(LastOld - 1, 8)).Select
    Selection.AutoFill Destination:=Range(Cells(LastOld - 1, 8), Cells(lastnew, 8)), Type:=xlFillDefault
End If

pctCompl = 100 'Update Progress Bar
progress pctCompl 'Update Progress Bar
ProgBar.Hide
ProTe 'Macro to Protected worksheet 
End Sub

I have tried to us DoEvents after the ActiveWorkbook.Connections("Connection").Refresh but that did not work. also I don't want to use a wait really because sometimes there may be 10 new lines and others the may be thousands.

Rory Rory · Accepted Answer · 2015-08-04T10:34:50

Change the properties of the connection so that it doesn't refresh in the background. That will force the code to wait while it refreshes before continuing.

Excel 2007 data refresh that used to work with sheet protected

1 Answers