Solved

Vb.Net writing data to named cells in Excel

Posted on 2014-03-06
2
249 Views
Last Modified: 2014-03-06
I need a lot of help although this is probably simple for most of you.
I have an excel spreadsheet named: template.xlsx
In that sheet I have three cells with defined names: "JAN", FEB", "MAR"
My VB.Net program has 4 textboxes, Text1, Text2, Text3, Filename and a button: btnSave

What I want the program to do when btnSave is clicked is to:
Open the template.xlsx file (without actually launching Excel - my user shouldn't see what's going on in the background)
put the values from Text1, Text2 and Text3 into the named cells "JAN", "FEB", "MAR"
Perform a SaveAs using the text from the textbox named Filename so that the original template.xlsx is not changed and then quit excel

My actual program will be much more complicated but if I hade the code to do this then the rest I think I could figure out. I have searched around and cannot seem to find an example of code that covers this.

Thanks,
Charlie
0
Comment
Question by:charlieb01
2 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
Comment Utility
Very basic code, but I think you can figure out what to change yourself:

Imports Microsoft.Office.Interop


Public Class Form1

    Dim appExcel As Excel.Application

    Dim oWB As Excel.Workbook

    Dim newoWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim oRng As Excel.Range


    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        If appExcel Is Nothing Then
            appExcel = New Excel.Application
        End If

        appExcel.visible = False



    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        oWB = appExcel.Workbooks.Open("D:\Book1.xlsx", True, True)
        oSheet = oWB.Sheets(1)
        oSheet.Range("JAN").Value = TextBox1.Text
        oSheet.Range("FEB").Value = TextBox2.Text
        oSheet.Range("MAR").Value = TextBox3.Text
        newoWB = appExcel.Workbooks.Add
        oSheet.Copy(Before:=newoWB.Sheets(1))
        newoWB.SaveAs("D:\" & TextBox4.Text)

        oRng = Nothing
        oSheet = Nothing
        newoWB.Close()
        newoWB = Nothing
        oWB.Close(False)
        oWB = Nothing
        appExcel.Quit()
        appExcel = Nothing

        Application.Exit()
    End Sub
End Class

Open in new window

0
 

Author Comment

by:charlieb01
Comment Utility
Ok, I took your code sample, made a few modifications and the version below des exactly what I needed. Thanks for your help and sample code.

Imports Microsoft.Office.Interop

Public Class Form1

    Dim appExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim newoWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim oRng As Excel.Range

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If appExcel Is Nothing Then
            appExcel = New Excel.Application
        End If

        appExcel.Visible = False

    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim myPath As String = Application.StartupPath
        Dim mynewfilename As String
        mynewfilename = myPath & "\" & txtNewFileName.Text

        oWB = appExcel.Workbooks.Open(myPath & "\" & "myTemplate.xlsx", True, True)
        oSheet = oWB.Sheets(1)
        oSheet.Range("JAN").Value = txtTextBox1.Text
        oSheet.Range("FEB").Value = txtTextBox2.Text
        oSheet.Range("MAR").Value = txtTextBox3.Text
        oWB.SaveAs(mynewfilename & ".xlsx")

        oRng = Nothing
        oSheet = Nothing
        oWB.Close()
        oWB = Nothing
        appExcel.Quit()
        appExcel = Nothing

    End Sub

    Private Sub txtNewFileName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtNewFileName.TextChanged

        If Len(txtNewFileName.Text) > 0 Then
            btnSave.Enabled = True
        Else
            btnSave.Enabled = False
        End If

    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click

        End

    End Sub
End Class
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now