I'm working on a loop that will open all workbooks in a folder and add two
specified tabs to each workbook. Important factors include my ability to
choose the name of the two tabs and for the loop to carry on through the
entire folder servicing all workbooks. Any suggestions

Adapted from a post by Bob Phillips. Adds two sheets called AddOne and AddTwo
if they do not allready exist in the files:

Sub OpenFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Temp")
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
On Error Resume Next
Set sht1 = Sheets("AddOne")
Set sht2 = Sheets("AddTwo")
On Error GoTo 0
If sht1 Is Nothing Then
Set sht1 = Sheets.Add
sht1.Name = "AddOne"
End If
If sht2 Is Nothing Then
Set sht2 = Sheets.Add
sht2.Name = "AddTwo"
End If
ActiveWorkbook.Close True
Set sht1 = Nothing
Set sht2 = Nothing
End If
Next

End Sub

Hope this helps
Rowan

"need_some_help" wrote:

I'm working on a loop that will open all workbooks in a folder and add two
specified tabs to each workbook. Important factors include my ability to
choose the name of the two tabs and for the loop to carry on through the
entire folder servicing all workbooks. Any suggestions

Rowan...your "night owl" tendencies are much appreciated!! I'll try it out
and update you later tonight (this morning)

"Rowan" wrote:

Adapted from a post by Bob Phillips. Adds two sheets called AddOne and AddTwo
if they do not allready exist in the files:

Sub OpenFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Temp")
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
On Error Resume Next
Set sht1 = Sheets("AddOne")
Set sht2 = Sheets("AddTwo")
On Error GoTo 0
If sht1 Is Nothing Then
Set sht1 = Sheets.Add
sht1.Name = "AddOne"
End If
If sht2 Is Nothing Then
Set sht2 = Sheets.Add
sht2.Name = "AddTwo"
End If
ActiveWorkbook.Close True
Set sht1 = Nothing
Set sht2 = Nothing
End If
Next

End Sub

Hope this helps
Rowan

"need_some_help" wrote:

I'm working on a loop that will open all workbooks in a folder and add two
specified tabs to each workbook. Important factors include my ability to
choose the name of the two tabs and for the loop to carry on through the
entire folder servicing all workbooks. Any suggestions

Or this afternoon if you happen to be in Sydney as I am...

"need_some_help" wrote:

Rowan...your "night owl" tendencies are much appreciated!! I'll try it out
and update you later tonight (this morning)

"Rowan" wrote:

Adapted from a post by Bob Phillips. Adds two sheets called AddOne and AddTwo
if they do not allready exist in the files:

Sub OpenFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Temp")
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
On Error Resume Next
Set sht1 = Sheets("AddOne")
Set sht2 = Sheets("AddTwo")
On Error GoTo 0
If sht1 Is Nothing Then
Set sht1 = Sheets.Add
sht1.Name = "AddOne"
End If
If sht2 Is Nothing Then
Set sht2 = Sheets.Add
sht2.Name = "AddTwo"
End If
ActiveWorkbook.Close True
Set sht1 = Nothing
Set sht2 = Nothing
End If
Next

End Sub

Hope this helps
Rowan

"need_some_help" wrote:

I'm working on a loop that will open all workbooks in a folder and add two
specified tabs to each workbook. Important factors include my ability to
choose the name of the two tabs and for the loop to carry on through the
entire folder servicing all workbooks. Any suggestions

Rowan, I engrafted this code (with a few minor customizations) into my
program as a call to Openfiles and it compiles and runs fine but does not
create the new tabs in each of the workbooks in the directory. Is there a
special way to invoke the procedure? Should I pass the directory as an
object or something? Thanks for your help

"Rowan" wrote:

Or this afternoon if you happen to be in Sydney as I am...

"need_some_help" wrote:

Rowan...your "night owl" tendencies are much appreciated!! I'll try it out
and update you later tonight (this morning)

"Rowan" wrote:

Adapted from a post by Bob Phillips. Adds two sheets called AddOne and AddTwo
if they do not allready exist in the files:

Sub OpenFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Temp")
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
On Error Resume Next
Set sht1 = Sheets("AddOne")
Set sht2 = Sheets("AddTwo")
On Error GoTo 0
If sht1 Is Nothing Then
Set sht1 = Sheets.Add
sht1.Name = "AddOne"
End If
If sht2 Is Nothing Then
Set sht2 = Sheets.Add
sht2.Name = "AddTwo"
End If
ActiveWorkbook.Close True
Set sht1 = Nothing
Set sht2 = Nothing
End If
Next

End Sub

Hope this helps
Rowan

"need_some_help" wrote:

I'm working on a loop that will open all workbooks in a folder and add two
specified tabs to each workbook. Important factors include my ability to
choose the name of the two tabs and for the loop to carry on through the
entire folder servicing all workbooks. Any suggestions