Compare 2 excel columns to get a match ...

Jason Yousef
Jason Yousef used Ask the Experts™
on
Hi Everyone,

i have two excel Sheets, would like for someone to show me Please or provide a snippet of VBScript to compare specific columns and a print the match in a seprate file.


DB.xls
-------------------------

FName  LName  Address    ZIP      EMP#  
---------------------------------------------
aa      CLI    TEST     70200    e234234234
bb      BLI    DOE      70200       
cc      AEL    SMITH      70200       e234234234
dd      AEL    JOE      70200       OTH00000
ee      CLI    COF      70200       r234234234


Emplyees List.xls
------------------

EMP#  
------------
e234234234
c234234234
e234234234  <----------if any number matches with the list above print the whole record in a report
f234234234
r234234234


If a match found: create a new file (any report format file)


Match List:
------------------

aa      AEL    SMITH      70200       e234234234  Match


If an employee has "OTH00000" or no number at all, need it also to come in the report as a possible match


Thanks in advance..and apologize for my bad example if it's misleading
God bless anyone who tries to help and share the knowledge...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi there, try this VBS code.  Copy the code to Notepad, save the file as GetReport.vbs and double-click it to run it.

Change the top three lines to the correct file paths.

Regards,

Rob.
strDB = "C:\Temp\Temp\Test Script\DB.xls"
strEmp = "C:\Temp\Temp\Test Script\Emp.xls"
strReport = "C:\Temp\Temp\Test Script\Report.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Const xlUp = -4162
Set objDB = objExcel.Workbooks.Open(strDB, False, False)
Set objEmp = objExcel.Workbooks.Open(strEmp, False, False)
Set objReport = objExcel.Workbooks.Add
Set objDBSheet = objDB.Sheets(1)
Set objEmpSheet = objEmp.Sheets(1)
'Read the employee IDs from the EMP sheet into a dictionary object
Set objEmpDict = CreateObject("Scripting.Dictionary")
For lngEmp = 2 To objEmpSheet.Cells(65536, 1).End(xlUp).Row
	strEmpID = LCase(objEmpSheet.Cells(lngEmp, 1).Value)
	If objEmpDict.Exists(strEmpID) = False Then objEmpDict.Add strEmpID, 0
Next
' Add the OTH00000 match to report on
If objEmpDict.Exists(LCase("OTH00000")) = False Then objEmpDict.Add LCase("OTH00000"), 0
objEmp.Close False
Set objReportSheet = objReport.Sheets(1)
objReportSheet.Cells(1, 1).Value = "FName"
objReportSheet.Cells(1, 1).Value = "LName"
objReportSheet.Cells(1, 1).Value = "Address"
objReportSheet.Cells(1, 1).Value = "Zip"
objReportSheet.Cells(1, 1).Value = "EMP#"
objReportSheet.Rows("1:1").Font.Bold = True
' Now read through the DB sheet
For lngEmp = 2 To objDBSheet.Cells(65536, 1).End(xlUp).Row
	strEmpID = LCase(objDBSheet.Cells(lngEmp, 5).Value)
	If strEmpID = "" Or objEmpDict.Exists(strEmpID) = True Then
		lngReportRow = objReportSheet.Cells(65536, 1).End(xlUp).Row + 1
		objDBSheet.Range("A" & lngEmp & ":E" & lngEmp).Copy objReportSheet.Range("A" & lngReportRow)
	End If
Next
objDB.Close False
objExcel.DisplayAlerts = False
objReport.SaveAs strReport
objExcel.DisplayAlerts = True
MsgBox "Finished."

Open in new window

Jason YousefSr. BI  Developer

Author

Commented:
Hi RobSampson:  Thank you so much for helping me...

But the results comes in an empty Excel file ... and it's always empty, even if there's a match or OTH000 or empty cell.

is it something I'm doing wrong?

Thanks and have a nice day :)
Slice-1.jpg
Most Valuable Expert 2012
Top Expert 2014

Commented:
What you need to is change these:
strDB = "C:\Temp\Temp\Test Script\DB.xls"
strEmp = "C:\Temp\Temp\Test Script\Emp.xls"
strReport = "C:\Temp\Temp\Test Script\Report.xls

so that strDB points the DB.xls that you have in your example, strEmp points to the Employee List.xls that you have in your example, and strReport if a file name that you want the results saved as (and will be overwritten).

The sheet in DB.xls that has these fields:
FName  LName  Address    ZIP      EMP#  

must be the first sheet in that workbook.

The sheet in Employee List.xls that has this field:
EMP#

must also be the first sheet in that workbook.

The EMP# in the first sheet of DB.xls must be Column 5 (Column E), and it must be the first column in Employee List.xls

If any of that needs changing, let me know.

Regards,

Rob.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Jason YousefSr. BI  Developer

Author

Commented:
Hi Rob,

Yes, I've changed the paths to reflect the Database, but looks like the Header names was different (lastname) not (Lname)

But it's working now, thank you so much, but I think still something is wrong.

as the EMP.XLS  628 Records, DB.XLS  47000 Records, and the Report.XLS came 38000 records.

looks like it's working backwards, as it should check our employees license's numbers in EMP.XLS if their license matches on the do not hire list DB.XLS that comes from the state every 3 months.

and display only the matches or the possible matches if the employee doesn't have a license or it's on the report listed as OTH000 or OTH00000  :)

I'm sorry if I confused you or my example was misleading...I do apologize

I've attached a screen shot of the report page, if it's possible and not gonna take time from you, can we add headers to the columns?

Thanks and have a wonderful weekend :)
Jason



report.jpg
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, if you say that it is reporting backwards, I have reversed the logic....hopefully this is better.

Regards,

Rob.
strDB = "C:\Temp\Temp\Test Script\DB.xls" 
strEmp = "C:\Temp\Temp\Test Script\Emp.xls" 
strReport = "C:\Temp\Temp\Test Script\Report.xls" 
Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 
Const xlUp = -4162 
Set objDB = objExcel.Workbooks.Open(strDB, False, False) 
Set objEmp = objExcel.Workbooks.Open(strEmp, False, False) 
Set objReport = objExcel.Workbooks.Add 
Set objDBSheet = objDB.Sheets(1) 
Set objEmpSheet = objEmp.Sheets(1) 

Set objReportSheet = objReport.Sheets(1) 
objReportSheet.Cells(1, 1).Value = "FName" 
objReportSheet.Cells(1, 2).Value = "LName" 
objReportSheet.Cells(1, 3).Value = "Address" 
objReportSheet.Cells(1, 4).Value = "Zip" 
objReportSheet.Cells(1, 5).Value = "EMP#" 
objReportSheet.Rows("1:1").Font.Bold = True 
' Now read through the EMP sheet 
For lngEmp = 2 To objEmpSheet.Cells(65536, 1).End(xlUp).Row 
	strEmpID = LCase(objEmpSheet.Cells(lngEmp, 1).Value) 
	lngFoundRow = 0
	For lngDB = 2 To objDBSheet.Cells(65536, 5).End(xlUp).Row
		If strEmpID = LCase(objDBSheet.Cells(lngDB, 5).Value) Then
			lngFoundRow = lngDB
			Exit For
		End If
	Next
	lngReportRow = objReportSheet.Cells(65536, 1).End(xlUp).Row + 1 
	If lngFoundRow > 0 Then 
		objDBSheet.Range("A" & lngFoundRow & ":E" & lngFoundRow).Copy objReportSheet.Range("A" & lngReportRow)
	ElseIf Left(strEmpID, 5) = "oth00" Then
		objReportSheet.Range("A" & lngReportRow).Value = strEmpID
	End If
Next 
objDB.Close False 
objEmp.Close False
objExcel.DisplayAlerts = False 
objReport.SaveAs strReport 
objExcel.DisplayAlerts = True 
MsgBox "Finished."

Open in new window

Jason YousefSr. BI  Developer

Author

Commented:
Rob,
Good morning...
Thank you so much, works great this time. it actually pulled the test matches that I added, but for the people with blank # or oth000 , it returned no names or address.

see in the attached picture.

Also it took 4 hours to run through the 47K records..is that's ok or it can be a little faster?

something else, how can I add more fields to get a possible match such as "oth000"  as the new report has "othoooo"  & "oth00" and some other stupid nonsense characters?


Thanks so much

Slice-1.jpg
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hmmm, try this.  I've changed the search method to use Excel's Find method, not sure if it will be quicker or not though.....

Also, if an exact match entry (case insensitive) from EMP.xls is NOT found in DB.xls, it will not be added to the Report, but if it is NOT found AND the first three characters are OTH, it will be added to the report as NO MATCH.

I hope that helps.

Regards,

Rob.
Most Valuable Expert 2012
Top Expert 2014
Commented:
Ooops, forgot the code....
strDB = "C:\Temp\Temp\Test Script\DB.xls" 
strEmp = "C:\Temp\Temp\Test Script\Emp.xls" 
strReport = "C:\Temp\Temp\Test Script\Report.xls" 
Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 
Const xlUp = -4162 
Const xlValues = -4163
Const xlWhole = 1
Const xlByRows = 1
Const xlNext = 1
Set objDB = objExcel.Workbooks.Open(strDB, False, False) 
Set objEmp = objExcel.Workbooks.Open(strEmp, False, False) 
Set objReport = objExcel.Workbooks.Add 
Set objDBSheet = objDB.Sheets(1) 
Set objEmpSheet = objEmp.Sheets(1) 

Set objReportSheet = objReport.Sheets(1) 
objReportSheet.Cells(1, 1).Value = "FName" 
objReportSheet.Cells(1, 2).Value = "LName" 
objReportSheet.Cells(1, 3).Value = "Address" 
objReportSheet.Cells(1, 4).Value = "Zip" 
objReportSheet.Cells(1, 5).Value = "EMP#" 
objReportSheet.Rows("1:1").Font.Bold = True 
' Now read through the EMP sheet 
For lngEmp = 2 To objEmpSheet.Cells(65536, 1).End(xlUp).Row 
	strEmpID = LCase(objEmpSheet.Cells(lngEmp, 1).Value) 
	'For lngDB = 2 To objDBSheet.Cells(65536, 5).End(xlUp).Row
	'	If strEmpID = LCase(objDBSheet.Cells(lngDB, 5).Value) Then
	'		lngFoundRow = lngDB
	'		Exit For
	'	End If
	'Next
	Set objCellRef = objDBSheet.Columns("E:E").Find(strEmpID, objDBSheet.Cells(1, "E"), xlValues, xlWhole, xlByRows, xlNext, False, False)
	lngReportRow = objReportSheet.Cells(65536, 1).End(xlUp).Row + 1 
	If Not objCellRef Is Nothing Then
		objDBSheet.Range("A" & objCellRef.Row & ":E" & objCellRef.Row).Copy objReportSheet.Range("A" & lngReportRow)
	ElseIf Left(strEmpID, 3) = "oth" Then
		objReportSheet.Cells(lngReportRow, 1).Value = "NO MATCH"
		objReportSheet.Cells(lngReportRow, 2).Value = "NO MATCH"
		objReportSheet.Cells(lngReportRow, 3).Value = "NO MATCH"
		objReportSheet.Cells(lngReportRow, 4).Value = "NO MATCH"
		objReportSheet.Cells(lngReportRow, 5).Value = strEmpID
	End If
Next 
objDB.Close False 
objEmp.Close False
objExcel.DisplayAlerts = False 
objReport.SaveAs strReport 
objExcel.DisplayAlerts = True 
MsgBox "Finished."

Open in new window

Jason YousefSr. BI  Developer

Author

Commented:
Rob,

You're the best, took less than 10 seconds to run !!!!!!!! I couldn't believe it !

but the only thing missing is ...

Rob said ---> "but if it is NOT found AND the first three characters are OTH, it will be added to the report as NO MATCH"  

Can we pull the lname, fname, address, zip, emp# for the "blank and OTH" to the report, add them as possible matches, so we can call them and get the license number and etc...

I mean get their actual name and address and the rest of the info, instead of displaying "no match"

i've attached a screen shot and the code i'm using after some coloring :)

Thank you so much






strDB = "C:\Documents and Settings\bsmith9\Desktop\projects\1\side test\DB.xls" 
strEmp = "C:\Documents and Settings\bsmith9\Desktop\projects\1\side test\Emp.xls" 
strReport = "C:\Documents and Settings\bsmith9\Desktop\projects\1\side test\Report.xls" 


Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = False


Const xlUp = -4162 
Const xlValues = -4163
Const xlWhole = 1
Const xlByRows = 1
Const xlNext = 1

Set objDB = objExcel.Workbooks.Open(strDB, False, False) 
Set objEmp = objExcel.Workbooks.Open(strEmp, False, False) 
Set objReport = objExcel.Workbooks.Add 
Set objDBSheet = objDB.Sheets(1) 
Set objEmpSheet = objEmp.Sheets(1) 


Set objReportSheet = objReport.Sheets(1) 

objReportSheet.Cells(1, 1).Value = "First Name" 
objReportSheet.Cells(1, 2).Value = "Last Name" 
objReportSheet.Cells(1, 3).Value = "Street Address" 
objReportSheet.Cells(1, 4).Value = "Zip Code" 
objReportSheet.Cells(1, 5).Value = "UPIN #" 

    objReportSheet.Range("A:A").EntireColumn.ColumnWidth = 15
    objReportSheet.Range("B:B").EntireColumn.ColumnWidth = 15
    objReportSheet.Range("C:C").EntireColumn.ColumnWidth = 25
    objReportSheet.Range("D:D").EntireColumn.ColumnWidth = 10
    objReportSheet.Range("E:E").EntireColumn.ColumnWidth = 10


objReportSheet.Rows("1:1").HorizontalAlignment = -4108
objReportSheet.Rows("1:1").Interior.ColorIndex = 30
objReportSheet.Rows("1:1").Font.ColorIndex = 2
objReportSheet.Rows("1:1").Font.Size = 12 
objReportSheet.Rows("1:1").Font.Bold = TRUE


' Now read through the EMP sheet 
For lngEmp = 2 To objEmpSheet.Cells(65536, 1).End(xlUp).Row 
        strEmpID = LCase(objEmpSheet.Cells(lngEmp, 1).Value) 
        'For lngDB = 2 To objDBSheet.Cells(65536, 5).End(xlUp).Row
        '       If strEmpID = LCase(objDBSheet.Cells(lngDB, 5).Value) Then
        '               lngFoundRow = lngDB
        '               Exit For
        '       End If
        'Next
        
Set objCellRef = objDBSheet.Columns("E:E").Find(strEmpID, objDBSheet.Cells(1, "E"), xlValues, xlWhole, xlByRows, xlNext, False, False)
        lngReportRow = objReportSheet.Cells(65536, 1).End(xlUp).Row + 1 
        
If Not objCellRef Is Nothing Then
                objDBSheet.Range("A" & objCellRef.Row & ":E" & objCellRef.Row).Copy objReportSheet.Range("A" & lngReportRow)
		
       


ElseIf Left(strEmpID, 2) = "ot" Then
                objReportSheet.Cells(lngReportRow, 1).Value = "NO MATCH"
                objReportSheet.Cells(lngReportRow, 2).Value = "NO MATCH"
                objReportSheet.Cells(lngReportRow, 3).Value = "NO MATCH"
                objReportSheet.Cells(lngReportRow, 4).Value = "NO MATCH"
                objReportSheet.Cells(lngReportRow, 5).Value = strEmpID
                objReportSheet.Cells(lngReportRow, 6).Value = "POSSIBLE MATCH - PLEASE VERIFY"

                objReportSheet.Cells(lngReportRow, 1).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 2).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 3).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 4).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 5).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 6).Interior.ColorIndex = 20



ElseIf Left(strEmpID, 3) = "slf" Then
                objReportSheet.Cells(lngReportRow, 1).Value = "NO MATCH"
                objReportSheet.Cells(lngReportRow, 2).Value = "NO MATCH"
                objReportSheet.Cells(lngReportRow, 3).Value = "NO MATCH"
                objReportSheet.Cells(lngReportRow, 4).Value = "NO MATCH"
                objReportSheet.Cells(lngReportRow, 5).Value = strEmpID
                objReportSheet.Cells(lngReportRow, 6).Value = "POSSIBLE MATCH - PLEASE VERIFY"

                objReportSheet.Cells(lngReportRow, 1).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 2).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 3).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 4).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 5).Interior.ColorIndex = 20
                objReportSheet.Cells(lngReportRow, 6).Interior.ColorIndex = 20

        End If



Next 
objDB.Close False 
objEmp.Close False
objExcel.DisplayAlerts = False 
objReport.SaveAs strReport 
objExcel.DisplayAlerts = True 
MsgBox "Finished."

Open in new window

screenshot2-2-2010.jpg
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, I'm struggling to understand this bit......given the data that you have posted in the question, we're reading from EMP.xls, and looking for an exact match in DB.xls.  So, if EMP.xls had "OTH00000" in it, then the following would be added to the report
dd      AEL    JOE      70200       OTH00000

because OTH00000 exists in DB.xls.

If OTH00000 does NOT exist in DB.xls, then where do you get the address details from?  Are you saying if OTH00000 does not exist, you want to pull the first match that begins with OTH?

That can be done......I'll give it a crack....

Regards,

Rob.
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, I haven't tested this, but give it a shot.  I was just able to use OT* as a find string, so you'll see the bits where that's been used.

Regards,

Rob.
strDB = "C:\Documents and Settings\bsmith9\Desktop\projects\1\side test\DB.xls"  
strEmp = "C:\Documents and Settings\bsmith9\Desktop\projects\1\side test\Emp.xls"  
strReport = "C:\Documents and Settings\bsmith9\Desktop\projects\1\side test\Report.xls"  

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False

Const xlCenter = -4108
Const xlUp = -4162
Const xlValues = -4163
Const xlWhole = 1
Const xlByRows = 1
Const xlNext = 1

Set objDB = objExcel.Workbooks.Open(strDB, False, False)
Set objEmp = objExcel.Workbooks.Open(strEmp, False, False)
Set objReport = objExcel.Workbooks.Add
Set objDBSheet = objDB.Sheets(1)
Set objEmpSheet = objEmp.Sheets(1)

Set objReportSheet = objReport.Sheets(1)

objReportSheet.Cells(1, 1).Value = "First Name"
objReportSheet.Cells(1, 2).Value = "Last Name"
objReportSheet.Cells(1, 3).Value = "Street Address"
objReportSheet.Cells(1, 4).Value = "Zip Code"
objReportSheet.Cells(1, 5).Value = "UPIN #"

objReportSheet.Range("A:A").EntireColumn.ColumnWidth = 15
objReportSheet.Range("B:B").EntireColumn.ColumnWidth = 15
objReportSheet.Range("C:C").EntireColumn.ColumnWidth = 25
objReportSheet.Range("D:D").EntireColumn.ColumnWidth = 10
objReportSheet.Range("E:E").EntireColumn.ColumnWidth = 10

objReportSheet.Rows("1:1").HorizontalAlignment = xlCenter
objReportSheet.Rows("1:1").Interior.ColorIndex = 30
objReportSheet.Rows("1:1").Font.ColorIndex = 2
objReportSheet.Rows("1:1").Font.Size = 12 
objReportSheet.Rows("1:1").Font.Bold = True

' Now read through the EMP sheet
For lngEmp = 2 To objEmpSheet.Cells(65536, 1).End(xlUp).Row
	strEmpID = LCase(objEmpSheet.Cells(lngEmp, 1).Value)
	Set objCellRef = objDBSheet.Columns("E:E").Find(strEmpID, objDBSheet.Cells(1, "E"), xlValues, xlWhole, xlByRows, xlNext, False, False)
	lngReportRow = objReportSheet.Cells(65536, 1).End(xlUp).Row + 1     

	If Not objCellRef Is Nothing Then
		objDBSheet.Range("A" & objCellRef.Row & ":E" & objCellRef.Row).Copy objReportSheet.Range("A" & lngReportRow)

	ElseIf Left(strEmpID, 2) = "ot" Then

		objReportSheet.Cells(lngReportRow, 1).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 2).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 3).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 4).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 5).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 6).Interior.ColorIndex = 20

		Set objCellRef2 = objDBSheet.Columns("E:E").Find("ot*", objDBSheet.Cells(1, "E"), xlValues, xlWhole, xlByRows, xlNext, False, False)

		If Not objCellRef2 Is Nothing Then
			objDBSheet.Range("A" & objCellRef2.Row & ":E" & objCellRef2.Row).Copy objReportSheet.Range("A" & lngReportRow)
			objReportSheet.Cells(lngReportRow, 6).Value = "POSSIBLE MATCH - PLEASE VERIFY"

		Else
			objReportSheet.Cells(lngReportRow, 1).Value = "NO MATCH"
			objReportSheet.Cells(lngReportRow, 2).Value = "NO MATCH"
			objReportSheet.Cells(lngReportRow, 3).Value = "NO MATCH"
			objReportSheet.Cells(lngReportRow, 4).Value = "NO MATCH"
			objReportSheet.Cells(lngReportRow, 5).Value = strEmpID
			objReportSheet.Cells(lngReportRow, 6).Value = "NO MATCH - PLEASE VERIFY"

		End If

	ElseIf Left(strEmpID, 2) = "slf" Then

		objReportSheet.Cells(lngReportRow, 1).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 2).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 3).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 4).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 5).Interior.ColorIndex = 20
		objReportSheet.Cells(lngReportRow, 6).Interior.ColorIndex = 20

		Set objCellRef2 = objDBSheet.Columns("E:E").Find("slf*", objDBSheet.Cells(1, "E"), xlValues, xlWhole, xlByRows, xlNext, False, False)

		If Not objCellRef2 Is Nothing Then
			objDBSheet.Range("A" & objCellRef2.Row & ":E" & objCellRef2.Row).Copy objReportSheet.Range("A" & lngReportRow)
			objReportSheet.Cells(lngReportRow, 6).Value = "POSSIBLE MATCH - PLEASE VERIFY"

		Else
			objReportSheet.Cells(lngReportRow, 1).Value = "NO MATCH"
			objReportSheet.Cells(lngReportRow, 2).Value = "NO MATCH"
			objReportSheet.Cells(lngReportRow, 3).Value = "NO MATCH"
			objReportSheet.Cells(lngReportRow, 4).Value = "NO MATCH"
			objReportSheet.Cells(lngReportRow, 5).Value = strEmpID
			objReportSheet.Cells(lngReportRow, 6).Value = "NO MATCH - PLEASE VERIFY"

		End If

	End If
Next  
objDB.Close False
objEmp.Close False
objExcel.DisplayAlerts = False
objReport.SaveAs strReport
objExcel.DisplayAlerts = True
objReport.Close
objExcel.Quit
MsgBox "Finished."

Open in new window

Jason YousefSr. BI  Developer

Author

Commented:
Rob,

You know what, i'm so stupid, my mistake, I didn't realize that the source report has been changed and I didn't mention it to you, it's different than the one in my example !

I'm really so sorry... the Emplyees List.xls has now different columns
------------------

Name                        UPIN  
---------------------------------
john doee           234234234
admam doe        c234234234
smith white        OTH000
brett smith          SF
john doeee
john doeeee       123456

and every time i'm testing your code, I just delete the name column to run the code and test it !! and expect it to bring the person's name if he has OT or SF or blank in his UPIN number !!!!


to answer your question, No the DB doesn't have OTH000 or any of those, the DB is the external file that has full info

but emp.xls is the one that has employees names in one column and UPIN in one column and we need to verify if they exist in that DB or not.


and if we don't have the accurate UPIN of one of them, we need to get his name in the report as a possible match to verify his information.


can I open another question and give you the URL to answer it and close this one, for the points? as i really feel that i wasted your time because that change in the report !!!

Thanks
please advice
i'm sorry again one more time


Most Valuable Expert 2012
Top Expert 2014

Commented:
Oh I see...LOL!  That could be a problem.....

Don't worry about it, but yeah, it's probably best to open a new question, describe your new data structure, and what you need from it.....post my most relevant piece of code and say it needs changing because the data changed.....and I'll continue there.

I'm glad you let me know though, coz it was pretty confusing!

Regards,

Rob.
Jason YousefSr. BI  Developer

Author

Commented:
the Best.....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial