Write to Excel from VB 09 Jan 2001 Rating: -4.43- (out of 5)Write to Excel from VB Tom WiggintonWriting 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
09 Jan 2001
Rating: -4.43- (out of 5)Write to Excel from VB
Tom WiggintonWriting 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