none
Open Workbook and UPdate Link. RRS feed

  • Question

  • I got some codes like this....

    I tried different forums and wont work...is something wrong with my workbook?

    'm trying to update links as workbook opens...

     

    Sub OpenUpdate()    
        Workbooks.Open FileNmae:= "C:\My Documents\LinkedBook.xls", UpdateLinks:=xlUpdateLinksAlways  
    End Sub  
    Sub OpenDontUpdate()   
        Workbooks.Open FileNmae:= _   
            "C:\My Documents\LinkedBook.xls", UpdateLinks:=xlUpdateLinksNever   
    End Sub   
    Sub OpenUpdateLinksPerSettings()  
        Workbooks.Open FileNmae:= _  

            "C:\My Documents\LinkedBook.xls", UpdateLinks:=xlUpdateLinksUserSetting  

    End Sub  

    Sub GetFile()
        Workbooks.Open "Put Workbook path here inc the .xls", UpdateLinks:=1
        ActiveWorkbook.Close SaveChanges:=True
        Workbooks.Open "Put Workbook path here inc the .xls", UpdateLinks:=1
        ActiveWorkbook.Close SaveChanges:=True
        Sheets("Name of sheet containing the macro").Select
        Range("A5").Select
        ActiveCell.Value = "Completed"
        Range("A5").Select
     End Sub

    other code when tried...run smoothly with no prob.

    for example

     

    Refresh Pivot Tables
    Those of you that use Pivot Tables will be well aware of their power. However, one draw-back can be that you can only refresh the PivotTables automatically by setting it refresh on open via the PivotTable Options. Lets look at some ways we can refresh all, or chosen Pivot Tables.
    The code below here can be called (Run) via the Worksheet_Activate Event. To get to the Private Module of any Worksheet right click on the sheet name tab and choose View Code. In you would place code like shown below;
    Private Sub Worksheet_Activate()
        Run "PivotMacro"
    End Sub
    Refresh a Single Pivot Table 
    Sub PivotMacro()
    Dim pt As PivotTable
        Set pt = ActiveSheet.PivotTables("MyPivot")
        pt.RefreshTable
    End Sub
    Refresh all Pivot Tables in a Worksheet 
    Sub AllWorksheetPivots()
    Dim pt As PivotTable
        For Each pt In ActiveSheet.PivotTables
            pt.RefreshTable
        Next pt
    End Sub
    Refresh Chosen Pivot Tables in a Worksheet 
    Sub ChosenPivots()
    Dim pt As PivotTable
        For Each pt In ActiveSheet.PivotTables
            Select Case pt.Name
                Case "PivotTable1", "PivotTable4", "PivotTable8"
                    pt.RefreshTable
                Case Else
            End Select
        Next pt
    End Sub
    Refresh All Chosen Pivot Tables in a Workbook 
    Sub AllWorkbookPivots()
    Dim pt As PivotTable
    Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
            For Each pt In ws.PivotTables
                        pt.RefreshTable
            Next pt
        Next ws
    End Sub

     


    Pedie
    • Edited by Pedie Nz Wednesday, July 27, 2011 8:30 PM edited
    Wednesday, July 27, 2011 8:29 PM

Answers