CODEWITHSUNDEEP
excelvb.netexcel-interop
user7390329
user7390329

Reputation: 23

Writing a value to an Excel cell

I'm aiming to create a simple ribbon with a button that set cell (A1) value to "Hello World!" in the active work sheet.

I have this code but it returns an error and I have no idea what I'm doing wrong.

Screenshot of error:

enter image description here

Code:

Imports Microsoft.Office.Tools.Ribbon
Imports Microsoft.Office.Interop.Excel

Public Class Ribbon1

    Private Sub Ribbon1_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet

        xlBook = xlApp.ActiveWorkbook
        xlSheet = xlBook.ActiveSheet

        With xlSheet
            .Range("A1").Value = "Hello World!"
        End With
    End Sub

End Class

Upvotes: 0

Views: 7693

Answers (1)

Bugs
Bugs

Reputation: 4489

You need to add a new Workbook before you can do anything with it:

xlApp.Workbooks.Add()

I would also consider declaring a Range to use:

Dim xlRange As Excel.Range = xlSheet.Range("A1")
xlRange.Value = "Hello World!"

Your code would look something similar to this:

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add()
Dim xlSheet As Excel.Worksheet = CType(xlBook.ActiveSheet, Excel.Worksheet)

Dim xlRange As Excel.Range = xlSheet.Range("A1")
xlRange.Value = "Hello World!"

Also please turn Option Strict On:

Restricts implicit data type conversions to only widening conversions, disallows late binding, and disallows implicit typing that results in an Object type.

Upvotes: 2

Related Questions