VB.NET Excel Addin, how do you write data to a specific worksheet by name?

By Visual Basic

So this is the problem I encountered. I am converting an old Excel macro to excel add in so that I can share it with my colleagues more easily. I am VB Newbie to .net, but I’m doing what I can, so please relax me.

I have a Windows form that allows users to enter data, and when they click the enter data button, The data should be transferred from the form to a specific worksheet. The code is as follows:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form_CutListEntry< br />
 Dim xApp As New Excel.Application
 Dim wss As Microsoft.Office.Tools.Excel.Worksheet


 Private Sub Btn_InsertJobInfo_Click(sender As Object, e As EventArgs) Handles Btn_InsertJobInfo.Click
 wss = xApp.Worksheets("Job Info")

'Check that all data is entered
 If Trim(TxtBx_CustomerName.Text) = "" Then
 TxtBx_CustomerName.Focus()
 MsgBox("Please enter a Customer Name")
 Exit Sub
 End If

 If Trim(TxtBx_OrderNum .Text) = "" Then
 TxtBx_OrderNum.Focus()
 MsgBox("Please enter an Order Number")
 Exit Sub
 End If

 If Trim(TxtBx_CutlistAuthor.Text) = "" Then
 TxtBx_CutlistAuthor.Focus()
 MsgBox("Please enter your initials")< br /> Exit Sub
 End If

'Write data to excel worksheet. 
 wss.Cells(3, 1) = "Customer Name: "+ TxtBx_CustomerName.Text
 wss.Cells(4, 1) = "Order Number: "+ TxtBx_OrderNum.Text
 wss.Cells(5, 1) = "Todays Date:" + TxtBx_TodaysDate.Text
 wss.Cells( 6, 1) = "Cutting List Prepared By: "+ TxtBx_CutlistAuthor.Text

 Exit Sub
 End Sub

(Note that I took out the comments and some irrelevant So the detailed error message below contains the wrong line number)

I can open the form from excel, but this happens when I enter some data and click enter data: 

An exception of type'System.Runtime.InteropServices.COMException' occurred in Toms CutList Maker.dll but was not handled in user code
Additional information: Exception from HRESULT: 0x800A03EC

In this line:

wss = xApp .Worksheets("Job Info")

Does anyone have a chance to point out my writing direction?

If anyone is interested, here is the complete error details:

System.Runtime.InteropServices.COMException was unhandled by user code
 ErrorCode=-2146827284
 HResult=-2146827284
 Message=Exception from HRESULT: 0x800A03EC
 Source=Microsoft.Office.Interop.Excel
 StackTrace:
 at Microsoft.Office .Interop.Excel.ApplicationClass.get_Worksheets()
 at Toms_CutList_Maker.Form_CutListEntry.Btn_InsertJobInfo_Click(Object sender, EventArgs e) in d:\tom\documents\visual studio 2013\Projects\Toms CutList Maker\Toms CutList Maker\CutList Entry.vb:line 15
 at System.Windows.Forms.Control.OnClick(EventArgs e)
 at System.Windows.Forms.Button.OnClick(EventArgs e)
 at System.Windows .Forms.Button.OnMouseUp(MouseEventArgs mevent)
 at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
 at System.Windows.Forms.Control.WndProc(Message& m)
 at System.Windows.Forms.ButtonBas e.WndProc(Message& m)
 at System.Windows.Forms.Button.WndProc(Message& m)
 at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
 at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
 at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
 InnerException:
You cannot directly reference worksheets from the Excel application. The worksheet collection is part of the Workbook object. Workbooks Collection is part of Application Object.

Try this:

Dim xApp As New Excel.Application 
Dim wss As Microsoft .Office.Tools.Excel.Worksheet
Dim wb As Microsoft.Office.Tools.Excel.Workbook

....

wb = xApp.Workbooks( "My Workbook.xlsx")'replace with your workbook name and proper file extension
wss = wb.Worksheets("Job Info")

So this is The problem I am having. I am converting an old Excel macro to excel add in so that I can share it with my colleagues more easily. I am new to VB.net, but I am doing what I can , So please relax I.

I have a Windows form that allows users to enter data. When they click the Enter Data button, the data should be transferred from the form to a specific worksheet. The code is as follows:

< p>

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form_CutListEntry

 Dim xApp As New Excel.Application
 Dim wss As Microsoft.Office.Tools.Excel.Worksheet


 Private Sub Btn_InsertJobInfo_Click(sender As Object, e As EventArgs) Handles Btn_InsertJobInfo.Click
 wss = xApp.Worksheets(" Job Info")

'Check that all data is entered
 If Trim(TxtBx_CustomerName.Text) = "" Then
 TxtBx_CustomerName.Focus()
 MsgBox(" Please enter a Customer Name")
 Exit Sub
 End If

 If Trim(TxtBx_OrderNum.Text) = "" Then
 TxtBx_OrderNum.Focus()
 MsgBox("Please enter an Order Number")
 Exit Sub
 End If

 If Trim(TxtBx_CutlistAuthor.Text) = "" Then
 TxtBx_Cutli stAuthor.Focus()
 MsgBox("Please enter your initials")
 Exit Sub
 End If

'Write data to excel worksheet. 
 wss .Cells(3, 1) = "Customer Name: "+ TxtBx_CustomerName.Text
 wss.Cells(4, 1) = "Order Number:" + TxtBx_OrderNum.Text
 wss.Cells(5, 1 ) = "Todays Date: "+ TxtBx_TodaysDate.Text
 wss.Cells(6, 1) = "Cutting List Prepared By:" + TxtBx_CutlistAuthor.Text

 Exit Sub
 End Sub

(Note that I took out the comments and some unrelated extra parts, so the detailed error message below contains the wrong line number)

I can open the form from excel , But this happens when I enter some data and click enter data:

An exception of type'System.Runtime.InteropServices.COMException' occurred in Toms CutList Maker.dll but was not handled in user code
Additional information: Exception from HRESULT: 0x800A03EC

In this line:

wss = xApp .Worksheets("Job Info")

Does anyone have a chance to point out my writing direction?

If anyone is interested, here is the complete error details:

System.Runtime.InteropServices.COMException was unhandled by user code
 ErrorCode=-2146827284
 HResult=-2146827284
 Message=Exception from HRESULT: 0x800A03EC
 Source=Microsoft.Office.Interop.Excel
 StackTrace:
 at Microsoft.Office .Interop.Excel.ApplicationClass.get_Worksheets()
 at Toms_CutList_Maker.Form_CutListEntry.Btn_InsertJobInfo_Click(Object sender, EventArgs e) in d:\tom\documents\visual studio 2013\Projects\Toms CutList Maker\Toms CutList Maker\CutList Entry.vb:line 15
 at System.Windows.Forms.Control.OnClick(EventArgs e)
 at System.Windows.Forms.Button.OnClick(EventArgs e)
 at System.Windows .Forms.Button.OnMouseUp(MouseEventArgs mevent)
 at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
 at System.Windows.Forms.Control.WndProc(Message& m)
 at System.Windows.Forms.ButtonBase.W ndProc(Message& m)
 at System.Windows.Forms.Button.WndProc(Message& m)
 at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
 at System. Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
 at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
 InnerException:

You cannot directly reference worksheets from the Excel application. The worksheet collection is part of the Workbook object. The Workbooks Collection is part of the Application Object.

Try this:

Dim xApp As New Excel.Application 
Dim wss As Microsoft.Office.Tools.Excel.Worksheet
Dim wb As Microsoft.Office.Tools.Excel.Workbook

....

wb = xApp.Workbooks("My Workbook.xlsx")'replace with your workbook name and proper file extension
wss = wb.Worksheets("Job Info")

, , , , , , , , , ,

Leave a Comment

Your email address will not be published.