Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Geek Culture / VBscript / vba positioning of an image in Excel help please.

Author
Message
Battoad
AGK Developer
18
Years of Service
User Offline
Joined: 12th Feb 2007
Location: A Dark Place
Posted: 15th Aug 2013 13:34
Link
Mobiius has introduced me to vbscript which when integrated in my DBP program creates an Excel spread sheet and formats cells.
I write to a .vbs file and then execute this file to run the vbs.



I have now ben able to insert an image into the spread sheet using


but I am unable to position it to where I would like.

I understand that I should be able to position the image using a top and left cell reference but after several days searching and trying I am unable to achieve any positioning.

Can anyone help me based on the previous code styling.

Back to top
Profile PM Email Website
Battoad
AGK Developer
18
Years of Service
User Offline
Joined: 12th Feb 2007
Location: A Dark Place
Posted: 15th Aug 2013 13:55
Link
Hi Mods
I have placed this thread in the wrong place, should have gone under "programming talk". Any chance you could move it for me.
Thank you

Back to top
Profile PM Email Website
Van B
Moderator
22
Years of Service
User Offline
Joined: 8th Oct 2002
Location: Sunnyvale
Posted: 15th Aug 2013 14:08
Link
No worries, but let's leave it here for a little bit first - see if anyone can help. Programming talk doesn't get a lot of traffic, I don't see any harm in fishing for help here for a little bit before we move it.

On the subject though. I think you'd be better off having the picture object in place first, hide it, then show it with code and change the image before setting the Top and Left properties. It might just be that it doesn't like changing positions of brand new objects - maybe having the object in place instead of dynamic would make it a little easier to code.

I am the one who knocks...
Back to top
Profile PM Email
Mobiius
Valued Member
22
Years of Service
User Offline
Joined: 27th Feb 2003
Location: The Cold North
Posted: 15th Aug 2013 23:54 Edited at: 15th Aug 2013 23:54
Link
Instead of activesheet, try activecell. (Or currentcell??)

I don't have excel installed on my laptop so can't test right now for you. (I was at work when I gave you the previous code you see.)
I'm next at work on Saturday if you wanna email me again then to remind me. (I did get your email, I've just been busy which is why I haven't replied yet.)


Now Visit My Website
Back to top
Profile PM Email Website
Battoad
AGK Developer
18
Years of Service
User Offline
Joined: 12th Feb 2007
Location: A Dark Place
Posted: 17th Aug 2013 10:13
Link
Thanks Van B and Mobiius for your thoughts/ideas.
Unfortunately I am still battling with this one, unsuccessfully.

I have tried 3 approaches to solving this one, none of which work.
1) Select cell area just prior to inserting picture
2) Cut picture, select new area, paste
3) Using the top & left cell format which is intended to place the image relative to a cell similarly to how DBPro positions sprites relative to pixels.

Replacing activesheet with activecell does not affect my efforts.

@Mobiius thank you for your offer to look at this for me when you have access to Excel.

Back to top
Profile PM Email Website
Battoad
AGK Developer
18
Years of Service
User Offline
Joined: 12th Feb 2007
Location: A Dark Place
Posted: 17th Aug 2013 17:31
Link
Mobiius has found a solution



Works a charm

Many thanks - I think I might need to get a vbscript manual for xmas.

Back to top
Profile PM Email Website
Mobiius
Valued Member
22
Years of Service
User Offline
Joined: 27th Feb 2003
Location: The Cold North
Posted: 17th Aug 2013 17:55 Edited at: 17th Aug 2013 17:56
Link
Boom! lol

Quote: "I think I might need to get a vbscript manual for xmas."

Or buy shares in google! lol


Now Visit My Website
Back to top
Profile PM Email Website
spooky
22
Years of Service
User Offline
Joined: 30th Aug 2002
Location: United Kingdom
Posted: 17th Aug 2013 17:58
Link
A quick Google found something that works (at least on my pc!)

Just select the cell where you want to insert the image first.

So for example to put at G6

Quote: "
objWorksheet.range("G6").Select
objWorksheet.Pictures.Insert("d:\image.jpg").Select
"


Boo!
Back to top
Profile PM Website
Battoad
AGK Developer
18
Years of Service
User Offline
Joined: 12th Feb 2007
Location: A Dark Place
Posted: 17th Aug 2013 18:53
Link
@spooky, I am unable to get your version to work but naming a cell does have its advantages.
It selects the cell ok but does nor insert the image.
I think it must be something to do with the 1st part of the string of your code which doesn't tie in with my coding, i.e objWorksheet.

Maybe a bit of experimenting with it may prove also successful.

Thank you

Back to top
Profile PM Email Website
spooky
22
Years of Service
User Offline
Joined: 30th Aug 2002
Location: United Kingdom
Posted: 17th Aug 2013 19:20 Edited at: 17th Aug 2013 19:21
Link
Good Luck.

Works fine for me. I have Excel 2010 by the way so maybe you have different version. Here is the full code so you can see what I ran;



Boo!
Back to top
Profile PM Website
spooky
22
Years of Service
User Offline
Joined: 30th Aug 2002
Location: United Kingdom
Posted: 17th Aug 2013 19:37
Link
You could also try;

Quote: "
objExcel.Range("G6").Activate
objWorksheet.Pictures.Insert("D:\image.jpg")
"


However just read bottom of following page which suggest Excel 2007 does not support this type of image placement and have to use different method using shapes inserting;

http://blogs.technet.com/b/heyscriptingguy/archive/2007/09/04/how-can-i-add-a-background-picture-to-an-excel-worksheet.aspx

Boo!
Back to top
Profile PM Website
Battoad
AGK Developer
18
Years of Service
User Offline
Joined: 12th Feb 2007
Location: A Dark Place
Posted: 17th Aug 2013 20:36
Link
Hi spooky, I am using Excel 2007 so that explains why I have been unable to position an image relative to a cell.
Unfortunately the ".Activate" option further confirms the limitation.
However I can move forward with pixel positioning, just a bit more code required.

So thank you, its been a good lesson in the end.

Back to top
Profile PM Email Website
spooky
22
Years of Service
User Offline
Joined: 30th Aug 2002
Location: United Kingdom
Posted: 18th Aug 2013 01:46
Link
Bit bored this evening so being having fun reading posts all over the net with people trying to do similar things in various versions of Excel and the miriad of answers given. Seems that VBA and VBS have undergone quite a few changes over the years and confused the hell out of everyone!

anyway something that should work in 2007 if you would still prefer to position image at a cell reference.



Boo!
Back to top
Profile PM Website
Battoad
AGK Developer
18
Years of Service
User Offline
Joined: 12th Feb 2007
Location: A Dark Place
Posted: 18th Aug 2013 10:30
Link
@spooky, that solution is spooky

I have played around with a similar piece of coding but not including the "with \ end with" nest but got nowhere near.
That's really good, worked 1st time in exactly the place I wanted it, thank you.

Back to top
Profile PM Email Website
Back to top

Login to post a reply

Server time is: 2025-05-15 02:38:11
Your offset time is: 2025-05-15 02:38:11