Importing files into main workbook

westwind

New Member
Joined
Dec 9, 2005
Messages
48
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.

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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

westwind

New Member
Joined
Dec 9, 2005
Messages
48
The following “open file” code is what I’d like to add/implement to the above code.
Code:
Sub Test()
   Dim Filename As Variant
   ChDrive "C"
   ChDir "C:\Files"
   Filename = Application.GetOpenFilename(MultiSelect:=True)
End Sub
Can someone please help me integrate it?

Thanks
 

westwind

New Member
Joined
Dec 9, 2005
Messages
48
I tried again trying to integrate the two codes but still couldn't get it to work.

I'd appreciate ANY at all help with this.

Anyone?
 

Forum statistics

Threads
1,141,721
Messages
5,708,092
Members
421,546
Latest member
delatollas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top