I fould this nice code below which imports all the files of a folder into another main workbook, and labels each tab as the file name. I’d like to change a few things and could use help.
First, I'd like to have more control of the file path & folders - I'd like an 'open file' window displayed to allow selecting any number of files in the folder. The initial file path is to be set to C:\Files\ so that I can manually select the folders & files from there on.
Secondly, the code imports the files before the existing sheets in the main workbook - I'd like the code to delete all the sheets before importing (creating new sheets for) the files? And I'd like to have the file names (sheet tabs) in alphabetical order in the final workbook.
Also, I’m wondering if there should be some error-trapping code here - any suggestions?
Thanks a lot for your help.
First, I'd like to have more control of the file path & folders - I'd like an 'open file' window displayed to allow selecting any number of files in the folder. The initial file path is to be set to C:\Files\ so that I can manually select the folders & files from there on.
Secondly, the code imports the files before the existing sheets in the main workbook - I'd like the code to delete all the sheets before importing (creating new sheets for) the files? And I'd like to have the file names (sheet tabs) in alphabetical order in the final workbook.
Also, I’m wondering if there should be some error-trapping code here - any suggestions?
Thanks a lot for your help.
Code:
Sub CopyFiles()
Dim objFso As Object
Dim objFolder As Object
Dim objFile As Object
Dim objFiles As Object
Dim strFolder As String
Dim strWorkbook As String
'message box - confirm macro run
Dim nResult As Long
nResult = MsgBox( _
Prompt:="Import files?", _
Buttons:=vbYesNo)
If nResult = vbYes Then
Application.ScreenUpdating = False
strWorkbook = "Main.xls" 'Main workbook
'Create objects
Set objFso = CreateObject("Scripting.FileSystemObject")
strFolder = "C:\Files" 'Folder directory
Set objFolder = objFso.getfolder(strFolder)
Set objFiles = objFolder.Files
'Loop through files
For Each objFile In objFiles
Workbooks.Open strFolder & "\" & objFile.Name
'Copy sheet
ActiveWorkbook.Sheets(1).Copy Before:=Workbooks(strWorkbook).Sheets(1) 'Copy sheet
Windows(objFile.Name).Activate
ActiveWorkbook.Close
Next objFile
'Clear memory
Set objFile = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFso = Nothing
End If
Application.ScreenUpdating = True
MsgBox "Files imported successfully!"
End Sub