.net - Object not set to the instance of an Object ERROR Excel 2013 Automation VB.NET -
trying create small report program in vb.net. works in excel 2003 , 2010, throws error in excel 2013 when trying create second worksheet. procedure listed below, point out line errors. doesn't catch in try , catch added comes across "unhandled exception".
the target framework 3.5
have tried x86, x64, , cpu target cpu
i used 2003 , 2010 .dlls reference.
imports microsoft.office.interop.excel private sub drpts() try dim strfiledirname string = "" me.cursor = cursors.waitcursor 'morning report sheet 1 dim mornrpt string = "select datecreated, reportclass, sum(qtyordered), sum(extendedprice) " & _ "from tmp_dailyreport group datecreated, reportclass" dim oexcel object dim obook object dim osheet1 object dim osheet2 object oexcel = createobject("excel.application") obook = oexcel.workbooks.add osheet1 = obook.worksheets("sheet1") osheet1.name = "morning report" 'load headers osheet1.range("a1").value = "class" osheet1.range("b1").value = "units" osheet1.range("c1").value = "extended price" 'formatting() osheet1.range("a1:c1").font.bold = true dim cellloc string = "" dim cellcntr integer = 2 dim srptdate string = me.rptstart.text dim erptdate string = me.rptend.text dim reader1 odbc.odbcdatareader using connection new odbc.odbcconnection("dsn=#########;") dim command new odbc.odbccommand(mornrpt, connection) command.connection.open() reader1 = command.executereader while reader1.read() cellloc = "a" & cellcntr osheet1.range(cellloc).value = reader1.getvalue(1).tostring cellloc = "b" & cellcntr osheet1.range(cellloc).value = reader1.getvalue(2).tostring cellloc = "c" & cellcntr osheet1.range(cellloc).value = reader1.getvalue(3).tostring cellcntr = cellcntr + 1 end while end using dim formatrange string = "c2:c" & cellcntr osheet1.range(formatrange).numberformat = "#,##0.00" osheet1.columns.entirecolumn.autofit() osheet1.columns("a").horizontalalignment = microsoft.office.interop.excel.xlhalign.xlhalignleft ------> osheet2 = obook.worksheets("sheet2") *** bad index error occurs here *** <------------------ ------> ' tried variation --> *** osheet2 = obook.worksheets("sheet2") *** neither work osheet2.name = "summary" dim overseassales string = "select sum(qtyordered) units, sum(extendedprice) extprice " & _ "from dbo.tmp_dailyreport " & _ "where (customerno between '0' , '9)' or " & _ "customerno between '0' , '9' or " & _ "customerno = '######')" & _ "group datecreated " 'load(headers) osheet2.range("a1").value = "report class" osheet2.range("b1").value = "product" osheet2.range("c1").value = "units" osheet2.range("d1").value = "extendedprice" this error receive.
system.nullreferenceexception: object reference not set instance of object. @ goldenmanagement.dailyreport.drpts() @ goldenmanagement.dailyreport.rundailyrpt2_click(object sender, eventargs e) @ system.windows.forms.control.onclick(eventargs e) @ system.windows.forms.button.onmouseup(mouseeventargs mevent) @ system.windows.forms.control.wmmouseup(message& m, mousebuttons button, int32 clicks) @ system.windows.forms.control.wndproc(message& m) @ system.windows.forms.buttonbase.wndproc(message& m) @ system.windows.forms.button.wndproc(message& m) @ system.windows.forms.control.controlnativewindow.wndproc(message& m) @ system.windows.forms.nativewindow.callback(intptr hwnd, int32 msg, intptr wparam, intptr lparam)
i forgot posted this. found solution sometime ago.
where error occurring should have used;
osheet2 = ctype(me.application.worksheets.add(), excel.worksheet) osheet2.name = "sheet2" the newer versions of office default 1 sheet in book unless specified.
Comments
Post a Comment