Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Posts
    18

    Unanswered: Data transfer between excel files automatically

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

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    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.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2006
    Posts
    113
    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
    Last edited by Fazza; 02-07-06 at 20:51.

  4. #4
    Join Date
    Feb 2006
    Posts
    113
    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.....

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    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.
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •