MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How can I import/copy data from two differet workbooks (XL)?

  Asked By: Joel    Date: Mar 10    Category: MS Office    Views: 497

How can I import/copy data to/from two diffferet workbooks (XL)?

How can I import / copy data to / from different workbooks. Actually
the case is I have to collect some data from different companies. I
have made workbook and locked the sheet leaving the cells unlocked
where I need the data.

There are fields like "Name", "Comapny
Name", "Address", "Tel", "Fax", "Description", "Price" and so on....
on different ranges and they are not tabular these are kind of
official form......

Then I emailed the same workbook to many of our business partners
and now I have received the same worksheet through email from
different peoples and these are in hundreds and I want to extract
the data from all workbooks (one by one) in to one MASTER worksheet
which may have the columns of "Name", "Company
Name", "Address", "Tel", "Fax", "Description", "Price" and so on....



2 Answers Found

Answer #1    Answered By: Jana Franklin     Answered On: Mar 10

I take it that that since you protected the sheet, you know what cells  in each
workbook contain the information you want. (and also know the name  for sheet).
Now, when you get the workbooks  back, are you putting them in a single folder?
or multiple folders?
is there other files there?
I guess here is the point.
If the files are in a specific folder (and/or sub folders)
you can write a macro in your "MASTER" workbook
to open each of the files and copy  the data.
Here is an example.
It makes use of a couple of techniques that will keep the screen from
as it opens and closes each of the data  files.
I executed it with a folder of 1,132 files, and it ran in about 90 seconds.
But, the files were very small (1-2 sheets).
Option Explicit
Sub Get_Data()
Dim fs, I, MasterFile, MasterSheet, DataFile, DataSheet, DataRow
MasterFile = ActiveWorkbook.Name
MasterSheet = "Master"
DataSheet = "Data" ' Name of sheet  in data documents
DataRow = 1
Application.ScreenUpdating = False
Set fs = Application.FileSearch
With fs
.LookIn = "C:\Documents and Settings\kzkhn4\My Documents\data\xtr\pmtr"
.SearchSubFolders = True
.Filename = "*.xls"
If .Execute() > 0 Then
For I = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(I)
DataFile = ActiveWorkbook.Name
(Workbooks(DataFile).Sheets(DataSheet).Range("K4").Value <> "") Then
DataRow = DataRow + 1

Workbooks(MasterFile).Sheets(MasterSheet).Cells(DataRow, 1) = _


Workbooks(MasterFile).Sheets(MasterSheet).Cells(DataRow, 2) = _


Workbooks(MasterFile).Sheets(MasterSheet).Cells(DataRow, 3) = _


Workbooks(MasterFile).Sheets(MasterSheet).Cells(DataRow, 4) = _

End If
End If
Next sht
Workbooks(DataFile).Close SaveChanges:=False
Next I
MsgBox "There were no files found."
End If
End With
Application.ScreenUpdating = True
End Sub

Answer #2    Answered By: Clay Cook     Answered On: Mar 10

I really appreciate what you have done, really appreciated!!

I tried that code with the required changes but it seems that there is
something I couldn't get grasp on so I have enclosed both sheets here with this
email (1) Form (2) Master and would appreciate again if you please help to fix
the code specifically.

I am happy to pick up the particular file individually to collect  the data
rather than checking all files in the folder.