MAJAEJ
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.