i creating web application replace current excel macro application. excel macro has several forms , modules in it. when call macro function in c# using xlapp.run method, function getting called throwing error when function calls other sub functions , uses global public variables.
errors : 1. compile error : sub or function not defined - @ "set svd = m_queries(getsheetrootname(sheet1))" 2. runtime error 424 : object required - @ "compareworksheetstocombined"
code :
public function compareexcels(filepath1 string, filepath2 string) call msgbox("done comparing sheets1.", vbinformation, "compare complete") on error goto comperr call msgbox("done comparing sheets2.", vbinformation, "compare complete") dim compbook workbook dim book1 workbook, book2 workbook dim sheet, sheet1 worksheet, sheet2 worksheet dim svd scriptviewdescription, obj dim l long dim sheetname string dim integer '// check make sure have valid options selected compare if validateoptions = false exit function cmdcompare.enabled = true '// create comparison output workbook set compbook = opencompareoutput() '// workbooks we're comparing set book1 = workbooks(filepath1) set book2 = workbooks(filepath2) '// verify if summary sheet exists , compare libraries. '//if (worksheetexists("[" & filepath1 & "]summary")) , (worksheetexists("[" & filepath2 & "]summary")) set sheet1 = book1.sheets("summary") set sheet2 = book2.sheets("summary") '//if sheet1.cells(2, 3) = sheet2.cells(2, 3) '// = msgbox("attention: comparing data same environment", vbcritical, "critical warning") '//end if '//else '//a = msgbox("summary tab not exist in either workbook or b , libraries not compared", vbcritical, "critical warning") '//end if '// selected sheets , compare them l = 0 lstsheets.listcount - 1 if lstsheets.selected(l) = true '// reset vars make sure don't accidentally re-use last loop set sheet1 = nothing set sheet2 = nothing set svd = nothing '// attempt load vars new stuff sheetname = lstsheets.list(l) set sheet1 = book1.sheets(sheetname) set sheet2 = book2.sheets(sheetname) set svd = m_queries(getsheetrootname(sheet1)) '// check vars , if we're compare if not (sheet1 nothing) _ , not (sheet2 nothing) _ , not (svd nothing) call compareworksheetstocombined(sheet1, sheet2, compbook, svd, txtprefixa.text, txtprefixb.text) end if end if next l cmdcompare.enabled = true call msgbox("done comparing sheets.", vbinformation, "compare complete") exit function comperr: call msgbox("error while attempting process compares." & vbcrlf & vbcrlf & "error " & err.number & ": " & err.description, vbcritical, "error during compare") cmdcompare.enabled = true end function please out.
let's have excel macro looks this
sub showmsg(msg string, title string) msgbox msg, vbinformation, title end sub here c# code pass arguments macro , call it. (tried , tested vs2010 + excel 2010)
using system; using system.collections.generic; using system.componentmodel; using system.data; using system.drawing; using system.linq; using system.text; using system.windows.forms; using excel = microsoft.office.interop.excel; namespace windowsformsapplication2 { public partial class form1 : form { public form1() { initializecomponent(); } private void button1_click(object sender, eventargs e) { //~~> define excel objects excel.application xlapp = new excel.application(); excel.workbook xlworkbook; //~~> start excel , open workbook. xlworkbook = xlapp.workbooks.open("e:\\users\\siddharth rout\\desktop\\book1.xlsm"); //~~> run macros supplying necessary arguments xlapp.run("showmsg", "hello c# client", "demo run excel macros c#"); //~~> clean-up: close workbook xlworkbook.close(false); //~~> quit excel application xlapp.quit(); //~~> clean releaseobject(xlapp); releaseobject(xlworkbook); } //~~> release objects private void releaseobject(object obj) { try { system.runtime.interopservices.marshal.releasecomobject(obj); obj = null; } catch (exception ex) { obj = null; } { gc.collect(); } } } } 
Comments
Post a Comment