Remove figure from the open workbook Excel

[LazyVasily]

Hello.

Who knows - as a script to remove the figure "Oval 1" from the open workbook of Excel 1.xls ?

[JLogan3o13]

@LazyVasily you are simply going to have to provide more information if you want help, our crystal ball is in the shop. How about a screenshot of the figure you are trying to remove, or better yet, an example of the spreadsheet itself.

[JoHanatCent]

Try

$oExcel = _Excel_Open()
$o_Excel = _Excel_BookOpen($oExcel, @DesktopDir & "\Book 1.xls")
$o_Excel.ActiveSheet.Shapes.Range(("Oval 1")).Delete
_Excel_BookClose($o_Excel)

 

[LazyVasily]

JoHanatCent, This script constantly reopens the xls file. I already have an xls file open-no need to open it again. Because part of the contents of the Excel-disappears if the file is not saved. How to remove the shape "Oval 1" in an already open Excel file, without reopening ?

[AutoBert]

Use https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_BookAttach.htm

 

[LazyVasily]

AutoBert,  This code doesn't work.

#include <Excel.au3>
$oWorkBook = _Excel_BookAttach(@ScriptDir & "\1.xls")
$oExcel = _Excel_Open()
$oWorkBook.ActiveSheet.Shapes.Range(("Oval 1")).Delete

It works only in rare exceptions - if you close all Excel files and run the desired file 1.xls by clicking on it.

In other cases, this code does not work. Gives an error "Variable must be of type Object".

How to delete the shape "Oval 1"regardless of how the file 1.xls was open-click or through the Excel program ?

[water]

#include <Excel.au3>
#include <MsgBoxConstants.au3>
$oExcel = _Excel_Open()
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error creating Excel object! @error = " & @error & ", @extended = " & @extended)
$oWorkBook = _Excel_BookAttach(@ScriptDir & "\1.xls")
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error attaching to the workbook! @error = " & @error & ", @extended = " & @extended)
$oWorkBook.ActiveSheet.Shapes.Range("Oval 1").Delete
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error deleting shape! @error = " & @error & ", @extended = " & @extended)

You first have to call _Excel_Open to create the Excel object!
I have added error checking so you know what went wrong.

[LazyVasily]

water,  File 1.xls is open at the time of running your script.

An error window appears :

[water]

Did you have a look at th help file? The error code is explained as: Workbook not found.
Does the Excel file exist in the script directrory? Is the name correct? is the extension correct?

[LazyVasily]

water, You don't quite understand what I'm asking.

Your script works.

But it works only in one case. It only works if the file " 1.xls"  - will be opened by clicking on the file.

If you open the file 1.xls in another way, the script does not work.

After all, the file can be opened by hyperlink or simply through the Excel menu. The file thus opened is " 1.xls " - the script does not recognize and displays an error window.

I asked - how to remove the shape "Oval 1" if the file 1.xls was opened not by clicking on this file, but in another way ?

The file can be opened via the Excel menu (and now AutoIt-script does not see it).

[Nine]

 

1 hour ago, LazyVasily said:

You don't quite understand what I'm asking.

 I don't think asking (looks like more of an order to me) for us to give you the solution all chewed up will service you.

So try a few things, bring back the different codes you have tested.

[water]

As long as the workbook 1.xls is opened from @ScriptDir and is open when the AutoIt script is started it doesn't matter how you open it.
_Excel_BookAttach compares the specified filepath which should match in this cases.

If it still doesn't work, try other $sMode options.

[LazyVasily]

water, Advise - as replace "$Mode FileName" on "$sMode Title" in your script (Title of the Excel window) ?

In Help no found example for "$sMode Title" (https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_BookAttach.htm)

Edited December 25, 2018 by LazyVasily

[water]

My advice is: Read the help file That's what it is made for
BTW: Not for every possible value of a parameter you'll find an example in the help file. But the examples give you an idea how it works.

[LazyVasily]

water,  But the help is missing an example for "$sMode Title" :

https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_BookAttach.htm

[water]

That's exactly what I told you in my last post.
Seems you need to show some effort yourself

[Nine]

@LazyVasily You do really want to respect your nick ? 

[LazyVasily]

water,  Independently created a script similar to the example in the Help. The script does not work. Shows the error "Error attaching to the workbook". 

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Opt('WinTitleMatchMode', -2)

$oExcel = _Excel_Open()
$sTitle = "1.xls"
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error creating Excel object! @error = " & @error & ", @extended = " & @extended)
$oWorkBook = _Excel_BookAttach($sTitle)
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error attaching to the workbook! @error = " & @error & ", @extended = " & @extended)
$oWorkBook.ActiveSheet.Shapes.Range("Oval 1").Delete
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error deleting shape! @error = " & @error & ", @extended = " & @extended)

 

#include <Excel.au3>
#include <MsgBoxConstants.au3>

;Opt('WinTitleMatchMode', -2)
Local $oWorkBook = "1.xls"

$oExcel = _Excel_Open()

If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error creating Excel object! @error = " & @error & ", @extended = " & @extended)
$oWorkBook = _Excel_BookAttach($sWorkbook, "title")
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error attaching to the workbook! @error = " & @error & ", @extended = " & @extended)
$oWorkBook.ActiveSheet.Shapes.Range("Oval 1").Delete
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error deleting shape! @error = " & @error & ", @extended = " & @extended)

What have I done wrong ?

How to change this script so that it finds the right window 1.xls  by Title ?

 

Edited December 26, 2018 by LazyVasily

[water]

In the second script you use $sWorkbook in _Excel_BookAttach but you use variable $oWorkbook to assign the title of the workbook ("1.xls")

[LazyVasily]

water, I understand. I made changes. The script still can't find the window "1.xls".

#include <Excel.au3>
#include <MsgBoxConstants.au3>

;Opt('WinTitleMatchMode', -2)
Local $sWorkBook = "1.xls"

$sExcel = _Excel_Open()

If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error creating Excel object! @error = " & @error & ", @extended = " & @extended)
$sWorkBook = _Excel_BookAttach($sWorkbook, "title")
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error attaching to the workbook! @error = " & @error & ", @extended = " & @extended)
$sWorkBook.ActiveSheet.Shapes.Range("Oval 1").Delete
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error deleting shape! @error = " & @error & ", @extended = " & @extended)

Shows the error "Error attaching to the workbook".