Write to Excel from VB

This tip shows how to write to Microsoft Excel from Visual Basic.

Write to Excel from VB
Tom Wigginton

Writing to an Excel spreadsheet shouldn't be tough, but it can be. But reader Tom Wigginton offers this technique to make it easy.

------------------------------------------------------------

Here is a way you can read and write to an Excel spreadsheet using the Excel Application Object. GetExcel gets the contents of a cell in an Excel spreadsheet, while SetExcel writes to a cell in an Excel spreadsheet. If you are going to do a lot of reading and/or writing to an Excel spreadsheet, instead of instantiating and opening the Excel.Application and Excel.Workbook objects, then saving the changes, closing and destroying them in the functions, you can move that code outside of these functions. Create the Excel.Application and Excel.Workbook objects before getting or setting cell values, and save and close the Workbook and quit the Application object after getting or setting cell values.

Option Explicit 
'************************************************************* 
' You need to set a reference to the Microsoft Excel Object 
' Library to use these Excel Objects. 
'************************************************************* 
Private xlApp As Excel.Application  ' Excel Application Object 
Private xlBook As Excel.Workbook    ' Excel Workbook Object 

'************************************************************* 
' Gets the contents of an Excel Worksheet's cell. 
' 
' xlWorksheet: Name of a worksheet in an Excel File, for example, 
'              "Sheet1" 
' xlCellName:  Name of a Cell (Row and Column), for example, 
'              "A1" or "B222". 
' xlFileName:  Name of an Excel File, for example, "C:TestTesting.xls" 
'************************************************************* 
Private Function GetExcel(xlFileName As String, _ 
                          xlWorksheet As String, _ 
                          xlCellName As String) As String 
                          
   On Error GoTo GetExcel_Err 
   
   Dim strCellContents As String 
   ' Create the Excel App Object 
   Set xlApp = CreateObject("Excel.Application") 
   ' Create the Excel Workbook Object. 
   Set xlBook = xlApp.Workbooks.Open(xlFileName) 
   
   ' Get the Cell Contents 
   strCellContents =     xlBook.worksheets(xlWorksheet).range(xlCellName).Value
   
   ' Close the spreadsheet 
   xlBook.Close savechanges:=False 
   xlApp.Quit 
   Set xlApp = Nothing 
   Set xlBook = Nothing 
   
   GetExcel = strCellContents 
   
   Exit Function 
GetExcel_Err: 
   MsgBox "GetExcel Error: " & Err.Number & "-" & Err.Description 
   Resume Next 
End Function 

'************************************************************* 
' Sets the contents of an Excel Worksheet's cell. 
' 
' xlWorksheet: Name of a worksheet in an Excel File, for example, 
'              "Sheet1" 
' xlCellName:  Name of a Cell (Row and Column), for example, 
'              "A1" or "B222". 
' xlFileName:  Name of an Excel File, for example, "C:TestTesting.xls" 
' xlCellContents:  What you want to place into the Cell. 
'************************************************************* 
Private Sub SetExcel(xlFileName As String, _ 
                     xlWorksheet As String, _ 
                     xlCellName As String, _ 
                     xlCellContents As String) 
                          
   On Error GoTo SetExcel_Err 
   
   ' Create the Excel App Object 
   Set xlApp = CreateObject("Excel.Application") 
   
   ' Create the Excel Workbook Object. 
   Set xlBook = xlApp.Workbooks.Open(xlFileName) 
   
   ' Set the value of the Cell 
   xlBook.worksheets(xlWorksheet).range(xlCellName).Value = xlCellContents 
   
   ' Save changes and close the spreadsheet 
   xlBook.Save 
   xlBook.Close savechanges:=False 
   xlApp.Quit 
   Set xlApp = Nothing 
   Set xlBook = Nothing 
   Exit Sub 
SetExcel_Err: 
   MsgBox "SetExcel Error: " & Err.Number & "-" & Err.Description 
   Resume Next 
End Sub

------------------------------------------------------------

Thanks, Tom. For your interest in SearchVB, we'll be sending you a SearchVB denim shirt.

Tom Wigginton is a programmer/analyst with Alfa Insurance in Montgomery, Alabama, and a Microsoft Certified Professional.


This was first published in January 2001

Dig deeper on VB 6 to VB .NET Migration

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close