Link to home
Start Free TrialLog in
Avatar of MAJAEJ
MAJAEJFlag for United States of America

asked on

Compare fields in Excel Workbook on different sheets

I have an excel workbook.  I'm new to working with VBScript and excel workbooks but am trying to learn -- Any good sites to point to commands working with Excel Tabs/worksheets?

Objective:  
1) Read Serial # from Sheet2.Column1 into var strWarranty {look until no more}
2) Read Serial # from Sheet1.Column8 into var strServers
3) Compare strWarranty with strServers - If match WScript "MATCH"
     This will eventually populate Sheet1.Column9-10 with data from Sheet2.Column3-4.
     Loop through Sheet1.column8 until match {or not} is found.

Problem:
  I am getting var strWarranty populated and output.
  I am NOT getting var strServers populated.  
  The inner DO WHILE is not being processed.

Workbook name  = ServerTest.xlsx
Sheet1 = Servers    (Server Inventory)  
Sheet2 = Warranty  {Dump of HP Warranty info)

Sheet1.Column8 = Serial # from Servers Sheet
Sheet2,Column1 = Serial # from Warranty Sheet


On Error Resume Next
 
' Declare Variables and Constants
Dim intCount1 
Dim intCount2 
Dim strWarranty
Dim strServers  
 
strExcelPath = "c:\ServerTest.xlsx"
Set objExcel = CreateObject("Excel.Application")
 
' Open specified spreadsheet and select the first worksheet.
objExcel.WorkBooks.Open strExcelPath
Set objSheet1 = objExcel.ActiveWorkbook.Worksheets(1)
Set objSheet2 = objExcel.ActiveWorkbook.Worksheets(2)
 
objExcel.Visible = True
WScript.Echo "Made it here..."
 
intCount1 = 2
intCount2 = 2
 
Do While objExcel.Sheets("Warranty").Cells(intCount1, 1).Value <> ""
    StrWarranty = objExcel.Sheets("Warranty").Cells(intCount1, 1).Value
	Wscript.echo "Warranty Serical Number: -->" & StrWarranty & "<--"
	Wscript.echo "                 COUNTS: -->" & intCount1 & "<-->" & intCount2 & "<--"
 
	Do While objExcel.Sheets("Servers").Cells(intCount2, 8).Value <> ""
		strServers = objExcel.Sheets("Servers").Cells(intCount2, 8).Value
		Wscript.echo "Server Serical Number: -->" & strServers & "<--" 
 
		If strServers = strWarranty then
			Wscript.echo "MATCH: Server Serical Number: -->" & StrServers & "<-->" & strWarranty & "<--"
		end if
		intCount2 = intCount2 + 1
	Loop
 
	Wscript.Echo "intCount1: " & intCount1
	Wscript.Echo "intCount2: " & intCount2
	Wscript.Echo "<<-------------------------------------------->>"
	intCount1 = intCount1 + 1
Loop

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of purplepomegranite
purplepomegranite
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial