Need help on excel datatable operations

[SushantS]

Hi

I am new to QTP. Need help on excel datatable operations.

I have 4 excel files in a folder. I need to convert all excel files to CSV.

I have written below code, but somehow it wont work. getting error message as "Open method of Workbooks class failed".

Please find below my code:


Option Explicit 
Dim TotRow,i,ExcelObj,Sourcefile,TargetFile

TotRow =  DataTable.LocalSheet.GetRowCount

For i = 1 To TotRow

    Set ExcelObj = CreateObject("Excel.Application")
    ExcelObj.DisplayAlerts = False
    ExcelObj.Visible  = False  
    
''    Sourcefile = Datatable("SourceFile",dtlocalsheet)
''    
''    TargetFile = Datatable("TargetFile",dtlocalsheet)
    
    ExcelObj.Workbooks.Open Datatable("SourceFile",dtlocalsheet)
    ExcelObj.SaveAs Datatable("TargetFile",dtlocalsheet)
    ExcelObj.Application.Quit

Next


Datatable Details:

          Sourcefile                                                [i]TargetFile[/i]

C:\QTP\ExpectedReports\a.xlsx               C:\QTP\ExpectedReports\a.csv
C:\QTP\ExpectedReports\b.xlsx               C:\QTP\ExpectedReports\b.csv
C:\QTP\ExpectedReports\c.xlsx               C:\QTP\ExpectedReports\c.csv
C:\QTP\ExpectedReports\d.xlsx               C:\QTP\ExpectedReports\d.csv


Please Note: If I provide full path of sourcefile and targetfile then code works. but I don't want to enter those each time as there are 1000 of files in a folder which I need to concert to CSV.

Thanks,
Sushant

[vinod123]

Instead of using QTP script use vbscript

Code:

'------------------- SET SEMI-COLON DELIMITER VIA WIN REGISTERS --------------- strDelimiter = ";" strSystemDelimiter = "" ' This will be used to store the current sytem value Const HKEY_CURRENT_USER = &H80000001 ' Get the current List Separator (Regional Settings) from the registry strKeyPath = "Control Panel\International" strValueName = "sList" strComputer = "." Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv") objRegistry.GetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter ' Set it temporarily to our custom delimiter objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strDelimiter '----------------------------- CONVERT XLS TO CSV ------------------------------ Dim strExcelFileName Dim strCSVFileName Dim objFSO Set objFSO = CreateObject("scripting.filesystemobject") strPath = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(1)) strExcelFileName = WScript.Arguments.Item(0) 'file name to parses rem get path where script is running Set fso = CreateObject ("Scripting.FileSystemObject") 'use this to find current path strScript = Wscript.ScriptFullName strScriptPath = fso.GetAbsolutePathName(strScript & "\..") rem If the Input file is NOT qualified with a path, default the current path LPosition = InStrRev(strExcelFileName, "\") if LPosition = 0 Then 'no folder path strExcelFileName = strScriptPath & "\" & strExcelFileName strScriptPath = strScriptPath & "\" else 'there is a folder path, use it for the output folder path also strScriptPath = Mid(strExcelFileName, 1, LPosition) End If rem msgbox LPosition & " - " & strExcelFileName & " - " & strScriptPath ' use this for debugging Dim objXL Dim objWorkBook, local Set objXL = CreateObject("Excel.Application") Set objWorkBook = objXL.Workbooks.Open(strExcelFileName) objXL.DisplayAlerts = False rem loop over worksheets For Each sheet In objWorkBook.Sheets 'only saveAS sheets that are NOT empty if objXL.Application.WorksheetFunction.CountA(sheet.Cells) <> 0 Then rem sheet.Rows(1).delete ' this will remove Row 1 or the header Row local = true call objWorkBook.SaveAs(strPath & "\" & sheet.Name & ".csv", 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, local) End If Next rem clean up objWorkBook.Close objXL.quit Set objXL = Nothing Set objWorkBook = Nothing Set fso = Nothing '------------------------- RETURN REGISTRY CHANGES BACK -------------------- ' Reset the system setting to its original value objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter rem end script