Chimaera Posted January 20, 2014 Author Share Posted January 20, 2014 512 test - Msg = 4 255 test - Msg = 0 and same result as the pic above If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 20, 2014 Share Posted January 20, 2014 So it seems _Excel_SheetAdd has the same limitation as _Excel_BookNew. I need to change the script to add new worksheets in chunks of 255. Will post tomorrow. Now it's time for bed ... My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 20, 2014 Author Share Posted January 20, 2014 Well we learnt something new If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 21, 2014 Share Posted January 21, 2014 Think I've got it Plus I enhanced performance by about 50% (when processing 2 files). expandcollapse popup#AutoIt3Wrapper_AU3Check_Stop_OnWarning=N #AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 #include <Excel Rewrite.au3> #include <Constants.au3> #include <Array.au3> #include <File.au3> #include <MsgBoxConstants.au3> Global $sDrive = "", $sDir = "", $sFilename = "", $sExtension = "" Global $iSheets = 0, $oWorkbookNew, $oWorkbook1, $iArray, $iSheet ; Create application object Global $oExcel = _Excel_Open(Default, Default, False) If @error <> 0 Then Exit MsgBox($MB_ICONERROR, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Global $aCsvFilesToConvert = _FileListToArrayRec(@ScriptDir, "*.csv", 1, Default, Default, 2) Global $aNumbersToConvert[$aCsvFilesToConvert[0] + 1] = [$aCsvFilesToConvert[0]] If @error Or Not IsArray($aCsvFilesToConvert) Then MsgBox($MB_ICONERROR, "Array Error", "No Files Available To Process", 2) Exit EndIf ; Get the highest filenumber. This is needed because the last file in the array doesn't have the highest number. 100.csv and 4.csv => 4.csv as the last For $i = 1 To $aCsvFilesToConvert[0] _PathSplit($aCsvFilesToConvert[$i], $sDrive, $sDir, $sFilename, $sExtension) $aNumbersToConvert[$i] = Int($sFilename) If $aNumbersToConvert[$i] > $iSheets Then $iSheets = $aNumbersToConvert[$i] Next ; Create a new workbook with the number of sheets calculated above $oWorkbookNew = _Excel_BookNew($oExcel, 1) If @error Then Exit MsgBox($MB_ICONERROR, "Error", "BookNew: Error " & @error) ; Create the number of worksheets in chunks of 255 (Excel limitation) $i = $iSheets - 1 While $i > 255 _Excel_SheetAdd($oWorkbookNew, 1, False, 255) $i = $i - 255 WEnd If $i > 0 Then _Excel_SheetAdd($oWorkbookNew, 1, False, $i) ; Set the name of all sheets to "Blank 00n" For $i = 1 To $iSheets $oWorkbookNew.Sheets($i).Name = "Blank " & StringRight("00" & $i, 3) Next For $iArray = 1 To $aCsvFilesToConvert[0] ; Open the CSV file in a new workbook $oWorkbook1 = _Excel_BookOpen($oExcel, $aCsvFilesToConvert[$iArray], True) If @error Then Exit MsgBox($MB_ICONERROR, "Error", "File: " & $aCsvFilesToConvert[$iArray] & ", BookOpen: Error " & @error) ; Get the filename of the CSV file = number of the worksheet to write the data _PathSplit($aCsvFilesToConvert[$iArray], $sDrive, $sDir, $sFilename, $sExtension) $iSheet = Int($sFilename) ; Copy the sheet from the source workbook to the target workbook _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.Sheets($iSheet).Range("A1")) If @error Then Exit MsgBox($MB_ICONERROR, "Error", "File: " & $aCsvFilesToConvert[$iArray] & ", CopyPaste: Error " & @error) ; Set the name of the worksheet $oWorkbookNew.Sheets($iSheet).Name = "Page " & StringRight("00" & $iSheet, 3) ; Close the source workbook (CSV file) _Excel_BookClose($oWorkbook1, False) If @error Then Exit MsgBox($MB_ICONERROR, "Error", "File: " & $aCsvFilesToConvert[$iArray] & ", BookClose: Error " & @error) Next $oExcel.ScreenUpdating = True Exit My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 21, 2014 Author Share Posted January 21, 2014 (edited) Ok that was a bit strange That version makes an excel window with no tabs it flashes between 2 windows whilst showing weird graphics in the box that normally displays the cursor box address see image below It completes the 611 pages but takes about twice the time the last one did So it works just a bit weird Edited January 21, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 21, 2014 Share Posted January 21, 2014 Change Global $oExcel = _Excel_Open(Default, Default, False) to Global $oExcel = _Excel_Open(Default, Default, True) and remove line $oExcel.ScreenUpdating = True The display will flicker again but I hope the weird display is gone. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 21, 2014 Author Share Posted January 21, 2014 Thats a bit less weird lol Yep that appears to work fine ill add some gui and progress and other bits to it and do a load of testing Many thanks If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 21, 2014 Share Posted January 21, 2014 Great! So all issues are solved now? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 21, 2014 Author Share Posted January 21, 2014 (edited) Im curious about one thing when using excelbooksave as this part $iType - Optional: Excel writable filetype. Can be any value of the XlFileFormat enumeration (default = $xlWorkbookNormal) after some googling "xlExcel12" would appear to make it save as xlsx but it doesnt work or am i doing it wrong _Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx", "xlExcel12", False) Edited January 21, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 21, 2014 Share Posted January 21, 2014 You have to specify one of the XlFileFormat enumeration (taken from ExcelConstants.au3 - $xlExcel12 in this case). But if you let this parameter default then Excel saves the file in it's default format - which is xlsx for Excel 2007 or later. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 21, 2014 Author Share Posted January 21, 2014 Ok i tried this _Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx", "50", False) as 50 seems to be the number for the excel 12 version but the only way the file saves is as an xls like this _Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xls", Default, False) never mind ill go back to reading If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 21, 2014 Share Posted January 21, 2014 Excel 12 format is xlsx. Set the filename to xxxx.xlsx and specify $xlExcel12 as $IType. If you want to save the file as xls then set filename to xxx.xls and $iType to one of the older Excel versions e.g. $xlExcel8. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 21, 2014 Author Share Posted January 21, 2014 (edited) yeah i get that thats what i did in this example _Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx", "xlExcel12", False) it just doesnt save it, thats why i mentioned it aaah wait i see the mistake brb hmm didnt work _Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx", $xlExcel12, False) Edited January 21, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 21, 2014 Share Posted January 21, 2014 What's the value of @error after calling the function? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 21, 2014 Author Share Posted January 21, 2014 (edited) It gives _ExcelBookSaveAs @error = 5 | @extended = -2147352567 Edited January 21, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 21, 2014 Share Posted January 21, 2014 Please add this lines at the top of your script so we get better error information: #include <Debug.au3> _DebugSetup() _DebugCOMError() My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 21, 2014 Author Share Posted January 21, 2014 @@ DEBUG COM Error encountered in Excel Test2.au3 (89) : Number = 0x80020006 (-2147352570) WinDescription = Unknown name. Description = Source = HelpFile = HelpContext = LastDllError = 0 Retcode = 0x00000000 @@ DEBUG COM Error encountered in Excel Test2.au3 (468) : Number = 0x80020009 (-2147352567) WinDescription = Exception occurred. Description = This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type. Source = Microsoft Excel HelpFile = xlmain11.chm HelpContext = 0 LastDllError = 0 Retcode = 0x800A03EC >>>>>> Please close the "Report Log Window" to exit <<<<<<< If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 21, 2014 Share Posted January 21, 2014 Strange. Need to test tomorrow ... My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted January 21, 2014 Share Posted January 21, 2014 If you try _Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx") does this work? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 21, 2014 Author Share Posted January 21, 2014 (edited) It creates the file but. Edited January 21, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
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