Macro to Search for numbered photos and insert in Calc?

The Application Programming Interface and the OASIS Open Document Format

Macro to Search for numbered photos and insert in Calc?

Postby elstie » Tue Aug 27, 2013 7:36 pm

I have a project where I have a numbered list in a Calc document (i.e. every line is numbered in the 'A' column separate from the row numbering).

I'd like to insert pictures at the end of each row (roughly column 'I') when I run the script. The beginning of the file names correspond to the aforementioned line numbers (e.g. "Line 20 - house.png"). All of the pictures are contained in one folder. So, basically, I believe the macro would need to search the folder for a partial file name, return the file name, and insert the picture in the corresponding cell.

I don't have any experience with this API, and it's been years since I've programmed things. The documentation isn't what I'm used to, so I'm finding it hard to find what I'm looking for.

Here's some laughably simple pseudocode (informal, obviously) that I think does what I'm looking for:

Code: Select all   Expand viewCollapse view
for x, firstLine to lastLine
    FileName = Search(path for "Line " + x)
    InsertPhoto(FileName, Row[x]Column[I])
end


A bonus would be if the macro could scale the inserted photo to the row height. I'm hoping for any help the experts on here could provide. It would save unimaginable amounts of time.
OpenOffice 3.4.0 on MacOS 10.7.4
elstie
 
Posts: 6
Joined: Tue Aug 27, 2013 6:57 pm

Re: Macro to Search for numbered photos and insert in Calc?

Postby Villeroy » Tue Aug 27, 2013 11:00 pm

Calc is the wrong component. This could be done easily (few lines of code) with the Base component.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to Search for numbered photos and insert in Calc?

Postby elstie » Tue Aug 27, 2013 11:12 pm

I appreciate the reply. Unfortunately, I can't use Base for this project, since it's a standardized work document that's come from my company. They need it back in this exact format.

Out of curiosity, what would be the code to use in Base? I could potentially use that for a different purpose.
OpenOffice 3.4.0 on MacOS 10.7.4
elstie
 
Posts: 6
Joined: Tue Aug 27, 2013 6:57 pm

Re: Macro to Search for numbered photos and insert in Calc?

Postby Villeroy » Tue Aug 27, 2013 11:17 pm

You would simply link a form's picture control or a reports picture control to a calculated file name.
A form lets you browse from record to record, a report would show all the records (or a filtered subset) in a Writer layout.
So the basic functionality is in the Base component (and the report builder extension) without writing a single line of macro code.
Millions of "Excel experts" do such stunts in their calculator software because they do not anything but spreadsheets. If all you know is a hammer, everything looks like a nail.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to Search for numbered photos and insert in Calc?

Postby elstie » Tue Aug 27, 2013 11:33 pm

Sorry, I haven't yet used Base, so I'm very new to this. I think I understand what you're saying, except that the file names aren't exact. They only start with the relevant date. For instance, if the entry's ID is, say, 25, the corresponding image would start with "Line 25...", but the rest of the file name is arbitrary, for instance "Line 25 - red house".

How would I go about linking the correct files to those entries?
OpenOffice 3.4.0 on MacOS 10.7.4
elstie
 
Posts: 6
Joined: Tue Aug 27, 2013 6:57 pm

Re: Macro to Search for numbered photos and insert in Calc?

Postby F3K Total » Tue Aug 27, 2013 11:52 pm

Hi,
try this:
Import_pictures.ods
(10.85 KiB) Downloaded 465 times


R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Macro to Search for numbered photos and insert in Calc?

Postby elstie » Tue Aug 27, 2013 11:59 pm

F3K Total wrote:Hi,
try this:
Import_pictures.ods


R


That looks pretty awesome. I'm not sure how to use it, though. What do I need to do to tailor it to my spreadsheet, and how do I activate the macro?
OpenOffice 3.4.0 on MacOS 10.7.4
elstie
 
Posts: 6
Joined: Tue Aug 27, 2013 6:57 pm

Re: Macro to Search for numbered photos and insert in Calc?

Postby F3K Total » Wed Aug 28, 2013 12:04 am

elstie wrote:and how do I activate the macro?

Tools/Options/OpenOffice/Security/Macro Security ... ->Level Medium
elstie wrote:What do I need to do to tailor it to my spreadsheet
see Help (F1) and read in the forum ...

R
Last edited by F3K Total on Wed Aug 28, 2013 12:09 am, edited 1 time in total.
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Macro to Search for numbered photos and insert in Calc?

Postby elstie » Wed Aug 28, 2013 12:08 am

Thanks. I got it to run but got an error.

How would I use it for the spreadsheet I was working on?
OpenOffice 3.4.0 on MacOS 10.7.4
elstie
 
Posts: 6
Joined: Tue Aug 27, 2013 6:57 pm

Re: Macro to Search for numbered photos and insert in Calc?

Postby Villeroy » Wed Aug 28, 2013 12:17 am

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest