Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Vb.Net writing data to named cells in Excel

Posted on 2014-03-06
2
Medium Priority
?
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 36

Accepted Solution

by:
Kimputer earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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.
Suggested Courses

618 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