On Excel 2010 (XLSx File type)

[everseeker]

I have a large number of QTP 11 scripts with AutoIT3 GUI front ends that report out to Excel tables I create "on-the-fly"

Our company recently upgraded to Office 2010... Turns out, this is a BAD thing... for me.

Now, If I save a .xls file with the following:

$sOutFilePath = $TestPath & "Output\Output" & StringRight(@YEAR & @MON & @MDAY, 6) & ".xls"
;See if that exists. If so, great... use it. If not, make a new file
$oExcel = _ExcelBookOpen($sOutFilePath, 0)
If @error = 1 Then
MsgBox(0, "Error!", "Unable to Create the Excel Object")
Exit
ElseIf @error = 2 Then
$oExcel = _ExcelBookNew(0)
;add new header
_ExcelWriteCell($oExcel, "Date&Time", 2, 1)
$oExcel.Activesheet.Columns(1).ColumnWidth = 15
_ExcelWriteCell($oExcel, "Script", 2, 2)
$oExcel.Activesheet.Columns(2).ColumnWidth = 55
_ExcelWriteCell($oExcel, "Iteration", 2, 3)
$oExcel.Activesheet.Columns(3).ColumnWidth = 9
_ExcelWriteCell($oExcel, "Step", 2, 4)
$oExcel.Activesheet.Columns(4).ColumnWidth = 5
_ExcelWriteCell($oExcel, "Pass/Fail", 2, 5)
$oExcel.Activesheet.Columns(5).ColumnWidth = 9
_ExcelWriteCell($oExcel, "Notes", 2, 6)
$oExcel.Activesheet.Columns(6).ColumnWidth = 85
$oExcel.ActiveWorkbook.Sheets(1).Range("A2:F2" ).Interior.Color = 0xE29F69
;Save the new book, so it is ready to use
_ExcelBookSaveAs($oExcel, $TestPath & "Output\Output" & StringRight(@YEAR & @MON & @MDAY, 6), "xls")
EndIf
_ExcelBookClose($oExcel, 1)

all LOOKS fine... The file is created

until the file is opened by QTP, where I get a compatibility popup... which hangs QTP

According to THEM, I can solve this by providing .xlsx files to QTP, However, looks like all AutoIT3 can handle is:

Excel writable filetype string = "xls|csv|txt|template|html", default "xls"

My questions are:

1. Is there a way to work with .XLSx files in Autoit3?

2. What is the "Template" filetype ? (Excel writable filetype string = "xls|csv|txt|template|html", default "xls"

[water]

Question 1: I have modified the _ExcelBookSaveAs function (and renamed it to _ExcelBookSaveAsEX) to support XML.

Check this example:

expandcollapse popup

#include <Excel.au3>
Local $oExcel = _ExcelBookNew() ;Create new book, make it visible
_ExcelWriteCell($oExcel, "I Wrote to This Cell", 1, 1) ;Write to the Cell
_ExcelBookSaveAs($oExcel, @TempDir & "\Temp1.xlsx", "xls", 0, 1) ; Now we save it into the temp
_ExcelBookSaveAsEX($oExcel, @TempDir & "\Temp2.xlsx", "xml", 0, 1) ; Now we save it into the temp
_ExcelBookClose($oExcel) ; And finally we close out
 
; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelBookSaveAsEX
; Description ...: Saves the active workbook of the specified Excel object with a new filename and/or type.
; Syntax.........: _ExcelBookSaveAs($oExcel, $sFilePath[, $sType = "xls"[, $fAlerts = 0[, $fOverWrite = 0[, $sPassword = ""[, $sWritePassword = ""[, $iAccessMode = 1[, $iConflictResolution = 2]]]]]]])
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                 $sFilePath - Path and filename of the file to be read
;                 $sType - Excel writable filetype string = "xml|xls|csv|txt|template|html", default "xls"
;                 $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable)
;                 $fOverWrite - Flag for overwriting the file, if it already exists (0=no, 1=yes)
;                 $sPassword - The string password to protect the sheet with; if blank, no password will be used (default = blank)
;                 $sWritePassword - The string write-access password to protect the sheet with; if blank, no password will be used (default = blank)
;                 $iAccessMode - The document sharing mode to assign to the workbook:
;                 $xlNoChange - Leaves the sharing mode as it is (default) (numeric value = 1)
;                 $xlExclusive - Disables sharing on the workbook (numeric value = 3)
;                 $xlShared - Enable sharing on the workbook (numeric value = 2)
;                 $iConflictResolution - For shared documents, how to resolve sharing conflicts:
;                 $xlUserResolution - Pop up a dialog box asking the user how to resolve (numeric value = 1)
;                 $xlLocalSessionChanges - The local user's changes are always accepted (default) (numeric value = 2)
;                 $xlOtherSessionChanges - The local user's changes are always rejected (numeric value = 3)
; Return values .: Success    - Returns 1
;                 Failure  - Returns 0 and sets @error on errors:
;                 |@error=1  - Specified object does not exist
;                 |@error=2  - Invalid filetype string
;                 |@error=3 - File exists, overwrite flag not set
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike
; Remarks .......: You can only SaveAs back to the same working path the workbook was originally opened from at this time
;                 (not applicable to newly created, unsaved books).
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelBookSaveAsEX($oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0, $sPassword = "", $sWritePassword = "", $iAccessMode = 1, _
  $iConflictResolution = 2)
Local Const $xlXMLSpreadsheet = 46
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If $sType = "xml" Or $sType = "xls" Or $sType = "csv" Or $sType = "txt" Or $sType = "template" Or $sType = "html" Then
  If $sType = "xml" Then $sType = $xlXMLSpreadsheet
  If $sType = "xls" Then $sType = $xlNormal
  If $sType = "csv" Then $sType = $xlCSVMSDOS
  If $sType = "txt" Then $sType = $xlTextWindows
  If $sType = "template" Then $sType = $xlTemplate
  If $sType = "html" Then $sType = $xlHtml
Else
  Return SetError(2, 0, 0)
EndIf
If $fAlerts > 1 Then $fAlerts = 1
If $fAlerts < 0 Then $fAlerts = 0
$oExcel.Application.DisplayAlerts = $fAlerts
$oExcel.Application.ScreenUpdating = $fAlerts
If FileExists($sFilePath) Then
  If Not $fOverWrite Then Return SetError(3, 0, 0)
  FileDelete($sFilePath)
EndIf
If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution)
If $sPassword <> "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, $sPassword, Default, Default, Default, $iAccessMode, $iConflictResolution)
If $sPassword <> "" And $sWritePassword <> "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, $sPassword, $sWritePassword, Default, Default, $iAccessMode, $iConflictResolution)
If $sPassword = "" And $sWritePassword <> "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, Default, $sWritePassword, Default, Default, $iAccessMode, $iConflictResolution)
If Not $fAlerts Then
  $oExcel.Application.DisplayAlerts = 1
  $oExcel.Application.ScreenUpdating = 1
EndIf
Return 1
EndFunc   ;==>_ExcelBookSaveAsEX

Edited October 5, 2011 by water

[Juvigy]

Excel 2010 template is .xltx

You can saveas files as .XLSX :

$oExcel.ActiveWorkbook.SaveAs ( "c:\filename.xlsx",51,"","",False,False)