Results 1 to 7 of 7

Activeworkbook.Refresh All stopping code

This is a discussion on Activeworkbook.Refresh All stopping code within the Excel Questions forums, part of the Question Forums category; Hi All, I have a workbook that sucks data in from Access, I was using the following code 3 times ...

  1. #1
    Board Regular Mike Blackman's Avatar
    Join Date
    Jun 2007
    Location
    Basingstoke, UK
    Posts
    2,445

    Default Activeworkbook.Refresh All stopping code

    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
    Regards

    Mikey B

  2. #2
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    6,399

    Default Re: Activeworkbook.Refresh All stopping code

    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

  3. #3
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Activeworkbook.Refresh All stopping code

    Hi Mikey

    What happens if you step thru the code using F8 in the VBE? Does it get past the RefreshAll line?
    Richard Schollar

    Using xl2013

  4. #4
    Board Regular Mike Blackman's Avatar
    Join Date
    Jun 2007
    Location
    Basingstoke, UK
    Posts
    2,445

    Default Re: Activeworkbook.Refresh All stopping code

    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?)
    Regards

    Mikey B

  5. #5
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Activeworkbook.Refresh All stopping code

    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?
    Richard Schollar

    Using xl2013

  6. #6
    Board Regular Mike Blackman's Avatar
    Join Date
    Jun 2007
    Location
    Basingstoke, UK
    Posts
    2,445

    Default Re: Activeworkbook.Refresh All stopping code

    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?
    Regards

    Mikey B

  7. #7
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Activeworkbook.Refresh All stopping code

    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.
    Richard Schollar

    Using xl2013

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com