Basic Excel Macro Question

[zhidzhid]

Hello all,

I'm definitely very green when it comes to both coding macros and vb, so I'm having a difficult time with something I'm hoping is relatively easy (maybe just properly implementing a try/catch?) The code is as follows:

Sub TestMacro()
'
' TestMacro Macro
'

'
    Sheets("Data").Select
    Range("B15").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Source SAP Data").Select
    Range("A14").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTable1").PivotSelect _
        "'[Project Code].[Project Code]'[All]", xlLabelOnly + xlFirstRow, True
    Sheets("SFDC to OLAP Comparison").Select
    Range("C8").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
End Sub

Basically the script refreshes a few tables - the one problem happens on this line: Selection.QueryTable.Refresh BackgroundQuery:=False

That brings up a file dialog, and if the user cancels instead of choosing a file to import, a run-time error 1004 is thrown. Any ideas on how to prevent that error from being thrown, and instead stop the script from executing any further (or just ignore the error...)

Any help would be greatly appreciated!

 

[RG FL]

How about coding for RefreshAll instead.

RefreshAll is on the external data menu if you want to record a macro for it.

The following may also help at the beginning of your code:

Application.EnableCancelKey = xlDisabled

 

[KWMSeattle]

I'm not sure exactly how your workbook is set up, but here is some code I use to go through one of my files and refresh all pivot tables. Maybe this will help.

Sub RefreshPivots()
 
Application.ScreenUpdating = False
 
' Cycle through worksheets and refresh all pivot tables found
    Dim wBook As Workbook, wSheet As Worksheet, pTable As PivotTable
 
    Set wBook = ThisWorkbook
    For Each wSheet In wBook.Worksheets
         For Each pTable In wSheet.PivotTables
                 pTable.RefreshTable
         Next pTable
    Next wSheet
 
Application.ScreenUpdating = True
 
End Sub

 

[zhidzhid]

Hey guys,

Thanks for the advice - unfortunately, the refreshing has to be done in a certain order so a refreshall isn't an option (and likely wouldn't fix the cancel issue either), and disabling the cancel key is more counterproductive than having to deal with an error (as users may be forced to import a completely random file in order to get rid of the dialog).

The table that's being refreshed is a data table that's pulled from another excel file and copied in - I don't know if that helps, but it's not a pivottable.