All office applications from Microsoft support automation. They can run as COM servers and be used from document-embedded or external scripts. So, you can write data to an Excel document from a vba script.
Necessary
- - installed Microsoft Excel application;
- - Visual Basic editor / text editor.
Instructions
Step 1
Create a container to host the vba code. If the script is to be embedded in a document, upload the appropriate file to Microsoft Excel. Open the Visual Basic Editor by pressing Alt + F11. If necessary, create a new module (Module item in the Insert menu). Open one of the modules or forms. Create a handler for your form control, or just add a procedure to a module. For example:
Sub test ()
End Sub
If you are developing a stand-alone script (that is, it will run under Windows Script Host), then simply create a file with the vbs extension in a text editor.
Step 2
In the script embedded in the document, add variable declarations at the beginning of the procedure:
Dim oWorkbook As Excel. Workbook
Dim oSheet As Excel. Worksheet
The first is for storing a reference to the Excel workbook object, and the second is for the sheet.
Step 3
Initialize variables with object references. In the vbs script, create an Excel application object (this will launch Excel as a COM server):
Set oApplication = CreateObject ("Excel. Application").
In the script embedded in the document, the global Application object will be used instead of the oApplication variable, which refers to the current application object. Select an existing or open a new Excel workbook. For example:
Set oWorkbook = Application. Workbooks (1)
Set oWorkbook = Application. Workbooks ("Book1")
Set oWorkbook = oApplication. Workbooks. Open ("D: / vic / relevant / tmp / test.xls")
Get a link to the desired sheet of the book:
Set oSheet = oApplication. Sheets ("Sheet1")
Step 4
Write data to Excel from vba script. Use the Cells collection, which is a property of the workbook sheet object referenced in the previous step, into the oSheet variable. An example of writing a string to one cell might look like this:
oSheet. Cells (1, 1) = "The string will be written to cell A1"
Step 5
In the vbs script, add the code to save the data and shut down the Excel application:
oWorkbook. Save
oApplication. Quit
Step 6
Save and execute the script. In the Visual Basic Editor, press Ctrl + S and then position the cursor in the body of the procedure and press F5. Save the vbs script to disk and then run it as a regular file.
