Is there a way to copy data from multiple workbooks in a folder without opening those workbooks and paste into a master workbook?

[Reetesh]

Hello, I'm quite new to VBA and I'm not sure whether the question which i'm going to ask can be done without using VBA or not.
I'm trying to figure out a way wherein i'll be able to copy data from multiple workbooks in a folder, without actually opening those workbooks, and paste the data into master workbook.
I know how to do it by opening all the workbooks and copying the data from them and pasting it into master workbook automatically using VBA.
However, i was thinking whether that can be done without actually opening all those workbooks in the folder.

I know its a weird one, but was not sure where to actually ask this or rather confirm this, whether it can actually be done or not, so just asked this question here.

If this can be done please let me know how. It doesn't matter whether it can be done using VBA or without it.
Thanks in Advance.

 

[sandy666]

you can use Power Query (Get&Transform) with From Folder feature [XL2016]

 

[Reetesh]

Thanks for the reply mate.
Just wanted to ask, can that be used in excel 2003?
Because I'm trying to do this for work purposes and the excel version which we use at work is 2003.

 

[sandy666]

Power Query is supported by
XL2010/2013 with PQ add-in
XL2016 and above with PQ built-in

but you can try with MS Query for XL2007 and less (for higher versions also)

 

[mehidy1437]

try below,
go through it & do the required changes

Sub simpleXlsMerger()
Application.ScreenUpdating = False

Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
 
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("E:\SHARE NEW\SS21\color combination\")

Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
 
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point

Range("A1:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate 'first worksheet of each book, change the number for other sheet
 
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next

Application.ScreenUpdating = True

End Sub

Another code,

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    
    ' Set summarysheet to activeworkbook/activesheet where the macro runs
    Set SummarySheet = ActiveWorkbook.ActiveSheet
    
    ' Modify this folder path to point to the files you want to use.
    FolderPath = "E:\SHARE NEW\SS21\tech\"
    
       'check the folder exists
   If Not FileFolderExists(FolderPath) Then
      MsgBox "Specified folder does not exist, exiting!"
      Exit Sub
   End If
  
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
    
    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")
    
    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)

        'loop through all Sheets in WorkBk
        For Each sh In WorkBk.Worksheets
          
        ' Set the source range to be A9 through C9.
          Set SourceRange = Sheets(sh.Name).Range("A1:p80")
        
        ' Set the destination range to start at column B and
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("A" & SummarySheet.Range("A" & Rows.Count).End(xlUp).Row + 1)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)
        
        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value
        
        Next sh
        
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
        
        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop
    
errHandler:
   On Error Resume Next
   Application.ScreenUpdating = True
    
    ' Call AutoFit on the destination sheet so that all
    ' data is readable.
    ActiveSheet.Columns.AutoFit
        
End Sub

Private Function FileFolderExists(strPath As String) As Boolean
    If Not Dir(strPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function

Sub DeleteAllEmptyRows()
    Dim LastRowIndex As Integer
    Dim RowIndex As Integer
    Dim UsedRng As Range
 
    Set UsedRng = ActiveSheet.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    Application.ScreenUpdating = False
 
    For RowIndex = LastRowIndex To 1 Step -1
        If Application.CountA(Rows(RowIndex)) = 0 Then
            Rows(RowIndex).Delete
        End If
    Next RowIndex
 
    Application.ScreenUpdating = True
End Sub

 

[Reetesh]

Hello mehidy1437

Thanks a lot again mate for helping me.

I tried both the codes, however both codes open the workbooks present in the folder and then copies data from it.

The second code, does not close the workbook (present in the folder) after copying and pasting the data into the master file.

I want to copy the data present in the last row only from one worksheet present in all the workbooks and paste it into master file, just below the headers in the master file, after inserting another row, so that the latest data will display at the top (Its the same thing that I want to do, which you helped me with earlier) . The name of that worksheet is "Activity Data" and it will be same in all the workbooks and the range is from column B to column I (B1:I1 contains headers) which i want to copy from all the workbooks and paste it into master file.

I'm not sure whether what I'm asking is possible or not.
But again thanks in advance for you help mate .

 

[mehidy1437]

hi, you can follow the below thread for the solutions. If you can't figure it out let us know here, will try to find the solution.
Sorry mate, right now I'm a bit buys.

Copy data only from last used row from multiple excel workbooks in a folder and paste into master file