Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
all worksheets into one workbook
Below is code from the VBA help menu.
Sub SearchEveryFolder() 'Declare variables that reference a 'SearchScope and a ScopeFolder object. Dim ss As SearchScope Dim sf As ScopeFolder 'Declare a variable to act as a generic counter. Dim lngCount As Long 'Use a With...End With block to reference the 'FileSearch object. With Application.FileSearch 'Clear all the parameters of the previous searches. 'This method doesn't clear the LookIn property or 'the SearchFolders collection. .NewSearch 'Specify the type of file for which to search. 'Use the FileType property to specify the first type 'and then add additional types to the FileTypes collection. .FileType = msoFileTypeWebPages .FileTypes.Add msoFileTypeExcelWorkbooks 'Clear the SearchFolder collection by 'looping through each ScopeFolder object 'and removing it. For lngCount = 1 To .SearchFolders.Count .SearchFolders.Remove lngCount Next lngCount 'Loop through the SearchScopes collection to find 'the scope in which you want to search. In this 'case the scope is the local machine. For Each ss In .SearchScopes Select Case ss.Type Case msoSearchInMyComputer 'Loop through each ScopeFolder in 'the ScopeFolders collection of the 'SearchScope object. For Each sf In ss.ScopeFolder.ScopeFolders 'Call a function that loops through all 'of the subfolders of the root ScopeFolder. 'This function adds any folders named "1033" to the 'SearchFolders collection. Call OutputPaths(sf.ScopeFolders, "1033") Next sf Case Else End Select Next ss 'Test to see if any ScopeFolders collections were added to 'the SearchFolders collection. If .SearchFolders.Count 0 Then 'Set the LookIn property to the path of 'the first ScopeFolder object in the SearchFolders 'collection. This is here so that any previous 'setting of the LookIn property doesn't affect 'the search. .LookIn = .SearchFolders.Item(1).Path 'Execute the search and test to see if any files 'were found. If .Execute < 0 Then 'Display the number of files found. MsgBox "Files found: " & .FoundFiles.Count 'Loop through the list of found files and 'display the path of each one in a message box. For lngCount = 1 To .FoundFiles.Count If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, _ "Found files") = vbCancel Then 'Break out of the loop lngCount = .FoundFiles.Count End If Next lngCount End If End If End With End Sub 'This subroutine loops through all of the ScopeFolders collections 'in a given ScopeFolders collection. It adds any folder 'that has the same name as the value of strFolder 'to the SearchFolders collection. Sub OutputPaths(ByVal sfs As ScopeFolders, _ ByRef strFolder As String) 'Declare a variable as a ScopeFolder object Dim sf As ScopeFolder 'Loop through each ScopeFolder object in the 'ScopeFolders collection. For Each sf In sfs 'Test to see if the folder name of the ScopeFolder 'matches the value of strFolder. Use LCase to ensure 'that case does not affect the match. If LCase(sf.Name) = LCase(strFolder) Then 'Add the ScopeFolder to the SearchFolders collection. sf.AddToSearchFolders End If 'Include a DoEvents call because there is the potential for this 'loop to last a long time. The DoEvents call allows this process to 'continue handling events. DoEvents 'Test to see if the ScopeFolders collection in the 'current ScopeFolder is empty. If it isn't empty, then 'that means that the current ScopeFolder object contains subfolders. If sf.ScopeFolders.Count 0 Then 'This subroutine recursively calls itself so that 'it can add the subfolders of the current ScopeFolder object 'to the SearchFolders collection. Call OutputPaths(sf.ScopeFolders, strFolder) End If Next sf End Sub "excel-tr" wrote: Hi, I have lots of workbooks having worksheets in different folders.How can I save all worksheets in different workbooks into one new workbook ? regards. -- SAHRAYICEDIT-ISTANBUL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine worksheets in multiple workbook in one workbook with a macro | Excel Discussion (Misc queries) | |||
Create New Workbook from Worksheets in One Workbook | Excel Discussion (Misc queries) | |||
copying worksheets to a new workbook without formulae referencing original workbook | Excel Programming | |||
Copy four worksheets from one workbook into a new workbook.e-mail | Excel Programming | |||
Link multiple worksheets in one workbook to another workbook and . | Excel Programming |