Open each file in a folder and closing it

[Ayo]

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]

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.)

 

[Dave Peterson]

ps. Instead of looping through the worksheets collection, you could just try to
delete that sheet and ignore any error:

'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

On Error Resume Next
Application.DisplayAlerts = False
RFDSWkbk.Worksheets("RFDS").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Next fl

 

[Ayo]

Thanks

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.)