Open Excel, Save and Close

Posted on 2013-12-09
Last Modified: 2013-12-09
I have a folder in my documents called 2012 (the path is C:\MyDocuments\2012). Inside the 2012 folder are four sub foldders (Folder1,Folder2,Folder3, and Folder 4). Each of the subfolders cantain many Excel woorkbooks.

I need each Excel workbook within each subfolder opended, saved and closed. This will allow built in formulas to update. Do you have code to open, close and save each workbook in Folder1, Folder2, Folder3, and Folder4?
Question by:Conernesto
LVL 35

Accepted Solution

mvidas earned 500 total points
ID: 39706326

Give the following a try. Note that in the ConernestoDirectory subroutine, there are two methods for you to use to retrieve the data. In the first one, it will look through all excel files in \MyDocuments\2012\ and all subdirectories. In the second method, it will only look in those 4 specified subdirectories.
Sub ConernestoDirectory()
 Dim vFiles() As String, i As Long
 ReDim vFiles(1, 0)
'if you want all excel files in \2012 and all subdirectories, use this format
 Call GetFilesWithDir("C:\MyDocuments\2012\", vFiles)
'if you only want excel files in the 4 specified subdirectories, use this format
 Call GetFilesWithDir("C:\MyDocuments\2012\Folder1\", vFiles, False)
 Call GetFilesWithDir("C:\MyDocuments\2012\Folder2\", vFiles, False)
 Call GetFilesWithDir("C:\MyDocuments\2012\Folder3\", vFiles, False)
 Call GetFilesWithDir("C:\MyDocuments\2012\Folder4\", vFiles, False)

'loop through files, look for excel files, and open/save/close any found
 For i = 0 To UBound(vFiles, 2)
  If LCase(vFiles(1, i)) Like "*.xls" Or LCase(vFiles(1, i)) Like "*.xls?" Then
   With Workbooks.Open(vFiles(0, i) & vFiles(1, i))
   End With
  End If
End Sub
Function GetFilesWithDir(ByVal vPath As String, ByRef vsArray() _
  As String, Optional IncludeSubfolders As Boolean = True) As Boolean
 'You must send this a string array, ReDim'med to (1,0)
 ' (0,x) = path of file
 ' (1,x) = file name
 Dim TempStr As String, vDirs() As String, Cnt As Long, dirCnt As Long
 dirCnt = 0
 If Len(vsArray(0, 0)) = 0 Then
  Cnt = 0
  Cnt = UBound(vsArray, 2) + 1
 End If
 If Right(vPath, 1) <> "\" Then vPath = vPath & "\"
 If IncludeSubfolders Then
  On Error GoTo BadDir
  TempStr = Dir(vPath, 31)
  Do Until Len(TempStr) = 0
   If Asc(TempStr) <> 46 Then
    If GetAttr(vPath & TempStr) And vbDirectory Then
     ReDim Preserve vDirs(dirCnt)
     vDirs(dirCnt) = TempStr
     dirCnt = dirCnt + 1
    End If
   End If
   TempStr = Dir
 End If
 On Error GoTo BadFile
 TempStr = Dir(vPath, 15)
 Do Until Len(TempStr) = 0
  ReDim Preserve vsArray(1, Cnt)
  vsArray(0, Cnt) = vPath
  vsArray(1, Cnt) = TempStr
  Cnt = Cnt + 1
  TempStr = Dir
 On Error GoTo 0
 If dirCnt > 0 Then
  For dirCnt = 0 To UBound(vDirs)
   If Len(Dir(vPath & vDirs(dirCnt))) = 0 Then
    GetFilesWithDir vPath & vDirs(dirCnt), vsArray
   End If
 End If
 Exit Function
 If TempStr = "pagefile.sys" Or TempStr = "???" Then
  Debug.Print "DIR: Skipping: " & vPath & TempStr
  Resume BadDirGo
 ElseIf Err.Number = 52 Then
  Debug.Print "No read dir rights: " & vPath & TempStr
  Resume SkipDir
 End If
 Debug.Print "Error with DIR Dir: " & Err.Number & " - " & Err.Description
 Exit Function
 If Err.Number = 52 Then
  Debug.Print "No read file rights: " & vPath & TempStr
  Debug.Print "Error with DIR File: " & Err.Number & " - " & Err.Description
 End If
 Resume BadFileGo
End Function

Open in new window


Author Closing Comment

ID: 39706985
Great code. Thank you.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question