Code that works on one workbook but not another ??

[gheyman]

I have these two codes that I run on Workbook open. The second runs without any errors. The first one does not.

Note: Sheets("1 Proposal Selection") is visible and not hidden. And the sheet names are different in each workbook.

First

Private Sub Workbook_Open()

'Gets Error message: Run-time error '1004':  Application-defined or object-define error
MsgBox "Please be patient while the workbook updates with the latest data from ProPricer"

Sheets("1 Proposal Selection").Activate
Range("B16").Select

    With ThisWorkbook
         Sheets("1 Proposal Selection").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With

End Sub

Second

Private Sub Workbook_Open()
'code that does not error

Sheets("Step 2").Visible = xlVeryHidden
Sheets("Step 3").Visible = xlVeryHidden
Sheets("DM Parts").Visible = xlVeryHidden
Sheets("DM Assemblies").Visible = xlVeryHidden
Sheets("DM Assembly Elements").Visible = xlVeryHidden
Sheets("Parts").Visible = xlVeryHidden
Sheets("Assemblies").Visible = xlVeryHidden
Sheets("Assembly Elements").Visible = xlVeryHidden
Sheets("Product List").Visible = xlVeryHidden
Sheets("Proposal List").Visible = xlVeryHidden

MsgBox "Please be patient while workbook is updated with ProPricer data."

    With ThisWorkbook
        Sheets("Proposal List").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    End With

End Sub

Don't understand why one works and the other does not.

Thanks!

 

[RoryA]

Which line actually causes the error?

Note: Your With block in the first code is not actually serving any purpose at all.

 

[gheyman]

Which line actually causes the error?

Note: Your With block in the first code is not actually serving any purpose at all.

Sheets("1 Proposal Selection").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

 

[gheyman]

I triple checked the sheet name.

 

[RoryA]

Can you manually refresh the query? (I assume the sheet is not protected?)

 

[gheyman]

Yes.

And if I run this code separately (not on Workbook Open) it works fine.

Sub RefreshProposal()
    
    Sheets("1 Proposal Selection").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

End Sub

 

[RoryA]

You could just set the connection properties to refresh when opening the file?

 

[gheyman]

can you give a little more information on how? Thanks

 

[RoryA]

On the Data tab, click Connections, select the connection, then the Properties button, and check the option to refresh when the workbook opens.