Append data in master file from 4 different files

[rajagopal]

Hi,

I've four files named as Daily tracker_A.xls; Daily tracker_B.xls; Daily tracker_C.xls; Daily tracker_D.xls in C:\data

These files will be updated (append) by respective users (column A to Q) every day. I want to do the following:

1. When the supervisor opens a master excel file (master.xls), file merger option should trigger "Please key in a entry date to merge the data".
2. Once a date is entered, macro should retrieve all the data (column A to Q) from respective excel files and append it in master excel file.

In the individual excel files, i want to capture the date of change in cilumn H whenever user enters or modifies content in column G (except first header row)

Attached the excel. Please help..

[fmcti]

The attached includes the function to insert the date automatically.

[rajagopal]

Hi,
Thanks for this. I want the dates to be deleted when users delete entries in column G.

[rajagopal]

Can you also please look into my requirement of merging the files?

[fmcti]

A small change is made to your requirement. See attached file.

With regard to other question, you'd better attach sample files with data and result you wanted. This way it will be easy to understand and people are more likely to help.

[rajagopal]

Hi,

Thanks and I've attached the sample files the merging functionality.

Due to site restriction, attached the files in successive updates..

[rajagopal]

Sample 2

[rajagopal]

Sample 3

Output requirement:

The data in sample 1, 2 & 3 are updated by individual users (files are kept in a common drive) - will add current day's data to their existing data on a daily basis. I want the macro to retrieve the latest updated data from the files sample 1, 2 & 3 to a master excel file as and when the macro is executed.

[fmcti]

Please find attached files for the merge function.

Before you can use the merge function you need to select the sheet "Setting" in Master.xls to set the paths for the three source files. It should be different from the ones I used for my testing.

When you open the Master.xls or select the first sheet in that file your will be prompted with an input box asking for the date for summary (default date is the current system date). Enter the date and click Ok, it will retrieve the correspondent data from the three source files.

[rajagopal]

When i give the dates in master file, it didn't retrieve the data. The master file was blank. Please help..

[rajagopal]

Getting an error like this
Run time error.. 'Sheet1$' is not a valid name. make sure that it does not include invalid characters or punctuation and that it is not too long..

Error highlight shown in the row
GetData.Open SQL, myCnn, adOpenDynamic, adLockOptimistic

[fmcti]

Please name the three source data sheets as:

Sheet1

[rajagopal]

I did but even then the data was not pulled in to the master sheet..

[fmcti]

Do you have data (Date of update) in the source files? Can you attach the source files you are using?

[rajagopal]

PFA the sample files

[rajagopal]

PFA the sample files

[rajagopal]

Sample file3

[fmcti]

It works for me. You can see attached screen shot. Did you try it at your local machine or on the shared network?