PDA

View Full Version : Solved: Copy worksheer to all workbooks in specified folder



Nosstech
01-11-2009, 08:48 PM
:banghead: Hi All,

I need some help.

Another person places files in a folder (up to 100) each week. I need to copy a worksheet from a workbook into multiple workbooks in the folder previously mentioned.

I have not started writing the code because I do not know how to systematically open files in a wb one at a time and make a change.

Thanks for the help

BTW - The file names change each week with no consistency

GTO
01-12-2009, 12:06 AM
:banghead: Hi All,

I need some help.

...I have not started writing the code because I do not know how to systematically open files in a wb one at a time and make a change. ..

Greetings Nosstech,


While I am sure this will just be a start, here's a simple example. As noted in the comments, this example needs the source (your workbook with the sheet you want to copy to the others) workbook to be saved in the same folder as the destination workbooks.

I would suggest that you create a temporary folder, create several workbooks in it, and save the attached wb to the same folder. Hopefully this will give you a general idea of what you're after?

Hope this helps,

Mark

Option Explicit

Sub CopyOneSheetToAllWBsInFolder()
'// For this to work, this workbook needs to be placed in the same folder as the //
'// other workbooks that you are copying a sheet to. //
'// In this example, I used late binding. To use early binding, under //
'// Tools|References... select "Microsoft Scripting Runtime" (scrrun.dll). //
'// This allows the variable fso to be defined as FileSystemObject, fol to be //
'// defined as Folder, etc. More importantly, for learning what methods, properties//
'// and so on are avaiable, early-binding allows for intellisense - that is, for //
'// instance, when you get down to the line that has "If fil.Type (etc)" when you //
'// type "fil." the little list of available stuff pops up. So... if you are //
'// not overly familiar with FileSystemObject for instance, this seems to me to //
'// make it easier to learn. //
Dim fso As Object ' FileSystemObject
Dim fol As Object ' Folder
Dim fil As Object ' File
Dim wb As Workbook
Dim sh As Worksheet
Dim bolShExists As Boolean
'// We'll define the name of the sheet you want to copy here. //
Const SH_NAME As String = "Sheet To Copy"

'// First, we'll set a reference to the FileSystemObject as an object, so we can//
'// access the fso Folder object. //
Set fso = CreateObject("Scripting.FileSystemObject")

'// Now we'll set/create a reference to the folder object we want, namely the //
'// folder that contains all the workbooks you want to copy a sheet to. //
'// In this example, this workbook (your workbook with the sheet to be copied in//
'// it, or more generally referred to as the source workbook) must be in the //
'// same folder as the other workbooks, as we are using this workbook's path to //
'// tell GetFolder which folder to return as our object. //
Set fol = fso.GetFolder(ThisWorkbook.Path)

'// Now since 'fol' equals the folder we want, and .Files returns all the files //
'// in fol, we can loop through all the files in the folder... //

For Each fil In fol.Files

'// Just one way of doing it, but as a safety, we can test before opening a //
'// file, just in case there's maybe a file in there we don't want. So, //
'// IF the file being looked at during this loop is both (the And) an //
'// Excel workbook AND it's NOT this workbook, then we'll open it. //
If fil.Type = "Microsoft Excel Worksheet" _
And Not fil.Name = ThisWorkbook.Name Then

'// Now we'll set wb to the workbook we are opening. //
Set wb = Workbooks.Open(Filename:=fil.Path, _
ReadOnly:=False, _
AddToMru:=False)

'// For the second thru (however many workbooks there are) loops, we //
'// want to set our flag (Boolean) to false before the little loop //
'// we're about to get in. //
bolShExists = False

'// Another 'safety'. We'll look at ea worksheet in the open workbook //
'// to make sure we didn't already copy our sheet to it. //
For Each sh In wb.Worksheets

If sh.Name = SH_NAME Then
'// If by chance we do find an identically named sheet, we can //
'// prevent an error... //
bolShExists = True
Exit For
End If
Next

'// ...by not attempting to copy. If no identical sheet was found, //
'// bolShExists will still be false, so we'll copy, save, and close //
'// the wb. //
If Not bolShExists Then
ThisWorkbook.Worksheets(SH_NAME).Copy _
Before:=Workbooks(wb.Name).Sheets(1)

wb.Save
wb.Close False
Else

'// Warn us if we did find an identical sheet. //
MsgBox "The sheet named: """ & SH_NAME & """ already exists in the" & _
vbCrLf & "workbook named: """ & wb.Name & ".""" & String(2, vbCrLf) & _
wb.Name & " will be closed without any changes.", vbInformation, ""

wb.Close False
End If
End If
Next

End Sub


...Another person places files in a folder (up to 100) each week. I need to copy a worksheet from a workbook into multiple workbooks in the folder previously mentioned...

...BTW - The file names change each week with no consistency...

Oh, I almost forgot. Given those two parameters, I am sure hoping that only workbooks that you want to copy to are in the aformentioned folder. Elsewise - yeeks! (might be just a tad bit harder)

Nosstech
05-06-2009, 06:29 AM
Thanks, This worked!!!