• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Vb.Net writing data to named cells in Excel

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
charlieb01
Asked:
charlieb01
1 Solution
 
KimputerCommented:
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
 
charlieb01Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now