Solved

Vb.Net writing data to named cells in Excel

Posted on 2014-03-06
2
250 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
ID: 39911303
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
ID: 39911495
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
The viewer will learn how to implement Singleton Design Pattern in Java.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

861 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

26 Experts available now in Live!

Get 1:1 Help Now