Hi all,
i'm new here, and i hope someone can help me. The problem is that i have a file with thousands of rows, and a couple of dozens of columns, that needs to be updated everyday with data from 2-5 other excel files. is there a macro to do this update using the Date (today date) as criterion? i attached some test files, to see what i'm talking about. thanks.
Hi ScarfaceLex
Welcome to the Forum!
Are all files in the same folder? Are there any other files in the folder other than the Master File and these other 2-5 Data Files?
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
all the files are in the same folder and there are not any other files in the folder.
Hi ScarfaceLex
Try this code in a General Module...let me know of issues.
Please Login or Register to view this content.
hello, thanks for working at my problem.
your code works good...it does what it should do, but it still doesn't solve my problem. let me explain it better. files 1 and 2 have the exact same data as the master file, but in some cases some data is updated manually (newentry/newinfo) . well, now these new entries must replace the old data in the master file. i hope i explained better this time.
Hi ScarfaceLex
What's the key between FILE 1 and MASTER FILE? What's the key between FILE 2 and MASTER FILE? I'm missing something...I don't see the link among the files.
i uploaded new files to see exactly what it's all about. each day, a copy of master file is given to some operators. they call at different companies in the database (master file) and they collect data ( eg : columns B, H, i, J). i marked with red the updated data in files 1 and 2. then i have to replace the old data in the master file (i receive a clean copy before they start calling companies) with the red marked data in files 1 and 2 . the new file (master file - updated)becomes master file for the next day, and so on.
each day, i filter the data in the files 1 and 2 after the Date (col. G), and then i have to copy-paste manually hundreds of entries in the master file.
Hi ScarfaceLex
Try this code...gotta warn you...may take some time to run with thousands of rows. Let me know of issues.Please Login or Register to view this content.
Hi jaslake,
i don't see any changes in the master file after running the code. i should do anything else than just running the code?
i thought at something else...you think that it's easier to do this in microsoft access? i tried to make a db in access but all i succeded was to merge the files, not to replace the old data with new ones.
thanks again for working at this...i already owe you a couple of beers
Last edited by ScarfaceLex; 07-16-2012 at 03:05 AM.
Hi ScarfaceLex
If your files don't contain any records with today's date you won't see any change to the Master File.
hi jaslake,
you are right
Hi ScarfaceLex
Hmm...I just reran the code and didn't get any errors. What line of code?
Last edited by jaslake; 07-16-2012 at 01:43 PM. Reason: Attach Files
when i try to run from ln 66 to ln 67 i get that error.
How about posting a screen shot of the debug screen so I can see the actual line.
i attached some images. i hope it helps.
Place the three files I attached into a separate folder, open Master File and click the button...let me know if you get the error. I can't duplicate the error on these files.
oh my god...everything works fabulous on your files. this is exactly what i needed. damn...you are a genius, man.
i don't know why it works on your files but it's absolutely fantastic. you have just saved me from hours of assiduous work. i owe you big time, tell me how can i repay you for working at this.
You're welcome...glad it works for you. Did you get it to work on your live files?
Click on the star at the lower left of one of my posts will be thanks enough...also, if this satisfies your need please mark your Thread as SOLVED.how can i repay you for working at this
Last edited by jaslake; 07-16-2012 at 04:32 PM.
unfortunately, when i run the code on my real files, i get an error : "subscript out of range", at line 38.
Hi ScarfaceLex
I don't have line numbers in my copy of the code...attach an image of the debug screen and I'll look at it. That error message typically means a non-existent object has been called.
hi jaslake,
i solved that problem, the code runs without errors but nothing happens in the end. can i send you my real files in private, to take a look at them?
Sure...I'll PM the email address.
Hi ScarfaceLex
In your files attached to this Thread the key among the files is Column A. In the files you emailed me the key is not Column A. Which Column has unique (not duplicated) data?
Column C is the one with unique data. what should be changed in the code?
There are four lines of code that require change:
I ran the revised code but can't tell if changes have been applied (too much data). I know it's doing a copy/paste operation. Here's the complete revised code...test it and let me know of issues.Please Login or Register to view this content.Please Login or Register to view this content.
it works perfectly. so, basically, if i want to adjust the code for another database i should change those four lines, yeah?
Last edited by ScarfaceLex; 07-17-2012 at 02:22 PM.
I can't say with certainty...too many unknowns...but probably, quite possibly. Best I can suggest is try it...you may like the results. If you don't come back to the Forum.
ok, i'll do that
You're welcome...glad to be of help.
One thing you should be aware of...your files contain a duplicate some where in Column C. Column C contains 3185 records. When you do an Advanced Filter for unique records the count is 3184 records.
If this satisfies your need please mark your Thread as SOLVED.
ok, thanks for telling me. i'll have a look at it.
i'll mark the thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks