Jump to content
Xenobiologist

_ExcelBookOpen 2007 xlsm file (print area gets lost)

Recommended Posts

Xenobiologist

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!

Mega

Test.zip

Edited 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

Share this post


Link to post
Share on other sites
Xenobiologist

I know, the thread is not even 24 hours open, but it is a little bit urgend. :graduated:

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

Share this post


Link to post
Share on other sites
bogQ

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 by bogQ

TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
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.

 

Share this post


Link to post
Share on other sites
Xenobiologist

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 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

Share this post


Link to post
Share on other sites
bogQ

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 by bogQ

TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
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.

 

Share this post


Link to post
Share on other sites
Xenobiologist

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

Share this post


Link to post
Share on other sites
Juvigy

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

Share this post


Link to post
Share on other sites
Xenobiologist

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 ... :graduated: 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

Share this post


Link to post
Share on other sites
Juvigy

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.

Share this post


Link to post
Share on other sites
Xenobiologist

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

Share this post


Link to post
Share on other sites
robertocm

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:

#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)

         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

 

Edited by robertocm

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×