Resize Images

Hints, tips and tricks for newbies

Moderators: Dorian (MJT support), JRL

nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

Resize Images

Post by nodochau » Wed Mar 25, 2020 3:35 pm

Hello,
I have some pictures those need to be resized so that they can be inserted into a spreadsheet (with a size: Height=0.28" Width=0.7") . So I am wondering that are we able to do the task with MS?
Any help would be greatly appreciated!

Thanks

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Resize Images

Post by Dorian (MJT support) » Wed Mar 25, 2020 3:45 pm

I've done this with great success using Macro Scheduler with ImageMagick via command line. I used GetFileList to get a list of thousands of images in a folder, and then ran through each one with imageMagick.
Yes, we have a Custom Scripting Service. Message me or go here

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Resize Images

Post by Dorian (MJT support) » Wed Mar 25, 2020 4:15 pm

Something like this. It'll resize images using ImageMagick. Be careful, the script below will overwrite the files it finds. I'm just using this as a simple example of ImageMagick usage. IM is very powerful once you delve into it, and I'm sure there will be a way of resizing the images how you want. I recommend tweaking this script to write to a different location, or make sure to back up your images first. Obviously you'll need ImageMagick in place for this to work.

Code: Select all

Let>RP_WINDOWMODE=0
let>FilePath=d:\MJT\files\images
let>IMPath=C:\Program Files\ImageMagick-7.0.9-Q16\magick

GetFileList>%FilePath%\*.gif,files
Separate>files,;,file_names

Let>k=0
Repeat>k
  Let>k=k+1

  let>TheFile=file_names_%k%
  //WARNING - this will overwrite the image files
  runprogram>%IMPath% convert %TheFile% -resize 100x100 %TheFile%
Until>k,file_names_count
Yes, we have a Custom Scripting Service. Message me or go here

nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

Re: Resize Images

Post by nodochau » Wed Mar 25, 2020 4:34 pm

Thank you Dorian.

nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

Re: Resize Images

Post by nodochau » Wed Mar 25, 2020 4:40 pm

Dorian (MJT support) wrote:
Wed Mar 25, 2020 4:15 pm
Something like this. It'll resize images using ImageMagick. Be careful, the script below will overwrite the files it finds. I'm just using this as a simple example of ImageMagick usage. IM is very powerful once you delve into it, and I'm sure there will be a way of resizing the images how you want. I recommend tweaking this script to write to a different location, or make sure to back up your images first. Obviously you'll need ImageMagick in place for this to work.

Code: Select all

Let>RP_WINDOWMODE=0
let>FilePath=d:\MJT\files\images
let>IMPath=C:\Program Files\ImageMagick-7.0.9-Q16\magick

GetFileList>%FilePath%\*.gif,files
Separate>files,;,file_names

Let>k=0
Repeat>k
  Let>k=k+1

  let>TheFile=file_names_%k%
  //WARNING - this will overwrite the image files
  runprogram>%IMPath% convert %TheFile% -resize 100x100 %TheFile%
Until>k,file_names_count
And do you have the easiest and fastest method to insert images into Excel spreadsheet?
:)

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Resize Images

Post by Dorian (MJT support) » Wed Mar 25, 2020 11:33 pm

Do you mean insert the actual images? Or the image names? I haven't tried it but if it's the actual images I'd probably start by looking at XLRunCode.
Yes, we have a Custom Scripting Service. Message me or go here

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Resize Images

Post by Dorian (MJT support) » Thu Mar 26, 2020 2:19 pm

Support is quiet here at the moment so I was able to put some time into this for you. You should be able to use it as a template and to understand the methods I used. I created the VBA by recording a macro in Excel. Then copied the VBA into the script, with minor editing. As you can see, we can use variables in it. XLRunCode can run that VBA in a non-Macro-enabled workbook.

You'll need to do a little research on Imagemagick to get you where you want, but the Macro Scheduler part is here for you to get you started.

Make sure to read the help file on XLRunCode, as you will have to change an Excel setting to enable it.

Code: Select all

//Set up the variables - edit these
Let>XLFile=d:\MJT\files\Excel\XLscratch.xlsx
Let>ImageLocationIn=D:\MJT\Files\ImagesIn
Let>ImageLocationOut=D:\MJT\Files\ImagesOut
let>IMPath=C:\Program Files\ImageMagick-7.0.9-Q16\magick

Let>RP_WINDOWMODE=0
Let>RP_Wait=1
Let>GFL_SORTTYPE=3


//If Excel file Exists, open it.  If not, create it.
IfFileExists>%XLFile%
  XLOpen>XLFile,1,xlBook
Else
  XLCreate>%XLFile%,1,xlBook
Endif

//Get the list of images.  In this case, GIFs
GetFileList>%ImageLocationIn%\*.gif,files
Separate>files,;,file_names

//Set up the loop variables
Let>k=0
Let>TopPos=10
Let>LeftPos=10

//Start the loop
Repeat>k
  Let>k=k+1
  let>TheFile=file_names_%k%
  ExtractFileName>%TheFile%,FileNameOnly

  //Resize and copy your image
  Let>IMcommand=convert %TheFile% -resize 50x50 %ImageLocationOut%\%FileNameOnly%
  runprogram>%IMPath% %IMcommand%

  //Insert and move image in Excel
  LabelToVar>insert_and_move,ImageCode
  XLRunCode>xlBook,ImageCode

  //Change the positioning so they're not all on top of each other
  Let>LeftPos=LeftPos+50
Until>k,file_names_count


//This vba is a recorded Excel Macro, with Macro Scheduler Variables edited in
/*
insert_and_move:
    ActiveSheet.Pictures.Insert("%ImageLocationOut%\%FileNameOnly%").Select
    Selection.ShapeRange.IncrementLeft %LeftPos%
    Selection.ShapeRange.IncrementTop %TopPos%
*/
Yes, we have a Custom Scripting Service. Message me or go here

Ranger762
Newbie
Posts: 7
Joined: Thu Mar 26, 2020 9:55 am

Re: Resize Images

Post by Ranger762 » Thu Mar 26, 2020 4:01 pm

Thanks, I tried it and it worked for me as well, it's great!

nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

Re: Resize Images

Post by nodochau » Sun Mar 29, 2020 8:09 pm

Dorian (MJT support) wrote:
Wed Mar 25, 2020 4:15 pm
Something like this. It'll resize images using ImageMagick. Be careful, the script below will overwrite the files it finds. I'm just using this as a simple example of ImageMagick usage. IM is very powerful once you delve into it, and I'm sure there will be a way of resizing the images how you want. I recommend tweaking this script to write to a different location, or make sure to back up your images first. Obviously you'll need ImageMagick in place for this to work.

Code: Select all

Let>RP_WINDOWMODE=0
let>FilePath=d:\MJT\files\images
let>IMPath=C:\Program Files\ImageMagick-7.0.9-Q16\magick

GetFileList>%FilePath%\*.gif,files
Separate>files,;,file_names

Let>k=0
Repeat>k
  Let>k=k+1

  let>TheFile=file_names_%k%
  //WARNING - this will overwrite the image files
  runprogram>%IMPath% convert %TheFile% -resize 100x100 %TheFile%
Until>k,file_names_count
Hi Dorian,
I tried to run the code but it did not work. But I could run in cmd manually :(

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Resize Images

Post by Marcus Tettmar » Mon Mar 30, 2020 8:42 am

I think some quotes are needed:

RunProgram>"%IMPath%" convert "%TheFile%" -resize 100x100 "%TheFile%"
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Resize Images

Post by Dorian (MJT support) » Mon Mar 30, 2020 10:17 am

This is worth delving in to. :)

It works for me both with the quotes Marcus added, and without them. However, that may be because I don't have any spaces in my file paths.

All these methods work - for me :

Code: Select all

Let>RP_WINDOWMODE=0
let>TheFile=d:\MJT\files\images\ImageA.gif
let>IMPath=C:\Program Files\ImageMagick-7.0.9-Q16\magick
runprogram>%IMPath% convert %TheFile% -resize 1000x1000 %TheFile%

Code: Select all

Let>RP_WINDOWMODE=0
let>TheFile=d:\MJT\files\images\ImageB.gif
let>IMPath=C:\Program Files\ImageMagick-7.0.9-Q16\magick
Let>IMcommand=convert %TheFile% -resize 1000x1000 %TheFile%
runprogram>%IMPath% %IMcommand%

Code: Select all

Let>RP_WINDOWMODE=0
Let>IMcommand=C:\Program Files\ImageMagick-7.0.9-Q16\magick convert d:\MJT\files\images\ImageC.gif -resize 1000x1000 d:\MJT\files\images\ImageC.gif
runprogram>%IMcommand%

Code: Select all

Let>RP_WINDOWMODE=0
runprogram>C:\Program Files\ImageMagick-7.0.9-Q16\magick convert d:\MJT\files\images\ImageD.gif -resize 1000x1000 d:\MJT\files\images\ImageD.gif
However, using the last example, if i rename my "Images" folder to "ima ges", even after I edit the filepath, the script doesn't resize the image. So as Marcus says, adding quotes is the fix :

Code: Select all

Let>RP_WINDOWMODE=0
runprogram>C:\Program Files\ImageMagick-7.0.9-Q16\magick convert "d:\MJT\files\ima ges\ImageD.gif" -resize 100x100 "d:\MJT\files\ima ges\ImageD.gif"
Yes, we have a Custom Scripting Service. Message me or go here

nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

Re: Resize Images

Post by nodochau » Mon Mar 30, 2020 4:46 pm

Thanks a lot Dorian,
I found out if you are in C:\users\pictures\imgs... then the IM works and if you create a folder within pictures folder like C:\users\pictures\resize_pic\imgs...then IM doesn't work. It is strange
But I made it worked now.
Again, I appreciate your help.
My project is done :)

nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

Re: Resize Images

Post by nodochau » Fri Apr 10, 2020 4:24 pm

Dorian (MJT support) wrote:
Thu Mar 26, 2020 2:19 pm
Support is quiet here at the moment so I was able to put some time into this for you. You should be able to use it as a template and to understand the methods I used. I created the VBA by recording a macro in Excel. Then copied the VBA into the script, with minor editing. As you can see, we can use variables in it. XLRunCode can run that VBA in a non-Macro-enabled workbook.

You'll need to do a little research on Imagemagick to get you where you want, but the Macro Scheduler part is here for you to get you started.

Make sure to read the help file on XLRunCode, as you will have to change an Excel setting to enable it.

Code: Select all

//Set up the variables - edit these
Let>XLFile=d:\MJT\files\Excel\XLscratch.xlsx
Let>ImageLocationIn=D:\MJT\Files\ImagesIn
Let>ImageLocationOut=D:\MJT\Files\ImagesOut
let>IMPath=C:\Program Files\ImageMagick-7.0.9-Q16\magick

Let>RP_WINDOWMODE=0
Let>RP_Wait=1
Let>GFL_SORTTYPE=3


//If Excel file Exists, open it.  If not, create it.
IfFileExists>%XLFile%
  XLOpen>XLFile,1,xlBook
Else
  XLCreate>%XLFile%,1,xlBook
Endif

//Get the list of images.  In this case, GIFs
GetFileList>%ImageLocationIn%\*.gif,files
Separate>files,;,file_names

//Set up the loop variables
Let>k=0
Let>TopPos=10
Let>LeftPos=10

//Start the loop
Repeat>k
  Let>k=k+1
  let>TheFile=file_names_%k%
  ExtractFileName>%TheFile%,FileNameOnly

  //Resize and copy your image
  Let>IMcommand=convert %TheFile% -resize 50x50 %ImageLocationOut%\%FileNameOnly%
  runprogram>%IMPath% %IMcommand%

  //Insert and move image in Excel
  LabelToVar>insert_and_move,ImageCode
  XLRunCode>xlBook,ImageCode

  //Change the positioning so they're not all on top of each other
  Let>LeftPos=LeftPos+50
Until>k,file_names_count


//This vba is a recorded Excel Macro, with Macro Scheduler Variables edited in
/*
insert_and_move:
    ActiveSheet.Pictures.Insert("%ImageLocationOut%\%FileNameOnly%").Select
    Selection.ShapeRange.IncrementLeft %LeftPos%
    Selection.ShapeRange.IncrementTop %TopPos%
*/
Hello Dorian,
I created the vb code in excel and it looks like this:
Sub insert_and_move()
Dim ws As Worksheet
Dim targetCell As Range
Dim picture1 As Picture

Set ws = ActiveSheet
Set targetCell = ws.Range("A2")
Set picture1 = ws.Pictures.Insert("%ImageLocationIn%\%FileNameOnly%")


With picture1
.Height = targetCell.Height - 15
.Top = targetCell.Top + (targetCell.Height - .Height) / 2
.Left = targetCell.Left + (targetCell.Width - .Width) / 2
End With
End Sub
The goal is: I have multiples pictures those need to be inserted into cell A2,A3.....
In my scripts with MS I do
let>ImagelocationIn = C:\pictures
Then loop to extract file names as you gave me an example. The question is how to let the vb change the cell from A2 to A3 A4 to follow the loop?
Can I do: Set targetCell = ws.Range("A%k%") in the vb?
Thanks

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Resize Images

Post by Dorian (MJT support) » Sat Apr 11, 2020 1:26 pm

Both of these work. Just remember you'll have to assign the variables for col/row in your loop.

Code: Select all

Let>Col=A
Let>Row=2
Set targetCell = ws.Range("%Col%%Row%")

//Or  
Let>ColRow=A2
Set targetCell = ws.Range("%ColRow%")
Yes, we have a Custom Scripting Service. Message me or go here

nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

Re: Resize Images

Post by nodochau » Sat Apr 11, 2020 1:44 pm

Thanks Dorian,
If we create a VBA in excel then how do we save that file? just a regular excel file (xlsx)? or excel file with macro enable (xltm)?
Or create a VBA then copy that into MS then just open the regular excel file?
I tried your code but it does not work for me. I am sorry, maybe I missed something. I figured out that the VBA can resize and position pictures very quick by using this:
With picture1
.Height = targetCell.Height - 15
.Top = targetCell.Top + (targetCell.Height - .Height) / 2
.Left = targetCell.Left + (targetCell.Width - .Width) / 2
End With
But I don't know how to let XLRunCode work on my side :(. I am working on VBScripts and use VBRun. It worked but if the XLRuncode works then the problem will be a lot easier though..I am using MS 14
Thanks

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts