VBA to - Open Excel files, run links and close

[Jamie2002]

How would I even start to write code that would open a file update links and calculate cells and then close the file?

I am looking to do this in turn for 10 files but all from one set of code. Due to system memory all the files won't open at once, max of about 3 and that's with 383mb memory. And I don't want to have to go into each file each time I changed the core data to update them all.

What it is I had a 65mb Excel file (a business model) obviously this was causing problems in itself so I have split it down into a 20mb data file with 9 x 5mb smaller files which all link to the main one.

Thanks

Jamie

 

[BrainB]

Set the macro recorder to record your actions and open/close the files you want.

The updating of links etc. occurs automatically.

You can then, if necessary, adapt the code you get by using the VB Editor.

That is how we all got started.
Regards
BrianB
** Let us know if you get something that works !
================================

 

[Loomah]

I must stress that this has NOT been tested but it should get you on the way to something

Sub OpenAndUpdate()
Dim fso, fold, f1, fyls
Set fso = CreateObject("Scripting.FileSystemObject")
Set fold = fso.GetFolder(ThisWorkbook.Path)
Set fyls = fold.Files
    
    For Each f1 In fyls
        If Not f1.Name = ThisWorkbook.Name Then
            Workbooks.Open Filename:=f1.Name, updatelinks:=True
            With ActiveWorkbook
                ' check opened files
                Debug.Print .Name
                Calculate
                .Close savechanges:=True
            End With
        End If
    Next
    
End SubSub OpenAndUpdate()
Dim fso, fold, f1, fyls
Set fso = CreateObject("Scripting.FileSystemObject")
Set fold = fso.GetFolder(ThisWorkbook.Path)
Set fyls = fold.Files
    
    For Each f1 In fyls
        If Not f1.Name = ThisWorkbook.Name Then
            Workbooks.Open Filename:=f1.Name, updatelinks:=True
            With ActiveWorkbook
                ' check opened files
                Debug.Print .Name
                Calculate
                .Close savechanges:=True
            End With
        End If
    Next
    
End SubSub OpenAndUpdate()
Dim fso, fold, f1, fyls
Set fso = CreateObject("Scripting.FileSystemObject")
Set fold = fso.GetFolder(ThisWorkbook.Path)
Set fyls = fold.Files
    
    For Each f1 In fyls
        If Not f1.Name = ThisWorkbook.Name Then
            Workbooks.Open Filename:=f1.Name, updatelinks:=True
            With ActiveWorkbook
                ' check opened files
                Debug.Print .Name
                Calculate
                .Close savechanges:=True
            End With
        End If
    Next
    
End Sub

The code assumes all the files are in the same folder and there are no other files present other than the main file containing the code. Each file is then opened and updated in turn, closed and saved. Joy!

Happy New Year
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

Got a bit carried away with the pasting of code it seems!!

should simply be

Sub OpenAndUpdate()
Dim fso, fold, f1, fyls
Set fso = CreateObject("Scripting.FileSystemObject")
Set fold = fso.GetFolder(ThisWorkbook.Path)
Set fyls = fold.Files
    
    For Each f1 In fyls
        If Not f1.Name = ThisWorkbook.Name Then
            Workbooks.Open Filename:=f1.Name, updatelinks:=True
            With ActiveWorkbook
                ' check opened files
                Debug.Print .Name
                Calculate
                .Close savechanges:=True
            End With
        End If
    Next
    
End Sub

;-) If a man says something and there are no women there to hear him, is he still wrong?