Program to copy data from MS excel to a text file at particular location using VBScript

Dim objExcel, objFile, objFSO, objFolder
strFolder = "\\10.6.2.58\Waves\Documents\TeamInformation\Eshwari"
strFile = "\datafromexcel.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(strFolder)
Set objFile = objFSO.CreateTextFile(strFolder & strFile)
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("W:\Documents\TeamInformation\BirthdayInfo.xls")
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
objFile.Write(objExcel.Cells(intRow, 1).Value & " ")
objFile.WriteLine(objExcel.Cells(intRow, 2).Value)
intRow = intRow + 1
Loop
objFile.Close
objExcel.Quit

Copy a list of files specified in an Excel Sheet from one location to another location using VBscript

'This function is used to copy a list of files specified in an Excel Sheet from

'Declaration Section
Dim sExcelFile, sSource , sDestination
sExcelFile = "C:\Documents and Settings\BanduvvE\Desktop\fileslist.xls"
sSource = "D:\MyFiles\Generaldocs\"
sDestination = "D:\myfilecopy\"

'Calling a function which copies files specified in the Excel sheet from source to destination
call CopyFiles(sExcelFile, sSource, sDestination)


'Implementation or body of "copyfiles" function
Function CopyFiles(ByVal sExcelPath, ByVal sSourcePath, ByVal sDestinationPath)

'Declaration of Function Variables
Dim objFSO ' Object type for FileSystemObject
Dim objFolder_ ' Object type for Folder Object
Dim objFileColl ' Object type for FilesCollection Object
Dim objExcel ' Object type for Excel application Object
Dim objWorkBook ' Object type for Excel Workbook Object
Dim sFilePath 'Variable which stores the path of the file to be copied
Dim iExcelRow 'Variable which stores the count of rows in the ExcelSheet
Dim iFileCount 'Variable which stores the count of files that are copied to the destination folder
Dim sFileName 'Variable which stores the path of each file accessed thru FilesCollection Object

'Creating instances for objects which are used in this function
Set objExcel = CreateObject("Excel.Application") 'Create instance for the Excel Application
Set objWorkBook = objExcel.Workbooks.Open(sExcelPath) 'Create instance for the Workbook which was opened thru Excel Application
Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create instance for the FileSystemObject
Set objFolder = objFSO.GetFolder(sSourcePath) 'Create instance for Folder object to access the source folder
Set objFileColl = objFolder.Files 'Create instance for FilesCollection Object to access files retrieved by that object

'Initialization of required variables
iExcelRow = 2
iFileCount = 2

'Main part of the function implementation
While objExcel.Cells(iExcelRow, 1).Value <> "" 'This loop is used to read the data from the Excel Sheet row by row
'Excel Sheet has only filenames and we need the whole path for each file
'So concatenate the source folder path with the filename retrieved from the Excel sheet.
sFilePath = sSourcePath & objExcel.Cells(iExcelRow, 2).Value 'sFilePath has the filename with full path
'This loop reads each file from the FilesList returned by the FilesCollection Object
For Each sFileName in objFileColl 'For Each Loop can be used only for accessing individual objects from a collection
If sFileName = sFilePath Then 'Comparing the filename from the ExcelSheet with the filename from the fileslist
'CopyFile function is used to copy a file from current location to another location
'This function has 3 arguments -- filename to be copied, to which location it has to be copied and should overwrite if that file already exists
objFSO.CopyFile sFileName, sDestinationPath, True
iFileCount = iFileCount + 1 'Incrementing the filecount once that file is copied
End If
Next
iExcelRow = iExcelRow + 1 'Incrementing the Excel sheet row count
Wend 'End of the loop
If iExcelRow = iFileCount then
MsgBox "All Files Copied Successfully into the folder -- " & sDestinationPath
End If

'Closing the Excel Application
objExcel.Quit

'End of the function implementation
End Function

How to get the parent node of the given node in XML using VBscript?

Actually this program i have written with the help of functions in VBscript .... so try to analyse it carefully....

Dim snode_name,xml_file
xml_file = InputBox("Enter xml file")
'snode_name = InputBox("Enter Any Node")
Call xmltoexcel(xml_file)


Function xmltoexcel(xmlfile)
Dim i,j,snode_name
i=2
j=2
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set objWorkbook = xlApp.Workbooks.Add("C:\Documents and Settings\BhavanaA\Desktop\VBscript\Book3.xls")
set xmlDoc=CreateObject("Microsoft.XMLDOM")
xmlDoc.load(xmlfile)
Call displaynode(xmlDoc.documentElement,xlApp,i)
snode_name = InputBox("Enter Any Node")
Call specific_node(xmlDoc.documentElement,xlApp,j,snode_name)

'objWorkbook.close
'xlApp.quit
End Function


Function displaynode(ByVal node_name,ByRef xlApp,Byref i )

xlApp.Range("A" & 1).Value="NODENAME"
xlApp.Range("B" & 1).Value="DATA"
xlApp.Range("C" & 1).Value="PARENTNODE"
xlApp.Range("A" & i).Value = node_name.nodename
xlApp.Range("B" & i).Value = ""
xlApp.Range("C" & i).Value = node_name.parentnode.nodename
For each cnt in node_name.childNodes
If cnt.HasChildNodes Then
i=i+1
Call displaynode(cnt,xlApp,i)
Else
xlApp.Range("B" & i).Value = cnt.text
xlApp.Range("C" & i).Value = cnt.parentnode.parentnode.nodename
End If
Next
End Function

Function specific_node(ByVal node_name,ByRef xlApp,ByRef j,snode_name)
xlApp.Range("H" & j).Value = node_name.nodename
'MsgBox iNode.nodename
If node_name.HasChildNodes Then
For Each sChildNode In node_name.ChildNodes
If sChildNode.HasChildNodes then
j=j+1
Call specific_node(sChildNode,xlApp,j,snode_name)
Else
xlApp.Range("I" & j).Value = sChildNode.parentnode.nodename
xlApp.Range("J" & j).Value = sChildNode.text
End if
Next
End If
If snode_name = node_name.nodename Then
Wscript.echo "My parent is " & node_name.parentnode.nodename
If node_name.HasChildNodes Then
get_nodelist(node_name)
End If
End If
End Function

Function get_nodelist(snode)
For Each strnode In snode.ChildNodes
If strNode.HasChildNodes then
snode_list = snode_list & "--->"& strnode.parentnode.nodename
Call get_nodelist(strnode)

End If
Next
Wscript.echo snode_list
End Function


Any QUES fEEL FREE TO ASK
Here is my email- id : aditya_joy58@yahoo.co.in

Program in VBscript for XML parser

Friendz,

I have written a program which actually parses XML into Excel(in my application)

the output will be like

Nodename Value Parentnode


Dim xml_file
xml_file = InputBox("Enter xml file")
Call xmltoexcel(xml_file)

Function xmltoexcel(xmlfile)
Dim i
i=1
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set objWorkbook = xlApp.Workbooks.Add("C:\Documents and Settings\BhavanaA\Desktop\VBscript\Book3.xls")
set xmlDoc=CreateObject("Microsoft.XMLDOM")
xmlDoc.load(xmlfile)
For j = 1 To 4
xlApp.Worksheets(1).Cells(1, j).Interior.color = 10092543
Next
xlApp.Range("A" & 1).Value="NODENAME"
xlApp.Range("B" & 1).Value="DATA"
xlApp.Range("C" & 1).Value="PARENTNODE"

xlApp.Worksheets(1).Cells.Range("A1:C1").ColumnWidth = 35
Call displaynode(xmlDoc.documentElement,xlApp,i)
objWorkbook.close
xlApp.quit
End Function

Function displaynode(ByVal node_name,ByRef xlApp,Byref i )
i=i+1
xlApp.Range("A" & i).Value = node_name.nodename
xlApp.Range("B" & i).Value = ""
xlApp.Range("C" & i).Value = node_name.parentnode.nodename
j=0
for each cnt in node_name.childNodes
if cnt.HasChildNodes Then
Call displaynode(cnt,xlApp,i)
Else
If (j>0)Or (j=0 And cnt.nodename<>"#text") Then
i=i+1
xlapp.Range("A" & i).value = cnt.nodename
xlApp.Range("C" & i).Value = cnt.parentnode.nodename
End If
xlApp.Range("B" & i).Value = cnt.text
end If
j=j+1
Next
End Function