Macro to Search for numbered photos and insert in Calc?

[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

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.

[Villeroy]

Calc is the wrong component. This could be done easily (few lines of code) with the Base component.

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

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

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

[F3K Total]

Hi,
try this:

Import_pictures.ods

(10.85 KiB) Downloaded 723 times

R

[elstie]

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?

[F3K Total]

and how do I activate the macro?

Tools/Options/OpenOffice/Security/Macro Security ... ->Level Medium

What do I need to do to tailor it to my spreadsheet

see Help (F1) and read in the forum ...

R

[elstie]

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

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

[Villeroy]

http://www.mediafire.com/download/qg1bd ... ontrol.zip
http://forum.openoffice.org/en/forum/do ... hp?id=8790