Import data from other excel files

Hi,

Please help me import data from 2 different excel files.

I have 2 files with heading Employee Name, Employee Number and Contact
Details.
I want similar data to be imported in third file with similar headings.

For example:
I have Employee Name, John in both the excel files. I want this name John
along with his employee number and contact details to be imported in another
file.
There will be other names which are appearing only in any one of the excel
file.

Cheers,
Mandeep

Hi

When you have an employee either in one or another file (but never in both)
1. Using links:
(This method is applicable only, when there is some max number of possible
entries in both files). Let's assume the number of entries never exceeds 99.
Create a sheet p.e. EmployeeList, with headings in row1.
Into cell A2 in sheet EmployeeList enter the formula (I assume original
employee lists are on sheet Employees in files File1.xls and File2.xls,
which are stored in folder C:\MyPath):
=IF('C:\MyPath\[File1.xls]Employees'!A2="","",'C:\MyPath\[File1.xls]Employees'!A2)
and copy the formula to whole table and down to row 100.
Into cell A101 in sheet EmployeeList enter the formula:
=IF('C:\MyPath\[File2.xls]Employees'!A2="","",'C:\MyPath\[File2.xls]Employees'!A2)
and copy the formula to whole table and down to row 200.

Now you have the list of all employees, although with a lot of empty rows in
it. For some tasks it is enough, for some not.
Easiest way to get a list without gaps is to use an ODBC query to retrieve
non-empty rows into a new table. For this you have to define the table
EmployeeList as a named range, like
EmplList=EmployeeList!$A$1:$C$200
, and save the workbook after that.

Now you create a new sheet p.e. Employees, and create there an ODBC query
from Excel files, where your workbook is ODBC source, and the named range
EmplList is the source table. Set [Employee Number] Is Not Null as WHERE
clause and finish. In query Data Range Properties set the query to be
refreshed on Open(, and/or after some every time unit).

2. Using ODBC queries - it is more complex solution, and I can give only
general directions, as much depends on your data.
(You must have Analysis Toolpack installed, otherwise source workbooks must
be always opened too - it's a mystery for me why)
Define employee lists in both source files like in example above (no dynamic
ranges will work), p.e. EmplList1 and EmplList2. You can freely have a lot
of empty rows in those named ranges ready for future entries. Save source
files and close them.
In destination workbook, create sheets p.e. Employees1 and Employees2. On
sheet Employees1, create an ODBC query from Excel files like above, but
with File1.xls as source, and named range EmplList1 as source table. On
sheet Employees2, create an ODBC query from Excel files like above, but
with File2.xls as source, and named range EmplList2 as source table.
Define 2 named ranges in destination workbook (they too may be p.e.
EmplList1 and EmplList2 too). On 3rd sheet (Employees) create an ODBC query
from table on sheet Employees1 (or from Employees2) like previous queries.
Activate query wizard, and there SQL window. Change the SQL string to
somewhat like

SELECT EmplList1.[Employee Name], EmplList1.[Employee Number],
EmplList1.[Contact Details] FROM EmplList1 EmplList1 WHERE
EmplList1.[Employee Number] Is Not Null
UNION
SELECT EmplList2.[Employee Name], EmplList2.[Employee Number],
EmplList2.[Contact Details] FROM EmplList2 EmplList2 WHERE
EmplList2.[Employee Number] Is Not Null

or

SELECT EmplList1.[Employee Name], EmplList1.[Employee Number],
EmplList1.[Contact Details] FROM EmplList1 EmplList1 WHERE
EmplList1.[Employee Number] Is Not Null
UNION
SELECT EmplList2.[Employee Name], EmplList2.[Employee Number],
EmplList2.[Contact Details] FROM EmplList2 EmplList2 WHERE
EmplList2.[Employee Number] Is Not Null AND EmplList2.[Employee Number] Is
Not In (SELECT [Employee Number] FROM EmplList1)

(The latter is for case some employees are in both source lists)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Mandeep Dhami" <MandeepDhami@discussions.microsoft.com> wrote in message
news:D4F36D36-E034-4DFF-BFB1-573596578BB1@microsoft.com...
> Hi,
>
> Please help me import data from 2 different excel files.
>
> I have 2 files with heading Employee Name, Employee Number and Contact
> Details.
> I want similar data to be imported in third file with similar headings.
>
> For example:
> I have Employee Name, John in both the excel files. I want this name John
> along with his employee number and contact details to be imported in
> another
> file.
> There will be other names which are appearing only in any one of the excel
> file.
>
> Cheers,
> Mandeep