Grab images from URL and insert in cells...

Hi,

I am trying to make an inventory of my wine cellar. I'm using CellarTracker for the cellar management and now need to print out some QR codes to attach to the bottles.

Anyways, I have exported my cellar to excel so I have all the data, including the barcode for each bottle. I have also generated the unique QR-code URLs for each bottle.

What I now need to do is write a macro that goes trough row 2 -> last row (currently 63) and grabs the URL from column U and inserts the corresponding picture in column V.

Is this possible?

Cheers,

Marcus

All seems a bit complicated to me:

Please Login or Register  to view this content.

All seems a bit complicated to me:

Please Login or Register  to view this content.

The reason you get the error in the previous code is that it uses code that only exists on windows computers

Hi Kyle123,

Thanks for your post. It seams a bit complicated to me as well, I am very grateful for the geniuses that reside in this forum

I tried your code but I get an error with that one as well. This time the debugger points to "With .Pictures.Insert(oCell.Value)"

Do you have any suggestion to why this happens?

Thank you

/Marcus

Are the urls in the U column valid? what do they look like?

It might be a mac thing, but I can't check since I don't have office on my mac.

The urls look like this:

http://chart.apis.google.com/chart?c...ntory=29145085
http://chart.apis.google.com/chart?c...ntory=29145086
http://chart.apis.google.com/chart?c...ntory=29145845
http://chart.apis.google.com/chart?c...ntory=29145846
http://chart.apis.google.com/chart?c...ntory=29145040
http://chart.apis.google.com/chart?c...ntory=29145041
http://chart.apis.google.com/chart?c...ntory=29145042
...

What's the error message? Is it - Unable to Get the Insert Property of the Pictures class?

"Run-time error '1004':

Unable to get the Insert property of the Pictures class"

And when I click Debug it just points to ".Pictures.Insert(oCell.Value)"

The links appear complete in excel. Without the "..." in them.

What do you mean not valid? They all seam to work for me (i get a qr-code when clicking on each of them..)

I tried the new code. Now I get a different error:

"Run-time error '9':

Subscript out of range"

The debugger points to: "With .Pictures.Insert(oCell.Hyperlinks(1).Address)"

Ok, think it's time for you to upload a sample workbook

hmm, found something strange now. If I click the link in excel i get to a page that just shows:

"‰PNG  IHDR
^
^BI�MbKGD��� ��“-IDATxœ���n�6@Ѹ��������C��<†V�*8K}|~~�����o�‘4A€ @� H�
�i‚4A€ @� H�
�i‚4A€ @� H�
�i‚4A€ @� H�
�i‚4A€ @� H�
�i‚4��� ����˜�…����™�}��W���–�]ٽŸ��>u��<���?�j‚4A€ @� H�
�s +S��S��žš�X9=Gp����y���e�€ @� H�
�i½s +S�������5n›#8=�p���e�€ @� H�
�i����������w��Ÿ����ž�\~a�i‚4A€ @� H�5|��~��;/pz>�<�%�€ @� H�
�i���n��ž:�`�~�� �����X5A€ @� H�
�„{�޲O>��„��x��/ž�–���X5A€ @� H�
�„�����O›šw�u�{1ž:Ÿ‚?d�i‚4A€ @� H��N�N���>�6����9+S���{@V^��X5A€ @� H�
�„{�n�~�s��_x˹�žš˸���‡i�i‚4A€ @� H�kx‹�/���o��S�M<�y �w @� H�
�i���X:��–��zj?���…]S��›_X�j‚4A€ @� H�
��5�œ�ž�?Ÿ��•��)n3�=�ŸV @� H�
�i‚4a��†���w�Ÿ��y+�����Œ�׹�yxœU�
�i‚4A€ @���†•ӿ{��œ…��—a���u�&L�j‚4A€ @� H�
��5�<���‡•���8}Ž����#v�v���<…U�
�i‚4A€ @�w��-��O�AL�������|�m��8� H�
�i‚4A€p�{(N���~Š��}���׹€k�A��j‚4A€ @� H�
�ž��”�����‰x�=S��X9}?��2��ƒ�j‚4A€ @� H�
��a��~��}���Ÿ~?��箾���/N�›<5W�e�€ @� H�
�i��{(�o�߯�2u��׹��†��]�‡�˪
�i‚4A€ @� �o�a����}�sN›š_˜z�v�?Ϊ
�i‚4A€ @� ��5�:������z�9SsS��ng�i‚4A€ @� H�k�m~�}��޿p�ƒ�›n{����i�i‚4A€ @� H~N����‚]��S0u���{N���<|V @� H�
�i‚4a~�awŸ|�w�˜š;8�=�}~��†\{.�ŠU�
�i‚4A€ @˜Ÿkx���W�ϧ�8�Ͽ��w���~��GxŠU�
�i‚4A€ @˜ŸkX�m�{��y S�S���5uŽ����qV @� H�
�i‚4�޹†•����WO�L}+O�kœž›8=W2Ϊ
�i‚4A€ @� �o��6S���–��k�/�M�;—aŪ
�i‚4A€ @� ˜k�j�����ž����L}o�fNa—U�
�i‚4A€ @x�\�[��w��O�“Ÿ>���|���ž~�ɵ�V @� H�
�i‚4�޹†��7a����?4u��S���|ʵ�ƒU�
�i‚4A€ @��v[d�i‚4A€ @� H�
�i‚4A€ @� H�
�i‚4A€ @� H�
�i‚4A€ @� H�
�i‚4A€ @� H�
�i��g†�����IEND�B`‚"

But if i then click in the address bar and then hit enter, the page reloads and the qr-code shows. (I have to click in the address bar and then press enter, just refreshing with cmd+r does not work...)

Ok, please post a sample workbook

Here comes the workbook

I think the problem is that you have a mixture of hyperlinks (as Excel understands them) and urls that Excel hasn't converted to hyperlinks yet.

This worked for me for all your items:

Please Login or Register  to view this content.

Seams like only the first url is a hyperlink and the rest is just text, right?

I tried running that macro but get the same error as the first time now.

Debugger points to: "With .Pictures.Insert(address)"

Where do you place the marker when you run the macro? Or does that not matter?

/Marcus

It doesn't matter, but unless you tell me what the error message actually is, I'm shooting in the dark - Which is "the first time" error message?

error message:

"Run-time error '1004':

Unable to get the Insert property of the Pictures class"

Debugger points to the line with: "With .Pictures.Insert(address)"

Then honestly, I've got no idea. It might be a mac thing but I'd guess not since Pictures.Insert has been around a very long time.

I'll ask to see if someone with mac office can have a look

Thanks Kyle123, I really appreciate it.

I found this link (http://stackoverflow.com/questions/1...ba-excel-shape) with has a similar issue. I quote:

"I've used a small subroutine to insert a picture into my sheet by
ActiveSheet.Pictures.Insert(URL).Select
This works fine with Excel 2003 (Windows), but does not work with Excel 2011 (Mac) any more."

It then goes on to explain another method, but it is to advanced for me. They use something that looks like theShape.Fill.UserPicture URL.

Further down in the code it looks like this:

"' Create a Shape for putting the Image into
' ActiveSheet.Pictures.Insert(URL).Select is deprecated and does not work any more!!!
Set theShape = wks.Shapes.AddShape(msoShapeRectangle, pasteCell.Left, pasteCell.Top, 200, 200)

' fill the shape with the image after greening
theShape.Fill.BackColor.RGB = RGB(0, 255, 0)
theShape.Fill.UserPicture URL
"

Does this make sense to you?

EDIT:
also saw that they start the code by defining theShape as shape: "Dim theShape As Shape"