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.

Go to Toparrow-1

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 Toparrow-1

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 Toparrow-1

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 Toparrow-1

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 Toparrow-1

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 Toparrow-1

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 Toparrow-1

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 Toparrow-1

9.Open Excel File

Using Created Excel object we open the excel file from the specified path.

To open file we use 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 Toparrow-1

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 Toparrow-1

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 Toparrow-1

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 Toparrow-1

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 Toparrow-1

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 Toparrow-1

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 Toparrow-1


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s