How to import data from different excel files and

Question

I have different excel files which have multiple sheets that I would like to import into one main excel file. These files are under different directories. And so I'm wondering if it's feasible to import specific cells from all these files into one main excel file which should be updated at every change.

Answer 1

You can create a macro like this:

Sub ConsolidateWorkbooks()
    Dim myRange As String
    myRange = "A1:B2" 'Put the range you want to copy from each workbook here
    Dim thisWorkbook As String
    Dim thisWorksheet As String
    thisWorkbook = ActiveWorkbook.name
    thisWorksheet = ActiveSheet.name

    CopyPasteOneWorkbook ("C:\firstworkbook.xls")
    CopyPasteOneWorkbook ("C:\secondworkbook.xls")


End Sub

Sub CopyPasteOneWorkbook(name As String)
    Workbooks.Open Filename:=name
    range(myRange).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(thisWorkbook).Activate
    'Select the first empty cell in column A:
    Worksheets(thisWorksheet).range("A1").End(xlDown).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
End Sub

If your workbooks are all in one file and have sequential names (workbook1.xls, workbook2.xls, etc.), then you can create a For loop. Otherwise, just put as many calls to CopyPasteOneWorkbook as you need.

Answer 2

use the following:

=('NAME_OF__SHEET'!A1)

NAME_OF__SHEET = "the name of your sheet" A1 = column, row

and your done!

Answer 3

http://www.rondebruin.nl/win/addins/rdbmerge.htm

This is a great macro/add-in to import all files in folder and if you want sub-folder into one spreadsheet.

Answer 4

Regarding the second part of this question (including data from cells in other documents to your document), the answer is here:

https://superuser.com/questions/835940/import-one-cell-of-data-from-one-spreadsheet-to-another