Solved

Vb.Net writing data to named cells in Excel

Posted on 2014-03-06
2
257 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…

828 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