asked on

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

ASKER CERTIFIED SOLUTION

Kimputer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

charlieb01

ASKER

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