How to Create Xml file from Excel using vbs

Показать описание

Tour : How to Create Xml file from Excel using vbs

Step 1. Open VBScript Editor
Step 2. Program Code
Step 3. Required Variable Declaration
Step 4. Create Scripting FileSystemObject
Step 5. Create Excel Application Object
Step 6. Get the AbsolutePathName()
Step 7. Create the Xml file
Step 8. Write Data into the Xml File
Step 9. Open Excel File
Step 10. Define Sheet object
Step 11. Loop to get all used row data
Step 12. Release created objects
Step 13. Save Created Script and Run
Step 14. Sample Excel file data
Step 15. Congratulation :)

1. Open VBScript Editor
Open the VB Script editor either from desktop VBS shortcut or from start menu.

2. Program Code
Here you see the Program for "How to Create Xml file from Excel using vbs".In next slides we will discuss whole code line by line for better understanding.

3. Required Variable Declaration
Here we Declare the Required variables.We use this variable as global variable and use when we declare required object.

4. Create Scripting FileSystemObject
We create the Scripting FileSystemObject because we will create the Xml file in the system, so for this we need the path and to get path we this object.

5. Create Excel Application Object
Here we create Excel Application object. we need this object to open our excel file for read the data for xml.We set visible property true for this excel object after set we are able to see the excel application windows on our screen when script run.

6. Get the AbsolutePathName()
Here we set the actual path of script into the work_path variable by using the GetAbsolutePathName() method.
This method returns the actual folder path where our script exist in the system.

7. Create the Xml file
We create the required Xml file on the specified path return by above method.To create Xml file we use CreateTextFile(FilePath) method and also assign this file to XmlFile object because we can write Xml data into file using this object.

8. Write Data into the Xml File
We use .Write method to write text into the created Xml file.To use this method we need File object.Here we write the default xml tag.VbNewLine will insert the one new line character in Xml file.

9. Open Excel File

10. Define Sheet object
Using open excel file object we can get whole workbook data.Now here we need first sheet data to create xml, so by using this workbook object we create sheet object which is ws.Now using this sheet object we extract all used rows count and set into the Row_count variable.

11. Loop to get all used row data
Here we loop to extract all used rows data of specified sheet.By using .cells(rowIndex,columIndex).valuemethod we get the specified cell value.To use this method we need Worksheet object.We also write the Xml data one by one into the created xml file using the .write method.

12. Release created objects

13. Save Created Script and Run

14. Sample Excel file data
Here you see the sample Excel file data by which we want to create the required Xml file.We have 5 employee detail and we will create the Xml file for this data using VBscript

15. Congratulation :)
Congratulation ! We have successfully Write our VBscript program "To create the Xml file from Excel data using VBScript".Here you see the created Xml file after run the script.