VB.Net Excel Addin，如何按名称将数据写入特定工作表？ [英] VB.Net Excel Addin, how to write data to specific worksheet by name?

问题描述

所以这就是我遇到的问题。我正在将一个旧的Excel宏转换为excel add in，这样我就可以更轻松地与我的同事分享。我是VB.net的新手，但我正在做我能做的事情，所以请放轻松我。



我有一个允许用户输入数据的Windows表单当他们点击输入数据按钮时，数据应该从表单转到特定的工作表。代码如下：

So here's the problem I have. I am converting an old Excel macro into an excel add in so I can share it easier with my coworkers. I am new to VB.net but I'm doing what I can so please go easy on me.

I have a Windows form that allows a user to enter data and when they hit the enter data button the data is supposed to go form the form to a specific worksheet. The code is as follows:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form_CutListEntry
    'Local Dims for this Class. 
    Dim xApp As Excel.Application = GetObject(, "Excel.Application")
    Dim wb As Microsoft.Office.Tools.Excel.Workbook = xApp.ActiveWorkbook
    Dim wss As Microsoft.Office.Tools.Excel.Worksheet



    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub


    Private Sub Btn_InsertJobInfo_Click(sender As Object, e As EventArgs) Handles Btn_InsertJobInfo.Click

        wss = wb.Worksheets("Job Info")

        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")
            Exit Sub
        End If

'input data into spreadsheet
        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

当我尝试运行它时，会出现以下错误：

This gives me the following error when I try and run it:

System.NullReferenceException was unhandled by user code
  HResult=-2147467261
  Message=Object reference not set to an instance of an object.
  Source=Toms CutList Maker
  StackTrace:
       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 18
       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.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: 

错误就行了：

That error is on the line :

wss = wb.Worksheets("Job Info")

我尝试将线路更改为：

I have tried changing the line to:

wss = wb.ActiveSheet

但是弹出同样的错误。任何想法或链接到文章或任何东西将不胜感激。

But the same error pops up. Any ideas or links to articles or anything would be appreciated.

推荐答案

我不知道VB.Net，但对于VBA，你需要使用设置将对象保存在变量中

I don't know for VB.Net, but for VBA, you need to use set to save an object in a variable

set wss = wb.Worksheets("Job Info")

set wss = wb.ActiveSheet

你真的需要翻译旧的宏吗？

您是否尝试通过选择文件类型将excel工作簿保存为.xla文件？



否则，在类中定义 wss ，但在使用之前没有实例化。

Do you really need to translate the old macro ?
Did you try to save you excel workbook as an .xla file by choosing the file type ?

Otherwise, you define wss in a class, but you did not instantiate before using it.

这篇关于VB.Net Excel Addin，如何按名称将数据写入特定工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！