troubleshooting Question

Linking multiple excel workbooks and gathering data in one single sheet

Avatar of Freek Doornkamp
Freek Doornkamp asked on
Microsoft OfficeMicrosoft ExcelVB ScriptVBA
5 Comments1 Solution144 ViewsLast Modified:

I am new to working with VBA and I'm probably trying to do  something above my level of knowledge.
I want to link multiple Excel Workbooks from one folder and gather specific cells (data) in one single sheet.

I got the following code working, but it only gives the output of all files in one workbook, with the value of cells A4: C4.


Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range

' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

' Modify this folder path to point to the files you want to use.
FolderPath = "C:\Users\603964\Desktop\Test"

' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1

' 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)

' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName

' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
Set SourceRange = WorkBk.Worksheets(1).Range("A4:C4")

' Set the destination range to start at column B and
' be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _

' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value

' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count

' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False

' Use Dir to get the next file name.
FileName = Dir()

' Call AutoFit on the destination sheet so that all
' data is readable.
End Sub

now I want to select multiple cells from the files that are in the folder, but here I get stuck.
unfortunately I can not manage to select multiple cells and get them in a format.

The output needs te be something like this:

      A                 B      C     D      E      F      G     H     I       J
1 Filename 1      A9      B9      C9      A10      B10      C10      A42      B42      C42
2 Filename 2      A9      B9      C9      A10      B10      C10      A42      B42      C42
3 Filename 3      A9      B9      C9      A10      B10      C10      A42      B42      C42
4 Filename 4      A9      B9      C9      A10      B10      C10      A42      B42      C42
5 Filename 5      A9      B9      C9      A10      B10      C10      A42      B42      C42
6 Filename 6      A9      B9      C9      A10      B10      C10      A42      B42      C42
7 Filename 7      A9      B9      C9      A10      B10      C10      A42      B42      C42
8 Filename 8      A9      B9      C9      A10      B10      C10      A42      B42      C42

Could someone please help me with my challenge?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros