Πέμπτη 4 Απριλίου 2013

Τρίτη 26 Φεβρουαρίου 2013

VBScript how to write (or open) values to cells in a' Microsoft Excel spreadsheet

' XLWrite.vbs
' VBScript program demonstrating how to write values to cells in a
' Microsoft Excel spreadsheet.
' ----------------------------------------------------------------------
' Original version by Richard L. Mueller http://www.rlmueller.net
' Makes use of object oriented principles of classes and methods' Requires Microsoft Host Cscript 5.*

Class Xlwrite

Private objExcel
Private objSheet

Private Sub Class_Initialize
    ' Bind to Excel object.
    On Error Resume Next
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False
    If (Err.Number <> 0) Then
        On Error GoTo 0
        MsgBox("Excel application not found.")
        Wscript.Quit
    End If
    On Error GoTo 0

    ' Create a new workbook.
    objExcel.Workbooks.Add

    ' Bind to worksheet.
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "nt_Backup_Logger"
   

End Sub

Private  internal_strExcelPath
   
Public Property Let strExcelPath(ByVal res)
    internal_strExcelPath = CStr(res)
End Property

Public Sub AddCell(celli,cellj,value)
    objSheet.Cells(celli, cellj).Value = CStr(value)
End Sub

Private Sub Class_Terminate
    objExcel.ActiveWorkbook.SaveAs internal_strExcelPath
    objExcel.ActiveWorkbook.Close
    ' Quit Excel.
    objExcel.Application.Quit
End Sub

End Class


'demostration
Set test = new Xlwrite
test.strExcelPath = "test.xls"
test.AddCell 1,1, "a"
test.AddCell 1,2, "b"
Set test = nothing
---------------------------------------------------------------------------------------------
' XLWrite.vbs
' VBScript program demonstrating how to open excel file and write values to cells in a 'Microsoft Excel 'spreadsheet.
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\john\Documents\vb-excel\test.xls")

objExcel.Application.Visible = False
objWorkbook.WorkSheets(1).Activate
objWorkbook.WorkSheets(1).Cells(1, 1).Value = "Test value"

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close


objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit