How to Write Data in Excel Sheet Using VB.NET

Sending data from a custom application to an Excel spreadsheet is a common requirement for creating reports, exporting data, or automating an otherwise manual process. If you're working with VB.NET, you have a direct and powerful way to control Excel, allowing you to write data, apply formatting, and even generate charts. This guide will walk you through exactly how to write data to an Excel sheet using VB.NET, covering the essential setup and providing practical, step-by-step code examples.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Setting Up Your VB.NET Project to Work with Excel

Before you can write a single line of code, you need to tell your VB.NET project how to communicate with Excel. This is done by adding a special reference to your project called the Excel Object Library, which lets you programmatically control almost every aspect of the Excel application.

This process is known as COM Interop. Think of it as giving your VB.NET application a direct phone line to the Excel application on your computer. Here’s how to set it up:

1. Create a New Project: Open Visual Studio and create a new VB.NET project. A "Windows Forms App (.NET Framework)" or "WPF App" is a great choice for this.
2. Add the COM Reference:

That's it! Your project now knows how to talk to Excel. You’ll also need to add an Imports statement at the very top of your code file to make interacting with the library easier.

Imports Microsoft.Office.Interop

Method 1: Writing Data Using COM Interop

Using the COM Interop library you just referenced is the most direct way to control Excel. It’s perfect when you need to not only write data but also apply custom formatting, create charts, or manipulate the spreadsheet in complex ways.

Remember that this method requires Microsoft Excel to be installed on the machine running the code.

Step 1: Create the Excel Objects

To start, you need to create instances of the Excel Application itself, a Workbook (the file), and a Worksheet (the tab within the file).

' Create a new instance of Excel
Dim excelApp As New Excel.Application()

' Add a new workbook
Dim workbook As Excel.Workbook = excelApp.Workbooks.Add()

' Get the active worksheet
Dim worksheet As Excel.Worksheet = workbook.ActiveSheet

For debugging, it can be useful to make the Excel window visible while your code is running. Just add this line:

excelApp.Visible = True ' Set to False for background processing

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Write to a Single Cell

The simplest action is writing a value to a single cell. You can reference cells by their row and column number using the .Cells property. Rows and columns in Excel Interop are 1-based, not 0-based.

' Write text to cell A1 (Row 1, Column 1)
worksheet.Cells(1, 1).Value = "Product Name"

' Write a number to cell B1 (Row 1, Column 2)
worksheet.Cells(1, 2).Value = "Sales"

Step 3: Write Data Using a Loop

A more realistic scenario is writing a series of records, perhaps from a list or an array. You can do this by looping through your data and writing it out one cell at a time. This method is intuitive and easy to understand.

Imagine you have two simple lists of product data:

' Sample data
Dim products() As String = {"Laptop", "Mouse", "Keyboard"}
Dim sales() As Integer = {150, 420, 275}

' Write headers first
worksheet.Cells(1, 1).Value = "Product"
worksheet.Cells(1, 2).Value = "Sales Figures"

' Loop through the data and write it to the sheet
For i As Integer = 0 To products.Length - 1
    ' We start at row 2 because row 1 has headers
    worksheet.Cells(i + 2, 1).Value = products(i)
    worksheet.Cells(i + 2, 2).Value = sales(i)
Next

While looping works, it can be slow if you have thousands of rows because each .Value assignment is a separate communication call to Excel. For larger datasets, writing an entire array at once is much faster.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: A Faster Way - Write a 2D Array to a Range

The most efficient way to write a block of data is to put it into a two-dimensional array and write the entire array to an Excel range in a single operation. This dramatically reduces the communication overhead between your app and Excel.

' Prepare a 2D array with your data (4 rows, 2 columns)
Dim data(3, 1) As Object
data(0, 0) = "Product"
data(0, 1) = "Sales"
data(1, 0) = "Monitor"
data(1, 1) = 95
data(2, 0) = "Webcam"
data(2, 1) = 310
data(3, 0) = "Docking Station"
data(3, 1) = 120

' Define the Excel range to write to (A1 to B4)
Dim writeRange As Excel.Range = worksheet.Range("A1", "B4")

' Write the entire array to the range in one go
writeRange.Value = data

This approach is the gold standard for performance when dealing with tabular data.

Step 5: Apply Some Basic Formatting

Your report will look much better with a little formatting. You can control fonts, colors, and layout directly from your code.

' Make the headers (A1:B1) bold
Dim headerRange As Excel.Range = worksheet.Range("A1", "B1")
headerRange.Font.Bold = True

' Change the background color of the headers
headerRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray)

' Auto-fit the columns to content width
worksheet.Columns("A:B").AutoFit()

Step 6: Save the File and Clean Up

This is the most critical step. After your work is done, you must save the workbook and, more importantly, properly close and release all the Excel objects. If you don't, you can leave "ghost" processes of Excel running in the background, consuming system memory.

' --- Best practice: Use a Try...Finally block for cleanup ---
Try
    ' ... YOUR EXCEL WRITING CODE GOES HERE ...
    
    ' Save the workbook
    ' NOTE: Disabling alerts prevents prompts like "Do you want to overwrite?"
    excelApp.DisplayAlerts = False
    workbook.SaveAs("C:\Users\YourUser\Documents\SalesReport.xlsx")
Catch ex As Exception
    ' Handle any errors
    MsgBox("An error occurred: " & ex.Message)
Finally
    ' --- CLEANUP ---
    ' Always ensure Excel closes and objects are released
    If workbook IsNot Nothing Then
        workbook.Close(SaveChanges:=False) ' Close without saving changes again
        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
    End If

    If excelApp IsNot Nothing Then
        excelApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
    End If
    
    ' Set objects to Nothing to release them from memory
    worksheet = Nothing
    workbook = Nothing
    excelApp = Nothing
End Try

The Marshal.ReleaseComObject method tells your program to explicitly let go of the COM object, ensuring a clean shutdown of the Excel process.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Alternative Method: Using Open XML SDK

For certain situations, especially server-side applications where Excel isn't installed, the COM Interop method is not viable. In these cases, you can use libraries that write .xlsx files directly without needing Excel.

One common choice is the Open XML SDK from Microsoft. It's more complex to learn because you're essentially building the XML structure of the spreadsheet yourself, but it’s extremely powerful and fast. A simpler approach is to use popular third-party libraries like EPPlus (for .NET Framework projects) or ClosedXML, which provide an easier interface on top of Open XML.

These libraries are great when your primary goal is to quickly dump data into a file without needing intricate formatting or charting capabilities.

Final Thoughts

You've now learned how to programmatically control Excel from a VB.NET application, write data to cells and ranges, and apply basic formatting. Mastering this skill is incredibly useful for automating report generation and bridging the gap between your custom applications and the familiar world of spreadsheets.

This kind of custom automation is powerful, but it often leads down a path of maintaining complex code for every new reporting need. That's a big reason we built Graphed. Our goal was to eliminate the need for custom scripts and manual CSV wrangling entirely. Instead of coding connections to specific files, you can connect your live data sources like Google Analytics, Shopify, and Salesforce. From there, you just use plain English to build the dashboards you need or ask questions about your performance, getting answers back in seconds - no programming required.