Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sheets from many files into 1
Hi everyone. I have a directory of excel files, each containing
multiple sheets (each may have different number of sheets). Can I consolidate all sheets into 1 file? Basically copying each sheet from each file and dropping each sheet into a single file. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sheets from many files into 1
Hi Steve,
The following code was an answer to another poster to this same newsgroup about 2 weeks ago with more or less the same problem. It displays an Open dialog box so you can select all of the files you want to process. It then successively opens each workbook. In the "ProcessAllFiles" routine immediately below where the comment says "'Call your macro here.", insert a call to a routine that will actually copy the data from each worksheet to your single worksheet. (I don't have code to actually copy each worksheet, as I don't know whether your data has headers in row 1, whether some rows should be ignored, etc.) Hopefully, this will give you a start. '---------------------------------------------------------------------- Public Sub ProcessAllFiles() Dim varFileList As Variant Dim lngFileCount As Long Dim ilngFileNumber As Long Dim strFileName As String 'Index for varFileList starts at 1 in this situation, 'even without Option Base 1 statement at the top of the module. varFileList = Application _ .GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls", _ Title:="Open Excel File(s)", _ MultiSelect:=True) lngFileCount = FileCount(varFileList) If lngFileCount = 0 Then GoTo ExitSub 'User canceled out of dialog box. For ilngFileNumber = 1 To lngFileCount Workbooks.Open Filename:=CurrentFileName(varFileList, ilngFileNumber) 'Call your macro here. 'Set SaveChanges according to whether your macro already saved or not. ActiveWorkbook.Close SaveChanges:=False Next ilngFileNumber ExitSub: End Sub '---------------------------------------------------------------------- Private Function FileCount(varFileList) As Long Select Case VarType(varFileList) Case vbBoolean 'User canceled out of the File Open dialog box. FileCount = 0 Case vbString 'Dialog box is in single file mode. 'Single file selected for opening only. FileCount = 1 Case vbArray + vbVariant 'Multiple files selected for processing. FileCount = UBound(varFileList) - LBound(varFileList) + 1 End Select End Function '---------------------------------------------------------------------- Private Function CurrentFileName(varFileList As Variant, _ ilngFileNumber As Long) As String Select Case VarType(varFileList) Case vbBoolean 'User canceled out of the File Open dialog box. CurrentFileName = "" Case vbString 'Dialog box is in single file mode. 'Single file selected for opening only. CurrentFileName = varFileList Case vbArray + vbVariant 'Multiple files selected for processing. 'Return the filename currently pointed to. CurrentFileName = CStr(varFileList(ilngFileNumber)) End Select End Function -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy&paste 28 individual files into one file/28 work sheets | Excel Worksheet Functions | |||
COPY SHEETS FROM TWO DIFFERENT EXCEL FILES INTO A THIRD | Excel Programming | |||
set up a macro to copy/ move sheets to individual files | Excel Programming | |||
Macro to copy sheets from several files into a new workbook. | Excel Programming | |||
Copy text files into multiple sheets of 1 workbook | Excel Programming |