+ Reply to Thread
Results 1 to 16 of 16

Compare Specific Columns in all workbook saved in folder and update variance in new file

  1. #1
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    102

    Compare Specific Columns in all workbook saved in folder and update variance in new file

    Please help me to create VBA code

    I have saved 5 excel files in one folder (as sample). C:\test\
    Each Files having unique data in column B, C and J
    I want to track the changes of column J amount in each excel files.
    if any row having variance in one workbook to another, then this need to be copied to the result excel file as above.
    All version of excel file data updated in result file ( only for if its changed from one work book to another workbook)

    Result
    Column B and C is from the Work book 1 to %
    Row D4 to G4 is file name which we are comparing
    wherever the numbers are varying that need to be populated

    And Column B and C is fixed one. Column J is not fixed one this may be column I or column K..
    So before running the macro column J(amount) to inputed through message box. Like if its K then all work book its K only
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by SARAN89; 08-26-2020 at 11:09 PM. Reason: Adding attachment

  2. #2
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    102

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Anyone please help on this request

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Please Login or Register  to view this content.
    Edit;
    Missed
    So before running the macro column J(amount) to inputed through message box. Like if its K then all work book its K only
    Last edited by jindon; 09-04-2020 at 04:43 AM.

  4. #4
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    102

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Hi Jindon

    Thank you so much for your help.

    I tried to execute your code, the code browsing for folder but its not listing the files in the folder.

    My files extension are xlsx. i changed the below line also to xlsx instead xls but its not showing the excel files


    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    It should detect xls, xlsx, xlsm up to 4 characters starting with xls.

    Try add asterisk and see how it goes.
    fn = Dir(myDir & "*.xls*"): t = 3

    I have tested the code before I post and it is working.

  6. #6
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    102

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    For me its showing blank tried with asterisk also
    Attached Images Attached Images

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Should work
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    102

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Hi Jindon

    Thanks. Its not displaying to select the files, however if i click ok, then the code is running (Seems its selecting automatically but not displaying )
    And i am also getting the results.

    only one issue.

    As mentioned in the request list . The results are required only wherever the amount is changed. if no change then that should be ignored

    see the current results. Green highlighted items are no change hence it should be ignored. Please help to change this please
    Attached Images Attached Images

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Compare each one of the workbook for unique Code & Reference?

    How do you define "Change"? differs to what?
    If all the values are completely different or just 2 or 3 are the same but others.

    Need to know the basic value to compare...

  10. #10
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    102

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Hi Jindon

    Code and amount is the base for comparison.

    For each code, the amount is same in all workbook then it should be ignored else for any codes the numbers are different from workbook 1 to others then this need to be updated as results .

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    When you say, "Workbook 1", it fully depends on the order of Folder or do you supply all the file names and the folder path somewhere?

  12. #12
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    102

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Hi Jindon

    I have saved excel files in folder. each file name ending with version number starting from 1
    Like Workbook-1, workbook-2, workbook-3

    also files modified date and time also different in each files. Which is convenient for you accordingly take it please

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    OK, the file names should have formatted to be sorted correctly as you posted.
    Verify the result.
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    102

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    hi Jindon

    Thank you so much its perfectly working. I am getting results as expected.

    My Actual File name is like this Test Aug'20_v1,Test Aug'20_v2,Test Aug'20_v3

    No change on V1, V2, V3 but because of the text "Aug'20" the code not updating results when i change the file name to Test_v1, Test_v2 its perfectly working

    once again thanks. if possible and if its small changes then help me to consider the file name as above.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Quote Originally Posted by SARAN89 View Post
    My Actual File name is like this Test Aug'20_v1,Test Aug'20_v2,Test Aug'20_v3
    Replace GetData sub procedure with
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    102

    Re: Compare Specific Columns in all workbook saved in folder and update variance in new fi

    Thanks Jindon working as expected.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 05-08-2020, 07:58 PM
  2. Macro wont open file it had created earlier
    By ILoveStMartin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2019, 03:26 AM
  3. Save workbook with new dynamic file name to specific folder.
    By Destiny1990 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-19-2018, 12:51 PM
  4. Find / open and update a file in a specific folder
    By mariec_06 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2016, 07:04 AM
  5. Replies: 3
    Last Post: 12-28-2015, 08:14 AM
  6. VBA open yesterdays saved file in a specific folder
    By forfiett in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2014, 08:17 AM
  7. saving to a specific file as workbook is update
    By superchew in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-09-2013, 06:22 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1