Embed and resize object with Macro/VBA

[SL_Accounting]

I found this very old post and it is almost exactly what I want... However there is one minor change I'm after.
here... /excel-programming-vba-macros/714267-resize-icon-representing-embedded-object-using-vba (I would make this a proper link -- but you all don't trust me with that much power)

I trust you :-)
https://excelforum.com/excel-program...using-vba.html
--6StringJazzer

well, there were a couple of changes, but I figured them out. What remains is I don't like the icon it gives. If I manually resize the object I end up with an icon that looks like a minuature of the document. With the macro, I didn't. I tried just deleting the icon file name (see code below), but that gives me a white square. What I would like is what I thought was the default.

Also, tried to include pic... as this is one of those cases where a picture would definately be worth 1000 words, but again not allowed for new users. Looks like it did allow the image after all... First icon is if done manually, 2nd is with the code from the above referenced thread, 3rd icon is what I am getting with code posted below.

Please Login or Register  to view this content.

[jolivanes]

I must be missing something but what is your concise question.

[SL_Accounting]

Concisely, i want the icon to look like the first one in the attached screenshot.

[SL_Accounting]

Well, magically, I closed everything and reopened and then it started working. I *think* the problem was the pdf was open. The solution (besides closing and reopening) was to set

Please Login or Register  to view this content.

Anyway. Im on to a new problem. Would there be a maximum number of objects and then the sizing part would quit working? I've inserted over 30 total objects into the spreadsheet, 10 or more using the macro and now, it is still placing the object at the active cell, but not resizing them, so I am back to resizing manually. I have again saved and closed all pdfs and excel and it did not fix the problem.

[jolivanes]

Not as far as I know.
How are you resizing them. I would think that importing them, as you have with above code, should be followed by resizing them.
Do you want to keep the aspect ratio or size the picture to cell height and width. In your first post it looks like keeping the aspect ratio to true

[jolivanes]

Don't know if this will help at all. References need to be changed.

Please Login or Register  to view this content.

[jolivanes]

What was your file size when all the problems started with the importing/resizing?

[SL_Accounting]

Thank you for this, alas, it still is not resizing.

So... in summary, I have rebooted my computer hoping to get rid of any residual whatever and start fresh.
If I run my original code or the code provided by jolivanes, it behaves just as if I had manually inserted the object (INSERT>Object>Create from File>Browse<pick file name>OK.) It embeds a pdf that is 8.26 x 6.38. I then right click choose format object>size>height -- set it to .3" and ok. No other changes. Then I am moving it to roughly center of the cell by dragging with the mouse. I expected to be able to automate this process.

I would think I imagined it working earlier, except I have the screen shot I inserted into my original post.

[SL_Accounting]

It does seem to have something to do with the file I am trying to embed them into. I opened a new xlsx sheet and ran the macro and it came out nearly perfectly. Any ideas?

The only thing wrong with this iteration is that instead of putting the file in my active cell (A7) it is putting it in one quite a ways away, (in cell C11R22 aka K22). (which made sense once i counted the cells )

It is just a bit bigger than I wanted it to be. See screenshot, I wanted it to be centered and just a bit shorter than the cell hieghtScreenshot 2022-08-04 201842.png

[jolivanes]

For me it centers it in cell D12.
Not for you?

[SL_Accounting]

I was wanting it to use the existing cell (row) height. not adjust the height to match the created thumbnail which is what happens when using your macro in the above file. And yes, it is on D12.
When I copied your code into my sheet, it did not adjust the row height, but used the existing row height (as per screen snip) but not where I expected it (as described above) I experimented by changing the size of K22 before running the macro and it adjusted to whatever size K22 was up to about 1.25" (i.e. make k22 any bigger than that and the thumbnail stays at ~1.25)

[jolivanes]

Re: "use the existing cell (row) height". That is what it does. Whatever the existing height happens to be.
Re: "it did not adjust the row height". No, it is not meant to. I might have missed that but did you request that?
Re: "but not where I expected it". Where else would you expect it if the code has a hard coded range in it (Set PicCell = Cells(12, 4))
Re: "make k22 any bigger than that and the thumbnail stays at ~1.25". After the macro has set the size, certainly so as it should. If you change the height and/or width before running the macro, the code will keep the ratio (aspect ratio) and centers the picture in that cell. Cell size and ratio height/width depending, picture will have white space on the sides or at the top and bottom.
Of course you can change the row height and/or column width with the code also.
All you have to do is explain what is needed.
I asked before but have not seen a reply. Have you looked at the saved workbook file size when you have 30+ embedded PDF files in it?

[SL_Accounting]

To answer your points and I want to reiterate, I really appreciate your help.

1) Re: "use the existing cell (row) height". That is what it does. Whatever the existing height happens to be.
perfect, exactly want I wanted -- but not what I observed.

2)Re: "it did not adjust the row height". No, it is not meant to. I might have missed that but did you request that?
no, I did not request it, none the less, I did observe the behavior.

3)Re: "but not where I expected it". Where else would you expect it if the code has a hard coded range in it (Set PicCell = Cells(12, 4))
obviously I didn't understand what that code was doing -- hence why I said, it was obvious once I counted it out. I would like it to be placed in the active cell. Is that possible -- If not, I can deal with hard coding it and then moving where i need it.

4)Re: "make k22 any bigger than that and the thumbnail stays at ~1.25". After the macro has set the size, certainly so as it should. If you change the height and/or width before running the macro, the code will keep the ratio (aspect ratio) and centers the picture in that cell. Cell size and ratio height/width depending, picture will have white space on the sides or at the top and bottom.
This wasn't important to me, just explaining my observations

5)I asked before but have not seen a reply. Have you looked at the saved workbook file size when you have 30+ embedded PDF files in it?
I thought you were asking the page size of the object I was inserting, my bad. The document is currently 29,434KB and contains over 100 embedded objects with about 25 left for me to add at this point. As I am using a diff cptr today, I don't have the copy I saved at that time, to say what it was when it quit working -- only my network copy, which when i just inserted a new object says it is #124.

I set this up as a macro on a new computer, just out of curiosity and ... it is behaving exactly like it was on the other computer when using the existing (on the network) file i.e. resizing not working placing top left corner of the thumbnail in the top left corner of active cell. With a new blank document it is also working the same as before. I.e. importing and resizing based on size of K22. If K22 is larger ~100 px it resizes to just a few px smaller than the cell height. If K22 is smaller < 50px it adjusts to just a fex px larger than cell height.

All this is a long way of trying to explain what I am hoping to accomplish.
I would like to have the thumbnail placed -- centered on the active cell. Some will be landscape orientation, most are portrait.
Optionally, I would like to be able to put two thumbnails in the same cell, and have it center both with a small bit of white space between them (so the white space would actually be at the center point) -- but I haven't got as far as experimenting with that automation.
I would like the thumbnail to be either a fixed size, (defined in the macro) and the macro adjusts the cell height accordingly or for it to adjust to the cell size, I don't so much care which approach as long as I know what to expect.
I would like the thumbnail size to be just smaller than the cell size so it looks aesthetically pleasing to me. and I could have multiple thumbnails all lined up nicely. (such as pic below -- but actually lined up)
Capture.PNG

Lastly, why does the forum log me out while I am typing the post, and not let me post (I do not have permission??) At first I thought it was just the permission to upload attachments, but then while trying to troubleshoot that, I realized I was no longer logged in.
Thank you again for your time and assistance.

[jolivanes]

If you want it in the ActiveCell (=Selected Cell), Change this

Please Login or Register  to view this content.

To this

Please Login or Register  to view this content.

The code should center the embedded file picture in the cell. However, it keeps the width/height ratio.

The reason I asked for the saved file size is that if it gets too big it might get unwieldy.

For longer posts it might be better to do it in notepad and copy/paste it into here. I think, but not sure, that there is a time limit on having a post open and available.

If you want to have 2 embedded pictures in a cell, that is possible of course but one needs to know how many pixels between pictures.

[SL_Accounting]

Thank you. I didnt realize until now that the macro you had in the downloaded file was not the same as the one you had posted just above it.
It works perfectly. I was able to modify it to be just smaller than the cell as I wanted it to be.

Also, with my new sheet, I added 32 objects so far and it is still working. No clue why the other one ceased to be functional. Since I actually only needed 16 more... I'll quit playing with it till the next time I need this. I assigned it to a button on my quick access bar and it works like a dream!

[SL_Accounting]

Please Login or Register  to view this content.

[jolivanes]

Sounds like all is well on the West Coast, as far as Excel goes anyway.
Thanks for the update and good luck.