everseeker Posted October 5, 2011 Posted October 5, 2011 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 createduntil 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" Everseeker
water Posted October 5, 2011 Posted October 5, 2011 (edited) 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 My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Juvigy Posted October 6, 2011 Posted October 6, 2011 Excel 2010 template is .xltx You can saveas files as .XLSX : $oExcel.ActiveWorkbook.SaveAs ( "c:\filename.xlsx",51,"","",False,False) beautifulsoup 1
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now