Open each file in a folder and closing it

  • Thread starter Thread starter Ayo
  • Start date Start date
A

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

Dave Peterson said:
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.)
Click to expand...
 
Back
Top