About Chris Expertise I can answer pretty much any question relating to VB.NET and its use in a Windows environment. I specialize in ASP.NET web development and MSSQL database access.
Experience I have over 5 years of industry experience using VB.NET and other .NET technologies for web and database development.
Education/Credentials I have some college education, but does it really matter in this field of work?
Expert: Chris Date: 9/26/2007 Subject: Append data to excel
Question Hi,
I'm using vb.net and would like to know how to append data to an excel spreadsheet. I have a form with data and it writes one record to excel. The next time I run the process I would like to have the data on the form appended to the excel file. Again it's just one record. Each time the application runs it produces just one row of data that needs to be appended to the excel file. Can you provide me with some code to do this. Thanks.
Answer Not knowing what versions you're using, I'm going to guess here and assume you're using Microsoft Office 2003, Visual Studio 2005 and the .Net Framework v2. First, you'll need to get the Office 2003 Primary Interop Assemblies from this URL: http://support.microsoft.com/kb/897646
Download that, extract it, and run the MSI file that's extracted. Then, you'll need to add a reference to them to your project, so open that up, right-click the main project, and pick Add Reference. Click on the COM tab and scroll down to select and add Microsoft Excel 11.0 Object Library.
In your code file, you'll then need to import the Microsoft.Office.Interop and the System.Runtime.InteropServices namespaces:
Before interacting with Excel, you'll need to make sure you've configured Threading to use the same culture info as your OS language, which I'll assume is English:
Then, create a new instance of Excel to work with, and specify that it is to run invisible (otherwise it would actually open Excel, and you'd be able to see the changes being made):
Dim ObjExcel As New Excel.Application()
ObjExcel.Visible = False
Then, open your XLS file and select the appropriate Worksheet to append your data to:
ObjExcel.Workbooks.Open("c:\test.xls")
Dim ObjWS As Excel.Worksheet = ObjExcel.Worksheets("Sheet1")
Now, to find out which row is the last one that has data in it, we can check the value of objWS.UsedRange.End(Excel.XlDirection.xlDown).Row, and add our data to the next row, at the first column:
Dim iNextRow As Integer = ObjWS.UsedRange.End(Excel.XlDirection.xlDown).Row + 1
ObjWS.Cells(iNextRow, 1) = "new row data, first column"
ObjWS.Cells(iNextRow, 2) = "new row data, second column"
Then we'll grab a reference to the Active Workbook, and tell it to save back to its original file:
Dim objW As Excel.Workbook = ObjExcel.ActiveWorkbook
objW.Save()
And tell the instance of Excel that we opened to close itself:
ObjExcel.Quit()
Finally, some cleanup code to make sure all the resources allocated by the COM interop are released, and a call to the .Net Framework's garbage collector to cleanup any used memory:
