Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Ayo Ayo is offline
external usenet poster
 
Posts: 489
Default Open each file in a folder and closing it

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Open each file in a folder and closing it

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Open each file in a folder and closing it

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



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


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.misc
Ayo Ayo is offline
external usenet poster
 
Posts: 489
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel program remains open after closing file RPC Excel Discussion (Misc queries) 9 February 7th 08 08:59 PM
Macro to open most current file in folder Tasha Excel Discussion (Misc queries) 6 June 19th 07 03:36 PM
closing file with multiple windows open gvanhoy3 Excel Worksheet Functions 1 November 15th 06 07:31 PM
Unable to open a file from its icon in folder John Excel Discussion (Misc queries) 4 February 14th 06 03:02 AM
How do I stop Excel from closing the open file each time I open a. Welsin Setting up and Configuration of Excel 3 January 9th 05 12:16 AM


All times are GMT +1. The time now is 04:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"