Vb Script - Excel

M

Malligai

Member
Hi Team,

Am using VB Script in the code stage. It showing some errors.

Can anyone please tell what dll i have to add when performing excel operations.

(I have added Microsoft.Office.Interop.Excel dll in initialize page, but still showing compile error in initialize page"

Please help.
 
V

VJR

Well-Known Member
Malligai said:
Hi Team,

Am using VB Script in the code stage. It showing some errors.

Can anyone please tell what dll i have to add when performing excel operations.

(I have added Microsoft.Office.Interop.Excel dll in initialize page, but still showing compile error in initialize page"

Please help.
Click to expand...
Hi Malligai,

It depends on what functions you are using in the code. Check if the text Microsoft.VisualBasic is added in the Namespace section. If not add it and run the code. If it still throws an error or if it is already added then you need to post the exact error that you think is due to the missing references.
 
M

Malligai

Member
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 :
  1. Can we use VBscript code directly to VB? (i think its not possible through code stage)
  2. 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
 
V

VJR

Well-Known Member
Malligai said:
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 :
  1. Can we use VBscript code directly to VB? (i think its not possible through code stage)
  2. 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
Click to expand...
What is it that you are trying to achieve using the vbscript code? May be there is a way in Blue Prism itself, or a code or part of it existing on the forum, or else might require sticking to the vbscript approach. But for that provide complete details of what you are trying to do.
 
You must log in or register to reply here.
Top