Logo 
Search:

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: 634
  

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....

Share: 

 

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
"flickering"
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
Workbooks(MasterFile).Sheets(MasterSheet).Range("A2:Z65000").ClearContents
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
If
(Workbooks(DataFile).Sheets(DataSheet).Range("K4").Value <> "") Then
DataRow = DataRow + 1

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

Workbooks(DataFile).Sheets(DataSheet).Range("K4").Value

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

Workbooks(DataFile).Sheets(DataSheet).Range("K5").Value

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

Workbooks(DataFile).Sheets(DataSheet).Range("K6").Value

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

Workbooks(DataFile).Sheets(DataSheet).Range("K7").Value
End If
End If
Next sht
Workbooks(DataFile).Close SaveChanges:=False
Next I
Else
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.

 




Tagged: