Page 1 of 1

Macro to Search for numbered photos and insert in Calc?

PostPosted: Tue Aug 27, 2013 7:36 pm
by elstie
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.

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

PostPosted: Tue Aug 27, 2013 11:00 pm
by Villeroy
Calc is the wrong component. This could be done easily (few lines of code) with the Base component.

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

PostPosted: Tue Aug 27, 2013 11:12 pm
by elstie
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.

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

PostPosted: Tue Aug 27, 2013 11:17 pm
by Villeroy
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.

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

PostPosted: Tue Aug 27, 2013 11:33 pm
by elstie
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?

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

PostPosted: Tue Aug 27, 2013 11:52 pm
by F3K Total
Hi,
try this:
Import_pictures.ods
(10.85 KiB) Downloaded 595 times


R

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

PostPosted: Tue Aug 27, 2013 11:59 pm
by elstie
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?

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

PostPosted: Wed Aug 28, 2013 12:04 am
by F3K Total
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

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

PostPosted: Wed Aug 28, 2013 12:08 am
by elstie
Thanks. I got it to run but got an error.

How would I use it for the spreadsheet I was working on?

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

PostPosted: Wed Aug 28, 2013 12:17 am
by Villeroy