code to open and close w/bs

[SV18]

Can anyone give me some code please?

I need a macro to open, save and then close all excel files from a specified location.

When it comes across a file names "Upload Controller" it needs to ignore it and continue with the task.

Can someone please help?

Regards,

 

[btadams]

Try this (on a copy of your directory, just in case):

Sub OpenSaveCloseFiles()
Dim strPath1 As String, strFilename As String
   
   On Error GoTo OpenSaveCloseFiles_Error

    strPath1 = "C:\Data\Temp" '<- Change to your directory path

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    strFilename = Dir(strPath1 & "\*.xls")
        Do While Len(strFilename) > 0
            If strFilename <> "Upload Controller.xls" Then
                Workbooks.Open (strPath1 & "\" & strFilename)
                Application.DisplayAlerts = False
                    With ActiveWorkbook
                        .Save
                        .Close False
                    End With
                Application.DisplayAlerts = True
                strFilename = Dir
            End If
        Loop
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox "All Done!"

   On Error GoTo 0
   Exit Sub

OpenSaveCloseFiles_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenSaveCloseFiles of Module Module1"
End Sub

 

[jproffer]

So you want to open each workbook...do nothing to it....save it (which seems pointless as it was already saved in the state your saving) and close it?

Certainly, you can do what you want, but just out of curiousity....why would you do this?

 

[SV18]

So you want to open each workbook...do nothing to it....save it (which seems pointless as it was already saved in the state your saving) and close it?

Certainly, you can do what you want, but just out of curiousity....why would you do this?

I basically have one file which has links to many others - I need to update the links by opening each file.

I've tried to use the automatic update commands but the files are so big they keep crashing and freezing Excel.

Maybe just open and close then

 

[jproffer]

NO..no, no...I hadn't thought of the automatic links for a reason.

I gotcha now. And no, I'd say you would want to open, save, close just as you were.

Just curious