Convert Excel Workbooks into Text files, Compare them & store result back ito Excel

[VishalkumarT]

Dear friends,

I have posted one querry here before few days and got the best reply in short time.

Thanx a lot for the same.

I hope to have it this time as well.

My querry is, I want to compare two Excel workbooks, having some (for example 10) worksheets in each, with same format (same number of row/columns and style of data store in it).

But this time due to typical data processing methods, I want to compare them in a different way.

I want to CONVERT these two workbooks into text files (with proper tabs/spaces for columns and so, for all worksheets in them), COMPARE these text files in some softwares like UltraEdit or Araxis Merge (which I found in Internet), GET the comparison text file with proper result (cell differences and/or lines-or-rows added and/or lines-or-rows removed, in all worksheets) and in the end, CONVERT this resulting file BACK INTO excel, to see the differences/result in the excel environment only.

Is there any possible way which can be suggested here to solve this problem ?

Thank you for your replies in advance.

Best Regards
Vishalkumar

P.S. Thanks Simon for your reply of my last post.

[Oorang]

Hi Vishalkumar,
Exporting them to text and importing the combined file is fairly easy, but the main issue is how you want to perform the compare. You can only automate the compare with VBA if the program you want to do the compare with had exposed an API, COM server or some other program interface VBA is capable of working with. (Sometime you can work around this with clever use of command line if the program has that feature.) Otherwise you are stuck performing the comparison with the native abilities of VBA (which is generally for doable, although not always trivial). Have you investigate the abilities of UltraEdit or Araxis?

[EdNerd]

Why not export into a Word table, use VBA to compare and generate a report, then export the table back into Excel? And you can do it all from within an Excel macro.

Ed

[Oorang]

For that matter why export/import at all? Why not just do the compare and make the changes? I assume it is because the aforementioned software offers some functionality not available in Excel.

[VishalkumarT]

Hello Oorang

Yes I have used the UltraCompare, which has command line option, which i can use from shell("") ...

So now it s working.
And I have to see the GUI things in VBA, and I am sure, if I ll need any help from this forum, that too i ll get for sure.

Thank you
Vishalkumar

[Oorang]

Just as an FYI, it looks like Araxis Merge does have a full API http://www.araxis.com/merge/topic_automationexamples.html

[VishalkumarT]

Hi

Thanx any ways.