Tip

Write to Excel from VB

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.