[Solved] Delete embedded pictures in spreadsheet with macro

[arunppai]

Dear Friends, Hello,

I am using Open Office 4.1.14, Windows 8.1 is the OS.
I use Open Office Visual Basic extensively.

Every month, I download financial statements from the net, and save in "Excel Format".
Subsequently I open the file in Open Office 4.1.14 as a calc document, and save it as an ".ods" file (I don't have Microsoft Excel).

Then with the help of Macros written in Open Office Basic the data is extracted and formated for analysis and storage.

These downloaded files come with embedded pictures, logos etc, which I have to pick and delete.

Please could any one give a few lines of VB code, as to how to get / enumerate these objects and delete them in one go.

Before posting this problem, I searched the forum and also read "OOo_Macros_Pitonyak.pdf" but did not find answers.

Thank you in advance.

Have a nice day.

[Villeroy]

If the unwanted pictures are the only unwanted objects:
Hit Ctrl+A to select all cells on the sheet.
Hit the Delete key (or menu:Edit>Delete Contents), choose "Objects", [OK], Done.

[JeJe]

Villeroy's solution, if sufficient, can be turned into a macro using the macro recorder.

[musikai]

https://ask.libreoffice.org/t/hi-im-try ... -tab/38183

[Zizi64]

Every month, I download financial statements from the net, and save in "Excel Format".

Does it means a "Copy-Paste" procedure, or it is a real "downloading" procedure? If you make a copy-paste, then you can paste the content as unformatted text (only the text and numbers, but not the other objects) into your spreadsheet file.

[Mountaineer]

...
These downloaded files come with embedded pictures, logos etc, which I have to pick and delete.

Please could any one give a few lines of VB code,
...

If the above suggestions are not fitting, please provide an example file. I don't think it will be your private account, but data for stocks or crypto-currencies, but remove/replace private data before upload.

It is much easier to discuss problems with fitting data, than without...

PS: You don't need VB code, as Visual Basic is the implementation and API MS uses in MS-Office. Aoo/LO use their own API named UNO, and there are also some differences between Star-Basic and Visual Basic.

[arunppai]

Hello Friends,

Thank you for all your suggestions.

This is what I did and it worked.

Option Explicit
Option Compatible
Sub Main
     CompatibilityMode(True)
     ClearDefinedRange
End Sub

Private Sub ClearDefinedRange
     REM Refer page 157 / 527 UsefulMacroInfo_AndrewMacro.pdf
     Dim oDoc As Object, oSheet As Object, oSheets As Object
     Dim oCellRange As Object
     
     
     oDoc  = ThisComponent
     oSheets = oDoc.Sheets
     
     REM get the sheet & the range
     oSheet = oSheets.getByName("Relevant Sheet")
     oCellRange = oSheet.getCellRangeByName("The Range")

     REM To keep data and delete objects etc
     oCellRange.clearContents(_          
          com.sun.star.sheet.CellFlags.HARDATTR OR _
          com.sun.star.sheet.CellFlags.STYLES OR _
          com.sun.star.sheet.CellFlags.OBJECTS OR _
          com.sun.star.sheet.CellFlags.EDITATTR)
End Sub

Trouble being, I did not apply my mind to my master's (Pitonyak's books) book which is my guiding principle.
Becuase, OOo unlike Microsoft VB is very difficult to comprehend.

In fact the above Macro also made the following lines reduntant.

With CellRange
.merge(False)
.IsTextWrapped = False
                REM and many more lines like this
                REM not necessary.
End With

Thank you very much, have a nice weekend.

Edit: Added formatting tags -- MrProgrammer, forum moderator