Xenobiologist Posted October 11, 2011 Share Posted October 11, 2011 (edited) Hi @all, I'm working on a little Autoit script which opens a .xlsm file and does something with it. I recently recognized that opening the file via Autoit (Excel UDF) has bad impact on the print area. The predefined print area gets lost. Anybody an idea how to avoid that? #include <Excel.au3> $sFilePath1 = @ScriptDir & "\test.xlsm" ;This file should already exist $oExcel = _ExcelBookOpen($sFilePath1) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist - Shame on you!") Exit EndIf If you open the test.xlsm with the script the print area is set to a1:a3 If you open the test.xlsm with the script via shellexecute or whatsoever, the print area is a1:e13 There is no marco in this test file! MegaTest.zip Edited October 11, 2011 by Xenobiologist Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
Xenobiologist Posted October 12, 2011 Author Share Posted October 12, 2011 I know, the thread is not even 24 hours open, but it is a little bit urgend. Somebody who can at least confirm the probelm with Excel.au3 opening a xlsm file with a predefined print area? Thanks! Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
bogQ Posted October 12, 2011 Share Posted October 12, 2011 (edited) 2007 and 2003 is a1:e13 with autoit or with double click on file, they gave identical mark results in print area after print preview. Edited October 12, 2011 by bogQ TCP server and client - Learning about TCP servers and clients connectionAu3 oIrrlicht - Irrlicht projectAu3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related) There are those that believe that the perfect heist lies in the preparation.Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost. Link to comment Share on other sites More sharing options...
Xenobiologist Posted October 12, 2011 Author Share Posted October 12, 2011 (edited) You mean, you opened the file with _ExcelBookOpen and the print area was the same as when you opened it via double click or shellexecute? The problem is the function from Excel.au3 (creating the excel obj) not Shellexecute or something else. Edited October 12, 2011 by Xenobiologist Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
bogQ Posted October 12, 2011 Share Posted October 12, 2011 (edited) yes i opened it with your script that you posed, and after that i opened it via double click, i got identical results on 2007 and after that i tested it on 2003 with identical results. Edited October 12, 2011 by bogQ TCP server and client - Learning about TCP servers and clients connectionAu3 oIrrlicht - Irrlicht projectAu3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related) There are those that believe that the perfect heist lies in the preparation.Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost. Link to comment Share on other sites More sharing options...
Xenobiologist Posted October 12, 2011 Author Share Posted October 12, 2011 That's weird! I have to co-workers testing the issue. Both of them and of course me too have the problem. Maybe it belongs to the German Excel version. I don't know. I'm using 12.0.6565.5003 SP2 of Excel 2007 on a WIN XP SP3 32 bit machine . Thanks for testing! Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
Juvigy Posted October 12, 2011 Share Posted October 12, 2011 Maybe it is related to excel options . Try setting the print area from Autoit : $oExcel.Range("A1:E20").Select $oExcel.Application.Activesheet.Pagesetup.PrintArea = $oExcel.Selection.Address Link to comment Share on other sites More sharing options...
Xenobiologist Posted October 12, 2011 Author Share Posted October 12, 2011 Hm, what option do you mean? Setting the print area is no option more me, because the script is part of a deployment process of IBM Cognos reports (Go!Office). That means there are tons of reports to deploy. I don't know the print areas of them and I don't want to hard-code them. If I open the file via Excel UDF I cannot grab the prtint area, because it is has already been changed by ... whatever. (Besides, I have a solution without Excel.au3 yet, but it is not very nice looking. (Too much Sends and so on) However, it seems to work, but it would be better with Excel UDF. Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
Juvigy Posted October 12, 2011 Share Posted October 12, 2011 Well it can be cell R1C1 references , security settings or other settings that remove the print area. If you can test - reset to default settings and try again. Link to comment Share on other sites More sharing options...
Xenobiologist Posted October 12, 2011 Author Share Posted October 12, 2011 Okay, I'll give it a try. Besides, I have asked in the German autoit.de-forum as well. Confirmation, that the print area changes via Excel.au3. Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
robertocm Posted December 10, 2018 Share Posted December 10, 2018 (edited) I've found this: https://answers.microsoft.com/en-us/msoffice/forum/all/printarea-is-lost-when-opening-a-excell-document/0881a8c7-e11e-4ce2-9824-6af0ef62f665 Just to report the same problem in spanish excel 2007 I think that have some relation with the name of the range and translation this is what i'm seeing: - before access with the com: in names manager window (ctrl+F3) all print areas are in spanish ('Área_de_impresión') - after: names manager shows all print area names as 'Print_Area' I'm trying this: If Not IsObj($oWorkbook.Sheets("Hoja1").Evaluate("Área_de_impresión")) Then ;If Not IsObj($oWorkbook.Sheets("Hoja1").Range("Área_de_impresión")) Then $oWorkbook.Sheets("Hoja1").Names.Add("Área_de_impresión", "=" & $oWorkbook.Sheets("Hoja1").Range("Print_Area").Address) $oWorkbook.Sheets("Hoja1").Names.Add("Print_Area", "") ;$oWorkbook.Sheets("Hoja1").Names("Print_Area").Delete EndIf I'm using this to restore lost print areas in all excel files in a directory and subdirectories: expandcollapse popup#include <File.au3> ;#include <Array.au3> #include <WinAPIFiles.au3> #include <Excel.au3> $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. Global $bFileOpen Global Const $sMessage = "Select Folder" Global $sFileSelectFolder = FileSelectFolder($sMessage, "") If @error Then MsgBox(0, "", "No folder was selected.") Exit EndIf ;Look for excel files in selected directory and all subdirectories Global $aFileList = _FileListToArrayRec($sFileSelectFolder, "*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then Local $oAppl = _Excel_Open(Default, Default, False, Default, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;_ArrayDisplay($aFileList, "", "", $ARRAYDISPLAY_NOROW, "", "Files") For $i = 1 To $aFileList[0] $bFileOpen = _WinAPI_FileInUse($aFileList[$i]) If $bFileOpen = 0 Then Local $oWorkbook = _Excel_BookOpen($oAppl, $aFileList[$i]) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;only for testing printareas lost ;$oWorkbook.Saved = False ;Restore print areas Global $Name ='' For $nm In $oWorkbook.Names $Name = $nm.Name If StringInStr($Name, "Print_Area") > 0 Then $oWorkbook.Sheets(StringSplit($Name, "!")[1]).Names.Add("Área_de_impresión", $nm.RefersTo) $nm.Delete EndIf Next ;~ ;Restore print areas: another option, but slower ;~ ;Check if range name exists ;~ Global $ShName = '', $Sheets = $oWorkbook.Sheets ;~ For $ws In $Sheets ;~ $ShName = $ws.Name ;~ If Not IsObj($oWorkbook.Sheets($ShName).Evaluate("Área_de_impresión")) Then ;~ ;If Not IsObj($oWorkbook.Sheets($ShName).Range("Área_de_impresión")) Then ;~ If IsObj($oWorkbook.Sheets($ShName).Evaluate("Print_Area")) Then ;~ $oWorkbook.Sheets($ShName).Names.Add("Área_de_impresión", "=" & $oWorkbook.Sheets($ShName).Range("Print_Area").Address) ;~ $oWorkbook.Sheets($ShName).Names.Add("Print_Area", "") ;~ ;$oWorkbook.Sheets($ShName).Names("Print_Area").Delete ;~ EndIf ;~ EndIf ;~ Next _Excel_BookClose($oWorkbook, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf _Excel_Close($oAppl) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;This is a custom error handler Func ErrFunc() $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs EndFunc ;==>ErrFunc Another option (not tested) could be to open excel file using ShellExecute (not the com udf) and once open connect to active workbook with _Excel_BookAttach function. I'm not testing, but should work because excel opens the file with the language configuration. Edited December 17, 2018 by robertocm 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