Tour : How to Create Xml file from Excel using vbs
1.Open VBScript Editor
Open the VB Script editor either from desktop VBS shortcut or from start menu.
Go to Top
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.
Go to Top
3.Required Variable Declaration
Here we Declare the Required variables.
We use this variable as global variable and use when we declare required object.
Go to Top
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.
Go to Top
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.

Go to Top
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.

Go to Top
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.

Go to Top
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.

Go to Top
9.Open Excel File
Using Created Excel object we open the excel file from the specified path.
To open file we use .workbooks.open(filepath) method with the excel object.
also assign opened file to wb object. we use this object to get data from the opened excel file

Go to Top
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.

Go to Top
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).value
method 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.

Go to Top
12.Release created objects
After completion of Xml file creation we need to close the opened excel file and also release the created objects.
Now here we use .close method to close the opened excel file.
and use the .Quit method to quit the excel object.

Go to Top
13.Save Created Script and Run
Now Save this created script file in the system where your excel file exist.we will show you the excel file data in next step.
here you see the one excel file "Employe.xlsx" and we want to convert this excel file data into the xml file.

Go to Top
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

Go to Top
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.

Go to Top
