Open each file in a folder and closing it
Thanks
"Dave Peterson" wrote:
fl includes the drive and path.
And when you use workbooks(...), you don't include that drive and path--you just
use the workbook's name.
So you could parse the string and strip out the filename or you could use a
variable that represents that workbook:
Option Explicit
Sub aa()
Call RFDS_Folder(RFDSFolder:="C:\my documents\excel\test")
End Sub
Sub RFDS_Folder(RFDSFolder As String)
Dim fs, f, fl, fc, fn
Dim TempWkbk As Workbook
Dim sh As Worksheet
Dim RFDSWkbk As Workbook
Set RFDSWkbk = Workbooks("RFDS Tracker GA Market_Form 4C.xls")
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(RFDSFolder)
Set fc = f.Files
'For Each fl In RFDSFolder
For Each fl In fc
Set TempWkbk = Workbooks.Open(Filename:=fl, ReadOnly:=True)
TempWkbk.Worksheets("RFDS").Copy _
After:=RFDSWkbk.Sheets("RFDS Tracker (2)")
TempWkbk.Close SaveChanges:=False
For Each sh In RFDSWkbk.Worksheets
If sh.Name = "RFDS" Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
Next fl
End Sub
(Untested, but it did compile.)
Ayo wrote:
I am having a little problem with the following code and I need some fresh
eyes to guide me in the right direction. What I am trying to do is open each
file in the RFDSFolder (RFDSFolder is a string representing a Folder path),
get a copy of a tab and place it in the current workbook. Then I want to
close the file and open the next file in the folder.
The problem is that I am getting a "Type mismatch" error on
"Workbooks(fl).Close SaveChanges:=False". I am having a problem using the
GetFilename method.
Any ideas?
Sub RFDS_Folder(RFDSFolder As String)
Dim fs, f, fl, fc, fn
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(RFDSFolder)
Set fc = f.Files
'For Each fl In RFDSFolder
For Each fl In fc
Workbooks.Open fl, ReadOnly:=True
Worksheets("RFDS").Copy After:=Workbooks("RFDS Tracker GA
Market_Form 4C.xls").Sheets("RFDS Tracker (2)")
Workbooks(fl).Close SaveChanges:=False
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In Worksheets
If sh.Name = "RFDS" Then
sh.Select
ActiveWindow.SelectedSheets.Delete
End If
Next sh
Workbooks("RFDS Tracker GA Market_Form 4C.xls").Worksheets("RFDS
Tracker").Range("A4").Select
Next fl
End Sub
--
Dave Peterson
|