• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to run a programme from external sheet

R

raypk

Member
Hi Everyone,

I need to compare workbooks of two different folders externally through a vba code in a excel sheet named Final as attached. I have a folder a with workbooks named file2,file2..and so on similarly another folder with workbooks having same name. For that i have developed a code mentioned below, what it does is compare workbooks having same name from two folders and highlight the mismatch.This i have done for one pair as shown in excel attached for file 1. But I am unable to write a code for running this for all pairs one after other. Guyz please help in looping the filepath as mentioned in attached file so that i can run programme all files one after other. Filepaths are always mentioned in A & B Cloumns.



Option Explicit

Sub Compare_Excel_Files_WorkSheets()
'Define Object for Excel Workbooks to Compare
Dim sh As Integer, ShName As String
Dim F1_Workbook As Workbook, F2_Workbook As Workbook
Dim iRow As Double, iCol As Double, iRow_Max As Double, iCol_Max As Double
Dim File1_Path As String, File2_Path As String, F1_Data As String, F2_Data As String

'Assign the Workbook File Name along with its Path
File1_Path = ThisWorkbook.Sheets(1).Cells(2, 1)
File2_Path = ThisWorkbook.Sheets(1).Cells(2, 2)
iRow_Max = ThisWorkbook.Sheets(1).Cells(2, 3)
iCol_Max = ThisWorkbook.Sheets(1).Cells(2, 4)

Set F2_Workbook = Workbooks.Open(File2_Path)
Set F1_Workbook = Workbooks.Open(File1_Path)
ThisWorkbook.Sheets(1).Cells(6, 2) = F1_Workbook.Sheets.Count

'With F1_Workbook object, now it is possible to pull any data from it
'Read Data From Each Sheets of Both Excel Files & Compare Data
For sh = 1 To F1_Workbook.Sheets.Count
ShName = F1_Workbook.Sheets(sh).Name
ThisWorkbook.Sheets(1).Cells(7 + sh, 1) = ShName
ThisWorkbook.Sheets(1).Cells(7 + sh, 2) = "Identical Sheets"
ThisWorkbook.Sheets(1).Cells(7 + sh, 2).Interior.Color = vbGreen

For iRow = 1 To iRow_Max
For iCol = 1 To iCol_Max
F1_Data = F1_Workbook.Sheets(ShName).Cells(iRow, iCol)
F2_Data = F2_Workbook.Sheets(ShName).Cells(iRow, iCol)

'Compare Data From Excel Sheets & Highlight the Mismatches
If F1_Data <> F2_Data Then
F1_Workbook.Sheets(ShName).Cells(iRow, iCol).Interior.Color = vbYellow
ThisWorkbook.Sheets(1).Cells(7 + sh, 2) = "Mismatch Found"
ThisWorkbook.Sheets(1).Cells(7 + sh, 2).Interior.Color = vbYellow
End If
Next iCol
Next iRow
Next sh

'''''Process Completed
ThisWorkbook.Sheets(1).Activate
MsgBox "Task Completed "

End Sub
 

Attachments

  • Final.xlsm
    13 KB · Views: 7
Would need sample of files that are being checked.

Also, there are more robust way to check for variance between 2 sheets.
 
Hi @Chihiro please find attached the sample input folders containing excel files which are being checked in Column A for example it checks Column A from File1 of folder A1 & Column A from File2 of folder A2..I do want to save highlighted difference in final sheets sheet2 onwards in final sheet....

Thanks please help if there is an robust way ..many many thanks...
 

Attachments

  • Input.rar
    22.1 KB · Views: 3
Last edited:
Why are you forcing members to use software to unzip your files, they are so small you could just upload them in your question
 
Is this your actual data? You only have 1 column and 1 sheet on each workbook. So there is no need to loop... Also none of the cells match in your sample.
 
@Chihiro this is just a sample data, i have kept it different so all rows containing it will be highlighted. I have only1 one column and 1 sheet in each workbook. Also changes will be in the column1 of sheet1 only for all files which i need to record in final sheet as it is with all all data copied in sheet1,sheet2..etc. of final workbook
 
Then upload final workbook with expected outcome from your sample. I don't quite get your requirement.
 
Final workbook is attached above .sheet 1should contain data of file 1 (which is located in A1 & A2 folder with same name but some changes ) with differences highlighted(rows).....sheet2 should contain data of file2 containing all data with highlighted differences(rows) and so on
 
@Chihiro Many Thanks ..this is working fine on 'xlsx' format files ....Just got one question what changes need to be done if the input file formats are '.dat' or '.csv'....
 
Never tried working with '.dat' format personally. For '.csv' just change the file name in list sheet and should work.

If need further help, upload both '.csv' & '.dat' format files and I can take a look at it.
 
You must log in or register to reply here.
Back
Top