2

I have the following code in a vbscript:

    Set ExcelObject=CreateObject("Excel.Application")
    ExcelObject.visible=False
    ExcelObject.WorkBooks.Add
    ExcelObject.Sheets(1).Cells(1,1).value="1"
    ExcelObject.Sheets(1).Cells(1,2).value="2"
    ExcelObject.Sheets(1).Cells(1,3).value="3"
    ExcelObject.Sheets(1).Cells(1,4).value="4"
    ExcelObject.Sheets(1).Cells(2,1).value="5"
    ExcelObject.Sheets(1).Cells(2,2).value="6"
    ExcelObject.Sheets(1).Cells(2,3).value="7" 
    ExcelObject.Sheets(1).Cells(2,4).value="Y"

    For x=0 to testData.Count-1
        ExcelObject.Sheets(1).Cells(x+3,1).value=testData(x)
        ExcelObject.Sheets(1).Cells(x+3,2).value="Constant"
        ExcelObject.Sheets(1).Cells(x+3,4).value="Y"
    Next

    ExcelObject.Activeworkbook.Sheets(1).Name = "Name"
    ExcelObject.Activeworkbook.SaveAs(path+"Data.xls")
    ExcelObject.Quit()
    Set ExcelObject=Nothing

Although, once I open this spreadsheet with Excel I get the following error: The file format and extension of "Data.xls" don't match. The file could be correupted or unsafe...

Also, I cannot import this datasheet from UFT HP. I believe it might be because of this issue with how Excel file is being generated.

What can I do to fix it?

JoaoFilipeClementeMartins
  • 1,770
  • 5
  • 28
  • 61
  • 1
    save it as an xlsx. –  Aug 17 '17 at 17:45
  • I specifically need it to be xls as QTP ( now HP-UFT) requires it to be xls – JoaoFilipeClementeMartins Aug 17 '17 at 17:46
  • 1
    then use the filetype parameter of the saveas command. Hint: don't include the .xls extension. Excel will put the correct one there if you get the code right. –  Aug 17 '17 at 17:47
  • I'm not sure how complex the file to save is. You may need to add `ExcelObject.DisplayAlerts = False` before saving to avoid confirmations. –  Aug 17 '17 at 18:00

1 Answers1

2

You need to SaveAs with FileFormat of xlExcel8 . xlExcel8 is the equivalent of 56 (since VB-Script doesn't have the xlExcel8 reference).

ExcelObject.Activeworkbook.SaveAs(path+"Data", 56)

Don't add the extension. Excel will add the correct one according to the file type parameter.

  • I'm not sure how complex the file to save is. You may need to add `ExcelObject.DisplayAlerts = False` before saving to avoid confirmations. –  Aug 17 '17 at 17:59