• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3457
  • Last Modified:

Combining excel files and extracting data using macros

How do I combine all Excel files in a folder into a single Excel sheet?
2 Solutions
I know a way to combine txt or cvv files to excel files but not excel to 1 single excel.I dont think it is possible also.
If you want i can share any of them.
This could be done via VBA or VBScript. To answer your question we would need to know more details about what you actually want to do...

Do you truly want all of the information placed into a single worksheet (not workbook)?
How many Excel documents are there, and what is the average number of rows?
Are there multiple sheets in each workbook? Do you want the solution to import all of the sheets?

See the code snippet for a general idea of how this could be done (untested)...
Dim wbDest as workbook
Dim wsDest as worksheet
Dim wbSource as workbook
Dim wsSource as worksheet
Dim FSO as object
Dim Fol as object
Dim fi as object
'Get a reference to the destination workbook.
set wbDest = ThisWorkbook
set wsDest = wbDest.worksheets(1)
'Create a new FileSystemObject. We'll use this to loop through
'the files.
set fso = createobject("scripting.filesystemobject")
set fol = fso.getfolder("c:\Source Documents")
'Loop through the files.
for each fi in fol.files
  'Open the workbook and get the first worksheet.
  set wbSource = excel.workbooks.open(fi.path)
  set wsSource = wbsource.worksheets(1)
  'Here you would add code to copy the data from one workbook to the other. There are many options on how to do this. The method depends on the answers to the questions above.
  'Close the workbook.

Open in new window

Forced accept.

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now