Use this macro to update all files in a specific folder, with an option to zip each file, after it is updated. The instructions and code are below, and download the free workbook.
In this example, a macro will copy the master ContactInfo sheet from the sample file, and use it to replace the outdated ContactInfo sheets in other files.
The master file contains a list of folders, and you can select a specific folder to update, or let the macro run through all the folders in the list.
Finally, the code can also zip each updated file, and store the zipped file in a different folder.
The workbook contains the master sheet that will be used to update all the other files -- ContactInfo. This sheet contains a list of websites, with a hyperlink to each site.
The code will copy this sheet to each file (Excel 2007 and later).For Excel 2003 files, it will copy the range, and paste it onto the ContactInfo sheet in that file, because the sheet is larger than those in the old versions of Excel.
In your file, you could have different information on your master sheet, but be careful if the sheet contains the following, which might cause problems when copied to another workbook:
On the Setup sheet, fill in the orange cells, with your file information and preferences.
Below the orange cells, there is also a table, where you can enter a list of folders -- both the original files to be updated, and the folders where the zipped copies should be stored.
An ID number is typed in each row, to identify the folder. You will use one of those ID numbers in the "Folder # to update" cell, to specify which folder should be updated.
You can enter as many folders as you need -- just make sure that each folder has a unique ID number.
Before you test the sample code, be sure to make a backup of the files that you plan to update, and any existing zipped files.
If possible, make a testing folder, and add that to your list of file locations. Then, test that folder first, before using the macro on any other files.
On the Setup sheet, there is a button to run the Update macro.
="Update" & IF(rngZip="Yes"," & Zip","") & " Files"
Select the button shape
Click in the formula bar, and type this formula:
The Update Button will run a macro named UpdateContactSheet -- the code is shown below. This code should be added to a regular code module in a workbook, and there are instructions here: Add code to regular module.
After you add the code to your workbook, assign the macro to the button.
Here is the code that updates the files in the selected folder.
Sub UpdateContactSheet() Dim fso As Object Dim fldr As Object Dim file As Object Dim wb As Workbook Dim wbLinks As Workbook Dim wsFL As Worksheet Dim wsL As Worksheet Dim strPath As String Dim strFileFix As String Dim strMsg As String Dim strFile As String Dim strFileTest As String Dim strLinks As String Dim rngFLID As Range Dim rngID As Range Dim rngFldr As Range Dim lUpdate As Long Dim lFldr As Long Dim bRun As Boolean Dim bZip As Boolean Dim strCopy As String Dim strCols As String Dim ZipPath As String Dim ZipDestPath As String Dim ZipDest As String Dim ZipFile As String Dim strMsgEnd As String Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error GoTo errHandler lUpdate = MsgBox("Update all files?", _ vbYesNo + vbDefaultButton2, "Update All?") If lUpdate <> vbYes Then GoTo exitHandler Set wbLinks = ThisWorkbook Set wsFL = wbLinks.Sheets("FileLocs") Set rngFLID = wsFL.Range("FileLocID") Set rngFldr = wsFL.Range("FldrZip") strFileTest = ".xls" strLinks = wsFL.Range("SheetCopy").Value strCopy = "A1:Z10" strCols = "A:Z" ZipPath = wsFL.Range("zippath").Value lFldr = rngFldr.Value Select Case wsFL.Range("rngZip").Value Case "Yes" bZip = True Case Else bZip = False End Select 'check for id For Each rngID In rngFLID If lFldr = 0 Then bRun = rngID > 0 Else bRun = rngID = lFldr End If If bRun = True Then strPath = rngID.Offset(0, 1).Value ZipDestPath = rngID.Offset(0, 2).Value If Right(strPath, 1) <> "\" Then strPath = strPath & "\" End If If Right(ZipDestPath, 1) <> "\" Then ZipDestPath = ZipDestPath & "\" End If strMsg = "Could not create the file path for import" Set fso = CreateObject("Scripting.FileSystemObject") If strPath <> "" Then Set fldr = fso.GetFolder(strPath) For Each file In fldr.Files strMsg = "Could not get file info for " & file.Name strFile = file.Name If InStr(1, strFile, strFileTest) > 0 Then strFileFix = strPath & strFile strMsg = "Could not update the link sheet" Workbooks.Open strFileFix, False Set wb = Workbooks(strFile) If Right(wb.Name, 3) = "xls" Then ''copy data into older files ''xlsm sheet will not fit in xls files On Error Resume Next Set wsL = wb.Sheets(strLinks) On Error GoTo errHandler If wsL Is Nothing Then Set wsL = wb.Sheets.Add _ (After:=wb.Sheets(wb.Sheets.Count)) wsL.Name = strLinks End If wbLinks.Sheets(strLinks).Range(strCopy).Copy _ Destination:=wb.Sheets(strLinks).Range("A1") wb.Sheets(strLinks).Columns(strCols) _ .EntireColumn.AutoFit Else On Error Resume Next wb.Sheets(strLinks).Delete On Error GoTo errHandler wbLinks.Sheets(strLinks).Copy _ After:=wb.Sheets(wb.Sheets.Count) End If wb.Activate 'delete any names created For Each nm In wb.Names strRefersTo = nm.RefersTo If InStr(1, strRefersTo, _ strWbName) > 0 Then nm.Delete End If Next nm wb.Sheets(1).Activate wb.Close SaveChanges:=True If bZip = True Then ZipDest = Chr(34) & ZipDestPath & _ Left(strFile, InStr(1, strFile, strFileTest)) _ & "zip" & Chr(34) strFileFix = Chr(34) & strFileFix & Chr(34) 'Note spaces are important ZipFile = Shell(ZipPath & "7zG.exe a -tzip " _ & ZipDest & " " & strFileFix, vbNormalFocus) End If End If Next file End If End If Next rngID strMsgEnd = "Files have been updated" If bZip = True Then strMsgEnd = strMsgEnd & vbCrLf _ & "Zipped files have been created" End If MsgBox strMsgEnd exitHandler: Application.ScreenUpdating = True Application.DisplayAlerts = True Exit Sub errHandler: MsgBox "Could not update the files" Resume exitHandler End Sub
First, the code shows a message that asks if you want to update the files. If you do not click Yes, the code stops running.
lUpdate = MsgBox("Update all files?", _ vbYesNo + vbDefaultButton2, "Update All?") If lUpdate <> vbYes Then GoTo exitHandler
Then, it gets the specifications that you entered on the Setup sheet, and checks if Zip files should be created.
Select Case wsFL.Range("rngZip").Valu
Next, it check the ID number entered on the Setup sheet. If that cell is blank, it will run the macro for every folder in the list..
For Each rngID In rngFLID If lFldr = 0 Then bRun = rngID > 0 Else bRun = rngID = lFldr End If If bRun = True Then
For each folder, it opens each file that has "xls" in its name, and the UpdateLinks setting is turned off (false).
Workbooks.Open strFileFix, False
The master sheet is updated or replaced, if names were added, they are deleted, and the file is closed, with the changes saved.
If the Zip setting is "Yes", double quotes are added around the file names, to prevent problems if there are spaces in the names or paths.
If bZip = True Then ZipDest = Chr(34) & ZipDestPath & _ Left(strFile, InStr(1, strFile, strFileTest)) _ & "zip" & Chr(34) strFileFix = Chr(34) & strFileFix & Chr(34
Finally, a zipped file is created. This is the Shell command used when working with the 7-zip program -- other zip programs will be slightly different.
ZipFile = Shell(ZipPath & "7zG.exe a -tzip " _ & ZipDest & " " & strFileFix, vbNormalFocus) End If
To download the sample file, click here: Update and Zip an Excel Workbook.
The file is in xlsm format, and contains macros. Enable macros when you open the file, if you want to test the code.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Search Contextures Sites
Last updated: June 21, 2017 4:34 PM
Contextures RSS Feed