+ Reply to Thread
Results 1 to 12 of 12

Need to compare .xlsx files in tow different folders

  1. #1
    Registered User
    Join Date
    08-19-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    11

    Need to compare .xlsx files in tow different folders

    Hello,

    I am working in financial projects and we have been used macros for comparing the data files but i am facing some issues with comparing the files in two different folders.

    I have a long list of .xlsx files in one folder and same long list of .xlsx files in another folder with the same name .. so i need to compare both files because of versions will be different.

    E.g : Folder 1 : A.xlsx ,B.Xlsx - Version V 1.0 files
    Folder 2 :A.Xlsx , B.Xlsx - Version V 1.1 Files

    so i want to check where is the difference in those .xlsx files

    Can you please help me out to resolve this issue.

  2. #2
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Need to compare .xlsx files in tow different folders

    Hi

    Perhaps something like this.
    It finds comparable file names in 2 folders then identifies if the file lengths are different.
    It lists the names of those files that are different but not where they are different.
    Your post suggests you might already have that code and indicated is where the proc or function should be called from.

    To demonstrate the code I have used a file compare function authored by Ervin Kosch - see comments above his code.

    Install the sub and function in a standard module and change the directory names where indicated.

    hth

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-19-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Need to compare .xlsx files in tow different folders

    Hi,

    Thank you for sending the code.
    I am little bit confusion about macro code as i am poor in coding..And sorry to trouble you
    Below are things needs to check during my testing phase: -

    1) Compare files in two different folders with the same name (.xlsx) - Its working which is sent by you.
    2) once both files are match then we need to open those 2 files and compare contents in those 2 files
    3) After that need to report where is not matched with file name and column name + Data records.

    Appreciate your prompt reply..

    attached file for your reference..
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Need to compare .xlsx files in tow different folders

    Hi

    Some points to consider....
    This part of your project could get complicated because of differences, intended or not, in wbook construction. It is easy to compare the same cell in both wbooks and 'report' 'mismatches' of any type. But the whole thing can get messy if wbook construction has meant partial row or column displacement of same names, same lists etc as described below.

    If you wish to get things moving quickly in your project there are many comparison packages available, some free, some commercial. Just search for 'compare 2 files'.

    Apart from that you need to clarify and specify precisely what is needeed.

    3) After that need to report where is not matched with file name and column name + Data records.
    Several things come to mind...
    What do you mean by 'not matched'? For example cell "A1" in the 2 wbooks may 'not match' by value, by formula, by format. The wbook may 'not match' by any number of wbook properties listed under File>Properties or by macro.
    How many wsheets are to be compared in each wbook?
    Are all wbooks and wsheets constructed identically? For instance, in wbook1 there may me a list in col "A" starting at row 10. Wbook2 may have an identical list but start at row 5.
    Where are the exceptions to be reported? For example you could highlight cell differences in the wbook in the 'to be matched folder'. You might want exceptions listed in the wbook where your code is kept.


    column name + Data records.
    What you mean?


    2) once both files are match then we need to open those 2 files and compare contents in those 2 files
    Perhaps you mean 'once 2 same name files are found to be mismatched- we need to open them?


    The supplied 'reference' wbook with a name in 3 cells unfortunately means nothing.

  5. #5
    Registered User
    Join Date
    08-19-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Need to compare .xlsx files in tow different folders

    Hi, Thank you for your quick reply .. Here i can give you more details on this

    3) After that need to report where is not matched with file name and column name + Data records.
    How many wsheets are to be compared in each wbook? Only one sheet.
    Are all wbooks and wsheets constructed identically? Yes

    In this case my expectation would be : Want to compare both sheets and at the same time need to compare column by column
    E.g in File 1 - Sheet1 -
    Column name is :Equity Type ( Row A)
    File 2 - Sheet1 -
    Column name is :Equity Type ( Row A)

    So now i want to check is there any differences are there in those 2 files


    Where are the exceptions to be reported? As per code now report will be showing like below
    Total Source Files Num of File Names Matched Num of File Names Not Matched Data MisMatch File Names
    234 231 3 All found files compared

    Yesterday i ran the code using my source and target files and result are shown above.
    here in report it shows how many Num of File Names Not Matched (3) but need to know which are those ( like file name at least ) so that i can open that file and can compare both 2 files( source and target files).


    column name + Data records.

    Here in this case my expectation would be.. As per point 3 above , i also need to compare data records in both source and target files.


    2) once both files are match then we need to open those 2 files and compare contents in those 2 files

    same as above.. I mean as per point 3 ..Report is showing how many files are not matched at the same time once files are matched need to compare contents for both the sheets.
    In this case my expectation would be : Want to compare both sheets and at the same time need to compare column by column
    E.g in File 1 - Sheet1 -

  6. #6
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Need to compare .xlsx files in tow different folders

    Hi

    File 1 - Sheet1 -
    Column name is :Equity Type ( Row A)

    File 2 - Sheet1 -
    Column name is :Equity Type ( Row A)
    You can have 'Col A' or 'Row 1' but there is no such thing as 'Row A'.
    Your supplied wbook contained 3 values only, in col A. They were namely Equity Type, Conventional Equity and Strategic Equity. From your example above are they meant to be column headers?
    Please clarify.


    Up to now we have established that...
    1. There is only 1 wsheet per wbook

    2. Wbooks are always identically constructed
    Can you tell me therefore how many columns and how many rows are used in every wsheet?


    2) once both files are match then we need to open those 2 files and compare contents in those 2 files

    same as above.. I mean as per point 3 ..Report is showing how many files are not matched at the same time once files are matched need to compare contents for both the sheets.
    In this case my expectation would be : Want to compare both sheets and at the same time need to compare column by column
    E.g in File 1 - Sheet1 -
    This is confusing.
    From the above you have indicated twice 'once files are matched need to compare contents for both the sheets'.
    Why do you want to compare files that do match? They can surely be ignored. Don't you mean compare those files which do not match and list the differences in each column?


    You did not reply what you wanted to match in each cell, is it value, format or formula? Value would be the usual thing but please clarify.

  7. #7
    Registered User
    Join Date
    08-19-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Need to compare .xlsx files in tow different folders

    Hi,

    In that file column is " Equity Type" and rest 2 are data(Conventional Equity and Strategic Equity). as a simple actually we will be getting a source files from the business team every one month( Approx) that time we will be treated as V 1.0 and next month we will be getting the same files from the business team and that time we will be treated as V 2.0 but not sure all files are identical that means not sure whether the column headers are same and also not sure whether the file has same records.

    From testing perspective we need to check both the files ( V 1.0 files and V 2.0 files) before loading the data into DB. but each and every time we can't check manually for all 230 files ( approx) for that i need to compare all the files ( v 1.0 and V 2.0) using macro code.

    I hope you understand my requirement what i am trying to do here....


    You did not reply what you wanted to match in each cell, is it value, format or formula? Value would be the usual thing but please clarify.
    --- i want to match for each and every cell and row from those 2 files....and to confirm there is no difference in those files even version is different..

  8. #8
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Need to compare .xlsx files in tow different folders

    Hi phanikumar

    Try this.
    The code is much too large to show in the usual manner and you will need to download the attached wbook. There is a limit of 10,000 characters and this is well over that figure. If there is any difficulty come back.

    The procedure iterates through all files in the target folder which have the required file extension and compares every used cell in the same name file in the source folder.
    Ensure a default 3 wsheet wbook is used.

    Install all the code in a standard module in your own wbook, or just use the wbook supplied.
    Change the directories and if necessary the file extension to suit where I have indicated.
    If you access the code through Tools>Macro>Macro then look for 'CompareFiles'

    hth

    gmk


    File Comparison.xls

  9. #9
    Registered User
    Join Date
    08-19-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Need to compare .xlsx files in tow different folders

    Hi,

    Thank you for sending the code.. I will check and will let you know..

  10. #10
    Registered User
    Join Date
    08-19-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Need to compare .xlsx files in tow different folders

    Hi GMK,

    I ran the code and it is working fine as per my requirement. Thank you so much for sending the code.

  11. #11
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Need to compare .xlsx files in tow different folders

    Hi phanikumar

    I am pleased we have it sorted now.

    If you feel I have helped perhaps you will mark the thread as solved and also click the star to add to my reputation.

    gmk

  12. #12
    Registered User
    Join Date
    08-19-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Need to compare .xlsx files in tow different folders

    Hi GMK,

    Sorry toi distrub you.. it was very useful the script which you have sent. earlier it was working fine without error but today i tried to run again but giving some error "Application defined or object -defined error "occured could you please check and do the needful

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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