How to convert XLM file to Excel xls or csv format

  • Document ID:KM00817420
  • Creation Date:25-Mar-2014
  • Modified Date:25-Mar-2014
    • Micro Focus Products:
      quicktest professional
      functional testing

Summary

QuickTest Professional (QTP) or Unifid Functional Testing GUI (UFT) products do not have built in functionality to convert between file formats. Vbscript and Excel automation allow to write a script using SaveAs excel automation method to achieve this functionality

Question

If there any function or method in QTP/UFT to convert XLM file to Excel xls or csv format?

Answer

Although QTP/UFT do not provide built-in function or method to convert between file formats using Vbscript and Excel automation allow to write a script using SaveAs excel automation method to achieve this functionality.

Example vbscript code to convert XML to Excel XLS format:

Dim xlApp, xlWkb, SourceFolder,TargetFolder,file
Set xlApp = CreateObject("excel.application")
Set fs = CreateObject("Scripting.FileSystemObject")
Const xlNormal=1
SourceFolder="c:\xml-to-xls\xml"
TargetFolder="c:\xml-to-xls\xls"
xlApp.Visible = false

for each file in fs.GetFolder(SourceFolder).files
  Set xlWkb = xlApp.Workbooks.Open(file)
  BaseName= fs.getbasename(file)
  FullTargetPath=TargetFolder & "\" & BaseName & ".xls"
  xlWkb.SaveAs FullTargetPath, xlNormal
  xlWkb.close
next
'Uncomment this line to delete the source file
'fs.DeleteFile("C:\xml-to-xls\xml\*.xml")
Set xlWkb = Nothing
Set xlApp = Nothing
Set fs = Nothing
Example vbscript code to convert XML to Excel CSV format:
Dim xlApp, xlWkb, SourceFolder,TargetFolder,file
Set xlApp = CreateObject("excel.application")

Set fs = CreateObject("Scripting.FileSystemObject")
Const xlNormal=1
Const xlCSV=6
SourceFolder="c:\xml-to-xls\xml"
TargetFolder="c:\xml-to-xls\xls"
xlApp.Visible = false
for each file in fs.GetFolder(SourceFolder).files
  Set xlWkb = xlApp.Workbooks.Open(file)
  BaseName= fs.getbasename(file)
  FullTargetPath=TargetFolder & "\" & BaseName & ".csv"
  xlWkb.SaveAs FullTargetPath, xlCSV, , , , , , 2
  xlWkb.Saved = True
  xlWkb.close
 
  'Uncomment this line to delete the source file
  'file.Delete
next
Set xlWkb = Nothing
Set xlApp = Nothing
Set fs = Nothing

Related articles:

Note:

This solution applies to external Excel files, not to the QuickTest Professional data table. Excel's object methods are not part of QuickTest Professional or Unified Functional Testing, therefore, they are not guaranteed to work and are not supported by HP Technical Support. Any changes that Microsoft may make to these methods are not the responsibility of HP. User may need to modify the code to suit specific needs.