?
Solved

run-time error 91 Object Variable or With Block not set. ....  EXCEL VBA

Posted on 2015-03-13
20
Medium Priority
?
2,684 Views
Last Modified: 2016-02-11
This workbook opens up another and updates it - Keep getting the error shown below with  this line highlighted - but not in every sheet it hits - they are random and it will change

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Daily-Trck.png
Here is the code

Sub Auto_Open()
'
' Auto_Open Macro
' Macro updated 2/11/15 by Helen Billingham
'
'Open Daily Tracking.xlsm and refresh all active tabs

    Workbooks.Open ("V:\DPMTS\MATCON\PP&DProjectTeam\TrackingReport\Daily Tracking.xlsm")
    
    Sheets("TwizzlerBonus").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("BonusPckgCandy").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("Halloween").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("CanadianHalloween").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select
    
    Sheets("Holiday").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select
    
    Sheets("CanadianHoliday").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select
    
    Sheets("Valentines").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("CanadianValentines").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("Easter").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("CanadianEaster").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    'Sheets("FallBaking").Select
    'Range("J2").Select
    'ActiveSheet.Unprotect
    'Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    'Range("J2").Select
    

'TwizzlerBonus Formatting

    Sheets("TwizzlerBonus").Select
    Range("J2").Select
    Range("A2:AW18").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:G18").Select
    Selection.NumberFormat = "0.00%"
    Range("J2:v18").Select
    Selection.NumberFormat = "#,##0"
    Range("H2:I18").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("w2:w18").Select
    Selection.NumberFormat = "$#,##0"
    Range("x2:Am18").Select
    Selection.NumberFormat = "#,##0"
    Range("An2:An18").Select
    Selection.NumberFormat = "0.00%"
    Range("Ao2:Ar18").Select
    Selection.NumberFormat = "#,##0"
    Range("J2:AS18").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("J2").Select
    
    Columns("AU:AU").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("J2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'BonusPckgCandy Formatting

    Sheets("BonusPckgCandy").Select
    Range("H2").Select
    Range("A2:AP15").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:G15").Select
    Selection.NumberFormat = "0.00%"
    Range("H2:H15").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("I2:T15").Select
    Selection.NumberFormat = "#,##0"
    Range("U2:U15").Select
    Selection.NumberFormat = "$#,##0"
    Range("V2:AK15").Select
    Selection.NumberFormat = "#,##0"
    Range("AL2:AL15").Select
    Selection.NumberFormat = "0.00%"
    Range("AM2:AP15").Select
    Selection.NumberFormat = "#,##0"
    Range("H2:AP15").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("H2").Select

    Range("H2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'Halloween Formatting

    Sheets("Halloween").Select
    Range("K2").Select
    Range("A2:AX405").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("H2:H405").Select
    Selection.NumberFormat = "0.00%"
    Range("K2:W405").Select
    Selection.NumberFormat = "#,##0"
    Range("I2:J405").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("X2:X405").Select
    Selection.NumberFormat = "$#,##0"
    Range("Y2:AN405").Select
    Selection.NumberFormat = "#,##0"
    Range("AO2:AP405").Select
    Selection.NumberFormat = "0.00%"
    Range("AQ2:AT405").Select
    Selection.NumberFormat = "#,##0"
    Range("AX2:AX405").Select
    Selection.NumberFormat = "$#,##0"
    Range("K2:AT405").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("K2").Select
    
    Columns("AV:AX").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("K2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True
   
'CanadianHalloween Formatting

    Sheets("CanadianHalloween").Select
    Range("G2").Select
    Range("A2:AO41").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:S41").Select
    Selection.NumberFormat = "#,##0"
    Range("T2:T41").Select
    Selection.NumberFormat = "$#,##0"
    Range("U2:AJ41").Select
    Selection.NumberFormat = "#,##0"
    Range("AK2:AK41").Select
    Selection.NumberFormat = "0.00%"
    Range("AL2:AO41").Select
    Selection.NumberFormat = "#,##0"
    Range("G2:AO41").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("G2").Select

    Range("G2").Select
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'Holiday Formatting

    Sheets("Holiday").Select
    Range("K2").Select
    Range("A2:AW279").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("H2:H279").Select
    Selection.NumberFormat = "0.00%"
    Range("K2:W279").Select
    Selection.NumberFormat = "#,##0"
    Range("I2:J279").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("X2:X279").Select
    Selection.NumberFormat = "$#,##0"
    Range("Y2:AN279").Select
    Selection.NumberFormat = "#,##0"
    Range("AO2:AP79").Select
    Selection.NumberFormat = "0.00%"
    Range("AQ2:AT279").Select
    Selection.NumberFormat = "#,##0"
    Range("K2:AT279").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("K2").Select
    
    Columns("AV:AW").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("K2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'CanadianHoliday Formatting

    Sheets("CanadianHoliday").Select
    Range("G2").Select
    Range("A2:AO55").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:S55").Select
    Selection.NumberFormat = "#,##0"
    Range("T2:T55").Select
    Selection.NumberFormat = "$#,##0"
    Range("U2:AJ55").Select
    Selection.NumberFormat = "#,##0"
    Range("AK2:AK55").Select
    Selection.NumberFormat = "0.00%"
    Range("AL2:AO55").Select
    Selection.NumberFormat = "#,##0"
    Range("G2:AO55").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("G2").Select

    Range("G2").Select
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True


'Valentines Formatting

    Sheets("Valentines").Select
    Range("K2").Select
    Range("A2:AW215").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("H2:H215").Select
    Selection.NumberFormat = "0.00%"
    Range("K2:W215").Select
    Selection.NumberFormat = "#,##0"
    Range("I2:J215").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("X2:X215").Select
    Selection.NumberFormat = "$#,##0"
    Range("Y2:AM215").Select
    Selection.NumberFormat = "#,##0"
    Range("AO2:AP215").Select
    Selection.NumberFormat = "0.00%"
    Range("AQ2:AT215").Select
    Selection.NumberFormat = "#,##0"
    Range("K2:AT215").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("K2").Select
    
    Columns("AV:AW").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("K2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'CanadianValentines Formatting

    Sheets("CanadianValentines").Select
    Range("G2").Select
    Range("A2:AO29").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:S29").Select
    Selection.NumberFormat = "#,##0"
    Range("T2:T29").Select
    Selection.NumberFormat = "$#,##0"
    Range("U2:AJ29").Select
    Selection.NumberFormat = "#,##0"
    Range("AK2:AK29").Select
    Selection.NumberFormat = "0.00%"
    Range("AL2:AO29").Select
    Selection.NumberFormat = "#,##0"
    Range("G2:AO29").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("G2").Select

    Range("G2").Select
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True


'Easter Formatting

    Sheets("Easter").Select
    Range("K2").Select
    Range("A2:AX355").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("H2:H355").Select
    Selection.NumberFormat = "0.00%"
    Range("K2:W355").Select
    Selection.NumberFormat = "#,##0"
    Range("I2:J355").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("X2:X355").Select
    Selection.NumberFormat = "$#,##0"
    Range("Y2:AN355").Select
    Selection.NumberFormat = "#,##0"
    Range("AO2:AP355").Select
    Selection.NumberFormat = "0.00%"
    Range("AQ2:AT355").Select
    Selection.NumberFormat = "#,##0"
    Range("AX2:AX355").Select
    Selection.NumberFormat = "$#,##0"
    Range("K2:AT355").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("K2").Select
    
    Columns("AV:AX").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("K2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'CanadianEaster Formatting

    Sheets("CanadianEaster").Select
    Range("G2").Select
    Range("A2:AO48").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:S48").Select
    Selection.NumberFormat = "#,##0"
    Range("T2:T48").Select
    Selection.NumberFormat = "$#,##0"
    Range("U2:AJ48").Select
    Selection.NumberFormat = "#,##0"
    Range("AK2:AK48").Select
    Selection.NumberFormat = "0.00%"
    Range("AL2:AO48").Select
    Selection.NumberFormat = "#,##0"
    Range("G2:AO48").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("G2").Select

    Range("G2").Select

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True


'FallBaking Formatting

    'Sheets("FallBaking").Select
    'Range("K2").Select
    'Range("A2:AX43").Select
    'With Selection
    '    .VerticalAlignment = xlBottom
    '    .WrapText = False
    '   .Orientation = 0
    '    .ShrinkToFit = True
    '    .MergeCells = False
   'End With
    'With Selection.Font
     '   .Name = "Arial"
     '   .FontStyle = "Regular"
     '   .Size = 8
     '   .Strikethrough = False
     '   .Superscript = False
     '   .Subscript = False
     '   .OutlineFont = False
     '   .Shadow = False
     '   .Underline = xlUnderlineStyleNone
     '   .ColorIndex = 1
    'End With
    'Range("H2:H43").Select
    'Selection.NumberFormat = "0.00%"
    'Range("K2:W43").Select
    'Selection.NumberFormat = "#,##0"
    'Range("I2:J43").Select
    'Selection.NumberFormat = "m/d/yyyy"
    'Range("X2:X43").Select
    'Selection.NumberFormat = "$#,##0"
    'Range("Y2:AN43").Select
    'Selection.NumberFormat = "#,##0"
    'Range("AO2:AP43").Select
    'Selection.NumberFormat = "0.00%"
    'Range("AQ2:AT43").Select
    'Selection.NumberFormat = "#,##0"
    'Range("AX2:AX43").Select
    'Selection.NumberFormat = "$#,##0"
    'Range("K2:AT43").Select
    'Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    '    SearchOrder:=xlByRows, MatchCase:=False
    'Range("K2").Select
    
    'Columns("AV:AX").Select
    'Selection.Locked = False
    'Selection.FormulaHidden = False
    'Range("K2").Select
    'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    '   , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
    '    :=True
 

       
'Return to first worksheet

    Sheets("TwizzlerBonus").Select
    Range("J2").Select
         
    ActiveWorkbook.Save
            
            ChDir "V:\DPMTS\MATCON\PP&DProjectTeam\TrackingReport"
            ActiveWorkbook.ProtectSharing Filename:= _
            "V:\dpmts\Matcon\PP&DProjectTeam\TrackingReport\DailyTracking" & Format(Date, "M-D-YYYY")
   
            
    Application.Quit
     
End Sub

Open in new window

.
Any ideas - it must be an easy fix...... No?
0
Comment
Question by:dawber39
  • 11
  • 7
  • 2
20 Comments
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40663517
Is there any code in the Worksheet_SelectionChange events in the other workbook?
0
 

Author Comment

by:dawber39
ID: 40663533
There is not - in any of the sheets "Holiday, Halloween, Easter,.. etc"

Just a macro in a module - which I don't think is even called to - this is it:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/7/2008 by DS

'
    Sheets(Array("Easter", "CanadianEaster", "TwizzlerBonus", "CanadianSummer", _
        "BonusPkgCandy")).Select
    Sheets("Easter").Activate
End Sub

Open in new window

0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40663552
The reason I asked is that while I may be missing something, the code you posted looks OK, but if there's an un-handled error in the other workbook, that error may show up in the main workbook. Would it be possible for you to post the two workbooks?

Also, did this problem start after the 2/11/15 changes?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 

Author Comment

by:dawber39
ID: 40663626
I am not sure when it started - I do know it was handed to me this morning by a woman that is currently converting Access, and Excel Applications to 2013 from 2003

How this works - open up the macro enabled copy with my name Richard on it - and it should open Daily Tracking. I know there is an external database involved with some local tables - and some linked Oracle tables - I can send that as well if need be
Macro-Workbook---CopyRichard.xlsm
Daily-Tracking.xlsm
0
 
LVL 51

Assisted Solution

by:Martin Liss
Martin Liss earned 400 total points
ID: 40663746
I'm not familiar with  querytables tables, but from what I've just read, querytables are "built from data returned from an external data source". When I run your code I get the error on the very first sheet and if I do Msgbox activesheet.querytables.count, i not surprisingly get 0. So perhaps the problem is that you are net getting external data for all the sheets all of the time. If so then something like this (where I added lines 2 and 7) for every sheet will fix the problem.

    Sheets("TwizzlerBonus").Select
    If ActiveSheet.QueryTables.Count > 0 Then
        Range("J2").Select
        ActiveSheet.Unprotect
        Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
        Range("J2").Select
    End If

Open in new window

0
 

Author Comment

by:dawber39
ID: 40663794
Now I get error 'Object Doesn't Support This Property or Method - with this Highlighted (If ActiveSheet.QueryTable.Count > 0 Then) - if I step through it - I get the original error also
0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40663856
If that's your actual code then QueryTable should be QueryTables.
0
 

Author Comment

by:dawber39
ID: 40663890
okay - I will try that
0
 

Author Comment

by:dawber39
ID: 40663897
Same thing - even after closed and re-open
0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40663980
Which version of Excel are you using?
0
 

Author Comment

by:dawber39
ID: 40664037
2013
0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40664060
Okay that may explain it because the code works in Excel 2010 and I don't have 2013 so unfortunately I can't help any further. So while someone else may chime in here, it's unlikely so you should probably start a new question and mention that it's 2013 and what I suggested as the solution.
0
 
LVL 24

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 1600 total points
ID: 40664210
Probably some of the tables are created in Excel 2003 and are not Listobjects.
It looks as if that is the case for the sheets Halloween, CanadianHalloween, CanadianHoliday and CanadianEaster.

Try change
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
to
ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False
for those sheets
0
 

Author Comment

by:dawber39
ID: 40665299
That does sound relevant - but

Funny thing is - those sheets you mentioned are the ones the code runs fine in stepping through it with break points, and it is the others TwizzlerBonus, BonusPckgCandy, Holiday, Valentines, and Easter - that fail.... any ideas?
0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40665355
Do those sheets have QueryTables when they fail?
0
 

Author Comment

by:dawber39
ID: 40665374
Yes
0
 

Author Comment

by:dawber39
ID: 40665376
Tried it with Table as it was - and Tables as it should be - with same results
0
 
LVL 24

Accepted Solution

by:
Ejgil Hedegaard earned 1600 total points
ID: 40665379
When I open Daily-Tracking I get an error message about unreadable content.
In the repaired workbook the sheets I mention + the hidden sheets FallBaking2 and Combined don't have a Listobject.
But there are no QueryTable either on the other sheets.
Guessed that was due to the error.
Only the hidden sheet FallBaking2 has a querytable, and the table looks like the data on the other sheets.

To update QueryTables I use the syntax
Worksheets("Sheetname").QueryTables(1).Refresh BackgroundQuery:=False
or sometimes with the 1 replaced by the table name, but typically there are only one on the sheet.

And for update of Listobjects querytables I use the syntax
Worksheets("Sheetname").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

It is not needed to select the sheet, or anything on the sheet to update the table.

Make a new sheet named Sheet1 in Daily-Tracking, and insert this macro in a module, and run to see if the sheet have ListObjects or QueryTables.
Sub ListTables()
    Dim rw As Integer, ws As Worksheet, wsTableList As Worksheet
    Set wsTableList = Worksheets("Sheet1")
    wsTableList.Cells.ClearContents
    wsTableList.Range("A1:C1") = Array("Sheet name", "ListObjects", "QueryTables")
    rw = 1
    For Each ws In Worksheets
        If ws.Name <> wsTableList.Name Then
            rw = rw + 1
            wsTableList.Cells(rw, 1) = ws.Name
            wsTableList.Cells(rw, 2) = ws.ListObjects.Count
            wsTableList.Cells(rw, 3) = ws.QueryTables.Count
        End If
    Next ws
End Sub

Open in new window


To unprotect all worksheets and update the tables, instead of doing it one at a time, you could use
Sub UnprotectAndUpdateTables()
Dim ws As Worksheet, iSheets As Integer, iTables As Integer
    For Each ws In ActiveWorkbook.Worksheets
        iSheets = iSheets + 1
        ws.Unprotect
        If ws.ListObjects.Count > 0 Then
            ws.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            iTables = iTables + 1
        End If
        If ws.QueryTables.Count > 0 Then
            ws.QueryTables(1).Refresh BackgroundQuery:=False
            iTables = iTables + 1
        End If
        If iSheets <> iTables Then
            MsgBox "Sheet " & ws.Name & "has no table"
        End If
    Next ws
End Sub

Open in new window

But you need to get it working first.
0
 

Author Comment

by:dawber39
ID: 40665420
I will give this a shot later on today - I appreciate your help - this has been one pain after another with legacy  apps and what not being converted to 2013. I am almost positive the person is not doing it correctly either - but I get to fix them. If I run into more issues trying it this way - I will post another question. Thanks again
0
 

Author Closing Comment

by:dawber39
ID: 40665429
These people are great up here - the membership is well worth it - Thinking its getting close to upgrade time
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question