Hi VJR,
Thanks for the reply. here the VBScript which I have to run. but its showing "not declared error- It may be inaccessbile due to protection level". Actually the code have more if condition, i have trimmed here. have to change dynamically - path & sheetnames from the data item.
For the entire code its showing some 101 error, but all are repeated errors only.
VBScript i have run through process - "Utility - start process", but i cant change the dynamic things.
I dont know got to incorporate code in BP, so need some help. VJR, it would be great if you could help. am also working on some workaround still.
2 questions here :
- Can we use VBscript code directly to VB? (i think its not possible through code stage)
- how can i use the same code in BP supporting languages?
Dim VExcelPath
vTodayfile="C:\Desktop\test01.xslx"
vTodayFile =Wscript.Arguments(0)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objworkbook1=objExcel.workbooks.Open(vTodayfile)
set objworkSheet1 = objworkbook1.worksheets("AAA")
set objworkSheet2 = objworkbook1.worksheets("BBB")
For i=2 to objworksheet2.usedrange.Rows.Count
Acellvalue2 = objworksheet2.cells(i,1). value
'Bcellvalue2 = objworksheet2.cells(i,2). value
Bcellvalue2 = objworksheet2.cells(i,2).text
For j=2 to objworksheet1.usedrange.Rows.Count
Acellvalue1 = objworksheet1.cells(j,1). value
Bcellvalue1 = objworksheet1.cells(j,2). value
If Acellvalue1=Acellvalue2 then
Exit For
End If
If Acellvalue2<>Acellvalue1 then
objworksheet1.cells(i,1).value=objworksheet2.cells(i,1). value
End If
Next
If Instr(BCellvalue2,"%")>0 AND BCellvalue2<>"0%" then
BCellvalue2=Replace(Bcellvalue2,"%","")
BCellvalue2=Trim(BCellvalue2)
objworksheet1.cells(i,4).value="SALE"
objworksheet1.cells(i,6).value="0"
objworksheet1.cells(i,7).value=Bcellvalue2 '--50
objworksheet1.cells(i,8).value=(objworksheet1.cells(i,5).value) - (objworksheet1.cells(i,6).value)
objworksheet1.cells(i,9).value=(objworksheet1.cells(i,5).value) / (objworksheet1.cells(i,11).value)
objworksheet1.cells(i,10).value=(objworksheet1.cells(i,8).value) / (objworksheet1.cells(i,11).value)
End If
If Acellvalue1="" and Acellvalue2 ="" then
Exit For
End If
If Acellvalue2=e then
Exit For
End If
Next
objworkbook1.save
objworkbook1.close
objexcel.quit
'Wscript.quit1
If you want here the full code : (same lil code, but more IF conditions)
Dim VExcelPath
vTodayfile="C:\Users\Desktop\test01\Customer.xslx"
vTodayFile =Wscript.Arguments(0)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objworkbook1=objExcel.workbooks.Open(vTodayfile)
set objworkSheet1 = objworkbook1.worksheets("AAA")
set objworkSheet2 = objworkbook1.worksheets("BBB")
For i=2 to objworksheet2.usedrange.Rows.Count
Acellvalue2 = objworksheet2.cells(i,1). value
'Bcellvalue2 = objworksheet2.cells(i,2). value
Bcellvalue2 = objworksheet2.cells(i,2).text
For j=2 to objworksheet1.usedrange.Rows.Count
Acellvalue1 = objworksheet1.cells(j,1). value
Bcellvalue1 = objworksheet1.cells(j,2). value
If Acellvalue1=Acellvalue2 then
Exit For
End If
If Acellvalue2<>Acellvalue1 then
objworksheet1.cells(i,1).value=objworksheet2.cells(i,1). value
End If
Next
If Instr(BCellvalue2,"%")>0 AND BCellvalue2<>"0%" then
BCellvalue2=Replace(Bcellvalue2,"%","")
BCellvalue2=Trim(BCellvalue2)
objworksheet1.cells(i,4).value="SALE"
objworksheet1.cells(i,6).value="0"
objworksheet1.cells(i,7).value=Bcellvalue2 '--50
objworksheet1.cells(i,8).value=(objworksheet1.cells(i,5).value) - (objworksheet1.cells(i,6).value)
objworksheet1.cells(i,9).value=(objworksheet1.cells(i,5).value) / (objworksheet1.cells(i,11).value)
objworksheet1.cells(i,10).value=(objworksheet1.cells(i,8).value) / (objworksheet1.cells(i,11).value)
End If
If Instr(BCellvalue2,"$")>0 AND BCellvalue2 <>"0$"then
BCellvalue2=Replace(Bcellvalue2,"$","")
BCellvalue2=Trim(BCellvalue2)
objworksheet1.cells(i,4).value="SALE"
objworksheet1.cells(i,6).value=Bcellvalue2
objworksheet1.cells(i,7).value="0"
objworksheet1.cells(i,8).value=(objworksheet1.cells(i,5).value) - (objworksheet1.cells(i,7).value)
if (objworksheet1.cells(i,11).value)= "0" then
objworksheet1.cells(i,9).value="0"
objworksheet1.cells(i,10).value="0"
else
objworksheet1.cells(i,9).value=(objworksheet1.cells(i,5).value) / (objworksheet1.cells(i,11).value)
objworksheet1.cells(i,10).value=(objworksheet1.cells(i,8).value) / (objworksheet1.cells(i,11).value)
end if
End If
If (BCellvalue2)="0%" then
objworksheet1.cells(i,6).value="0"
objworksheet1.cells(i,7).value="0"
objworksheet1.cells(i,8).value="0"
objworksheet1.cells(i,9).value=(objworksheet1.cells(i,5).value) / (objworksheet1.cells(i,11).value)
objworksheet1.cells(i,10).value="0"
End If
If (BCellvalue2)="0$" then
objworksheet1.cells(i,6).value="0"
objworksheet1.cells(i,7).value="0"
objworksheet1.cells(i,8).value="0"
objworksheet1.cells(i,9).value=(objworksheet1.cells(i,5).value) / (objworksheet1.cells(i,11).value)'--100
objworksheet1.cells(i,10).value="0"
End If
If (BCellvalue2)=trim("XXX") OR (BCellvalue2)=trim("XXX") then
objworksheet1.cells(i,6).value="0"
objworksheet1.cells(i,7).value="0"
objworksheet1.cells(i,8).value="0"
if (objworksheet1.cells(i,11).value) = "" or (objworksheet1.cells(i,11).value) = "0" then
objworksheet1.cells(i,9).value="0"
else
objworksheet1.cells(i,9).value=(objworksheet1.cells(i,5).value) / (objworksheet1.cells(i,11).value)
end if
objworksheet1.cells(i,10).value="0"
End If
If (BCellvalue2)=trim("YYY") OR (BCellvalue2)=trim("YYY") then
objworksheet1.cells(i,6).value="0"
objworksheet1.cells(i,7).value="0"
objworksheet1.cells(i,8).value="0"
if (objworksheet1.cells(i,11).value) = "" then
objworksheet1.cells(i,9).value="0"
else
objworksheet1.cells(i,9).value=(objworksheet1.cells(i,5).value) / (objworksheet1.cells(i,11).value)
end if
objworksheet1.cells(i,10).value="0"
End If
If (BCellvalue2)="" then
objworksheet1.cells(i,6).value="0"
objworksheet1.cells(i,7).value="0"
objworksheet1.cells(i,8).value="0"
if (objworksheet1.cells(i,11).value)= "0" OR (objworksheet1.cells(i,11).value) = "" then
objworksheet1.cells(i,9).value="0"
ELSE
objworksheet1.cells(i,9).value=(objworksheet1.cells(i,5).value) / (objworksheet1.cells(i,11).value)
END IF
objworksheet1.cells(i,10).value="0"
End If
If Acellvalue1="" and Acellvalue2 ="" then
Exit For
End If
If Acellvalue2=e then
Exit For
End If
Next
objworkbook1.save
objworkbook1.close
objexcel.quit
'Wscript.quit1