There is one main excel file of which the structure is fixed. And there will be number of excel files coming to our end with another fixed structure. Now, what I need is to compile all the user excel files of required fields into the required field of the main file. This should be done on condition based and after matching of the data only the data transfer should be done automatically. So, please if anyone could guide me, it will be really helpful to me....

Looks like you need to do something a little more advanced than just linking.
I'd suggest going to BOL, and reading up on "Importing Data", specifically the section about Microsoft Query.

I've done something similar with a query table. Does required the source files to be closed and, I think, also requires named ranges for the source data.

The query table has an SQL CommandText that you will need to create. Requires a modicum of knowledge of SQL.

The CommandText - which you can create programmatically for a complex case - might be something like,

SELECT whatever fields
FROM data source
WHERE your conditions

[then for each subsequent data source]
UNION
SELECT fields
FROM next data source
WHERE conditions

You can set properties for the query so that it refreshes on file opening.

It was quite fast for my application - which involved pulling data from a variable number of data files within a directory. Typically a few dozen files. Via VBA I scanned the directory, checked for files containing data and then created the query. About 10,000 records were taken from the source files and put into the destination file. The whole operation takes a few seconds.

MS Query will get you only so far for a complex requirement, after that I think you need to know a little SQL. You can write the SQL straight into MS Query but it is far easier to do via VBA, IMO.

Another thought, this is pushing Excel a little and it may be better to do the work in MS Access. Or, forget the query table approach and do the work in Excel via VBA - filtering ranges and cutting & pasting from the source files into your destination file. It is a more basic approach but can do the job.

Certainly as RedNeckGeek wrote, linking is not the way to go. HTH

regards,
Fazza

Bumping this thread as it did not jump to the top of listed posts when I posted a few minutes ago, and it does not show my above post as the last post on the thread. RedNeckGeek's post shows as the last post still.....

You could use a VB macro that acts on the open workbook or uses a recursive procedure to open files in a receiving directory. Once open the procedure would directly transfer data from defined fields in the source workbook to the appropriate fileds in the target workbook.

When doing this I suggest creating named ranges in your source workbook to identify the data fields. This will allow you to refer to the fields by the defined name insted of R1C1 Row Column method. This provides the advantage of being able to change the layout of the source workbook without having to re-write your code just because the field locations change.

with named range:
SourceBook.Range("SalesTotal") = TargBook.Cells(r,1)

w/o named range:
SourceBook.Range("B3") = TargetBook.Cells(r,1)

Using a query method as suggested works good if you are extracting data from workbooks having a list of records. If it is Excel form where data is not listed by rows then it may be easier to open both workbooks and walk through the fields using a macro.