Activeworkbook.Refresh All stopping code

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi All,

I have a workbook that sucks data in from Access, I was using the following code 3 times to refresh the tables;

Code:
.Range("A1").QueryTable.Refresh BackgroundQuery:=False

Worked fine, Jon von der Heyden suggested that I use;

Code:
Activeworkbook.Refreshall

In its stead, This seems to work much much quicker than my previos code but the rest of my code does not run after the refresh.

I know the obvious answer is to use the querytable.Refresh but can't understand why the Refreshall would block my code. I don't get any errors its simply that it seems to ignore my code.

Can anybody shed any light on this?

My full code is
Code:
Sub Data_Integration()

Dim lRow As Long

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

ActiveWorkbook.RefreshAll

lRow = Sheets("OCC Data").Range("A" & Rows.count).End(xlUp).Row

With Sheets("OCC Data")    
    For i = 2 To lRow
        If .Cells(i, 32) = "00/00/0000" Then
            .Cells(i, 32).ClearContents
            .Cells(i, 33).ClearContents
        End If
    Next i
    
    .Columns("AF").TextToColumns Destination:=.Range("AF1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
    .Columns("AG").TextToColumns Destination:=.Range("AG1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
    
End With
With Sheets("Cost Data")
    .Columns("E").TextToColumns Destination:=.Range("E1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End With
With Sheets("Timesheet Data")
     .Columns("I").TextToColumns Destination:=.Range("I1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
    .Columns("J").TextToColumns Destination:=.Range("J1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
    .Columns("K").TextToColumns Destination:=.Range("K1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End With
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I'm not seeing the conclusion of your code.

Your...
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Where is this being RESTORED to:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
 
Upvote 0
Hi Jim,

Sorry forgot to add to the post, it is there in my code promise!

Hi Rich,

Strangely, it works when I step through with F8, I know this becaise the IF statement works perfectly, it just doesn't when the code is run in full.

PS. I never knew you could step thru with F8 (Is this one of the things that you don't admit to?)
 
Upvote 0
I'm not sure why this wouldn't work - something to try (and have no idea if this will show anything or not): in the VBE go Tools>Options>General tab and make sure you have "Notify Before State Loss" checked. Then go back into Excel itself and try running your code again (via Tools>Macro>Macros). Do you get a warning message displayed?
 
Upvote 0
Hi Rich,

It shows nothing mate, the code runs as pur usual with no errors but again the code beneath doesn't take effect.

Is it possible that the query is still refreshing until the code has finished and thats why we don't see the changes?
 
Upvote 0
Do you have the BackgroundRefresh property of all your external data ranges set to False? If so, then they will refresh synchronously (ie the refresh will need to complete before the code continues). You could try setting them all to True and try running the code then.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top