Creating worksheet if not found.

[kaffal]

Hi all, I have ten .xls file in one folder, C:\data\.
In the files, there are several worksheets inside.

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Directory</td><td>C:\Data\</td></tr></tbody></table>
I have one Master.xls in the other folder, C:\Summary\
I would like to have a macro, to check with all the files in the folder,B2, comparing to the Master.xls

If Master.xls does not have the exact worksheet name with files in the folder. It will create one new worksheet with the same name in the Master.xls.
The macro will end when all the files are opened and compared with the master.xls.
The output, Master.xls will have all the worksheets which can be found in all the files in the directory.

 

[Zack Barresse]

Hi there,

Not sure if this is what you're looking for or not, but this works for me in testing...




<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> TransferToMasterFile()<br>    <SPAN style="color:#00007F">Dim</SPAN> FSO <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, fsoFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, fsoFolder <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wbM <SPAN style="color:#00007F">As</SPAN> Workbook, wb <SPAN style="color:#00007F">As</SPAN> Workbook, wsM <SPAN style="color:#00007F">As</SPAN> Worksheet, ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> blnFileOpen <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wbM = ThisWorkbook<br>    <SPAN style="color:#00007F">Set</SPAN> wsM = wbM.Worksheets("Sheet1") <SPAN style="color:#007F00">'master worksheet, change as needed</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> FSO = CreateObject("Scripting.FileSystemObject")<br>    <SPAN style="color:#00007F">Set</SPAN> fsoFolder = FSO.GetFolder(wsM.Range("B1").Value)<br>    <SPAN style="color:#00007F">Call</SPAN> TOGGLEEVENTS(False)<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> fsoFile <SPAN style="color:#00007F">In</SPAN> fsoFolder.Files<br>        <SPAN style="color:#00007F">If</SPAN> ISWBOPEN(fsoFile.Name) = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            blnFileOpen = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks(fsoFile.Name)<br>        <SPAN style="color:#00007F">Else</SPAN><br>            blnFileOpen = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Open(fsoFile.Path)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> wb.Worksheets<br>            <SPAN style="color:#00007F">If</SPAN> SHEETEXISTS(ws.Name, wbM) = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                ws.Copy After:=wbM.Worksheets(wbM.Worksheets.Count)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>        <SPAN style="color:#00007F">If</SPAN> blnFileOpen = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN> wb.Close SaveChanges:=<SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> fsoFile<br>    <SPAN style="color:#00007F">Call</SPAN> TOGGLEEVENTS(True)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> ISWBOPEN(wbName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#007F00">'Originally found by Jake Marx</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    ISWBOPEN = Len(Workbooks(wbName).Name)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> SHEETEXISTS(wsName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> wkb <SPAN style="color:#00007F">As</SPAN> Workbook) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> wkb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wkb = ActiveWorkbook<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    SHEETEXISTS = Len(wkb.Sheets(wsName).Name)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> TOGGLEEVENTS(<SPAN style="color:#00007F">ByVal</SPAN> blnState <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><SPAN style="color:#007F00">'Originally written by Zack Barresse</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .DisplayAlerts = blnState<br>        .EnableEvents = blnState<br>        .ScreenUpdating = blnState<br>        <SPAN style="color:#00007F">If</SPAN> blnState <SPAN style="color:#00007F">Then</SPAN> .CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> blnState <SPAN style="color:#00007F">Then</SPAN> .StatusBar = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>





HTH