Macro to open, refresh all, save & close

[drue66]

Hello,

I am using Excel 2007 and I would like to see if there is a macro I can create that will automatically open a document, refresh all connections, save and close. I would like to set this up to run on some sort of timed interval.

I am unable to set up this particular document to auto refresh when opened, for whatever reason, this just won't work even though I have selected this option in my data table and pivot table; however, if I manually select refresh all everything refreshes.

Any help would be much appreciated.

Thanks!
Andrea

 

[pboltonchina]

Try this on a copy of your workbooks. Select the folder containing all your workbooks to refresh and run it.

Sub RunCodeOnAllXLSFiles()
    Dim ws As Worksheet
    Dim MyFile As String
    Dim lCount As Long
    Dim i As Long
    Dim wbResults As Workbook, wbCodeBook As Workbook
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            On Error Resume Next
            Set wbCodeBook = ActiveWorkbook
            Set ws = Sheets("Sheet1")           
            lastRow = Range("A65536").End(xlUp).Row
            For i = i To lastRow
              [B][COLOR=red]  'Change path to suit
[/COLOR][/B]                MyFile = "H:\Operation Analyse\2009 budgets\Yr2009 Working Folders\" _
                 & ws.Range("A" & i) & "\" & ws.Range("B" & i) & "\" & ws.Range("C" & i)
                        
            With .FileSearch
                .NewSearch
                .LookIn = MyFile
                .FileType = msoFileTypeExcelWorkbooks
                .filename = "*.xls"
                    If .Execute > 0 Then 'Workbooks in folder
                        For lCount = 1 To .FoundFiles.Count 'Loop through all.
                            'Open Workbook x and Set a Workbook variable to it
                            Set wbResults = Workbooks.Open(filename:=.FoundFiles(lCount), UpdateLinks:=0)
                                ActiveWorkbook.RefreshAll                            
                                wbResults.Close savechanges:=True
                        Next lCount
                    End If
            End With
            On Error GoTo 0
            Next i
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
  
End Sub

 

[drue66]

Hey there,

Thanks so much for this. I'm trying to test this out, and I don't claim to know what I'm doing, but I think that I found one line in the code that is no longer supported in Excel 2007 -> With .FileSearch
Is there something I should replace this with, or should I eliminate it? I honestly know just about nothing about VBA code, so I apologize in advance and can't thank you enough for your assistance!

Andrea

 

[pboltonchina]

Try this

Sub LoopThroughFolder()
    Dim folderPath As String
    Dim filename As String
    Dim WB As Workbook
    [B][COLOR=#ff0000]'Change path to suit
[/COLOR][/B]    folderPath = "C:\temp\excel\"
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xlsx")
    Do While filename <> ""
        Set WB = Workbooks.Open(folderPath & filename)
        
        'Call a subroutine here to operate on the just-opened workbook
        ActiveWorkbook.RefreshAll                            
       wbResults.Close savechanges:=True        
        
        filename = Dir
    Loop
    
End Sub

 

[KanagamNagarajan]

Hi,

Hope you are doing well
When i am executing the macro the line "Activeworkbook.RefreshAll" is not executing. but when i am execute the vba in step-by-step mode it will execute.
Can you please help me???

Rgds,
Nagaraj