Resize Images
Moderators: Dorian (MJT support), JRL
Resize Images
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
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
- Dorian (MJT support)
- Automation Wizard
- Posts: 1350
- Joined: Sun Nov 03, 2002 3:19 am
- Contact:
Re: Resize Images
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
- Dorian (MJT support)
- Automation Wizard
- Posts: 1350
- Joined: Sun Nov 03, 2002 3:19 am
- Contact:
Re: Resize Images
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
Re: Resize Images
Thank you Dorian.
Re: Resize Images
And do you have the easiest and fastest method to insert images into Excel spreadsheet?Dorian (MJT support) wrote: ↑Wed Mar 25, 2020 4:15 pmSomething 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
![Smile :)](./images/smilies/icon_smile.gif)
- Dorian (MJT support)
- Automation Wizard
- Posts: 1350
- Joined: Sun Nov 03, 2002 3:19 am
- Contact:
Re: Resize Images
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
- Dorian (MJT support)
- Automation Wizard
- Posts: 1350
- Joined: Sun Nov 03, 2002 3:19 am
- Contact:
Re: Resize Images
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.
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
Re: Resize Images
Thanks, I tried it and it worked for me as well, it's great!
Re: Resize Images
Hi Dorian,Dorian (MJT support) wrote: ↑Wed Mar 25, 2020 4:15 pmSomething 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
I tried to run the code but it did not work. But I could run in cmd manually
![Sad :(](./images/smilies/icon_sad.gif)
- Marcus Tettmar
- Site Admin
- Posts: 7380
- Joined: Thu Sep 19, 2002 3:00 pm
- Location: Dorset, UK
- Contact:
Re: Resize Images
I think some quotes are needed:
RunProgram>"%IMPath%" convert "%TheFile%" -resize 100x100 "%TheFile%"
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?
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?
- Dorian (MJT support)
- Automation Wizard
- Posts: 1350
- Joined: Sun Nov 03, 2002 3:19 am
- Contact:
Re: Resize Images
This is worth delving in to. ![Smile :)](./images/smilies/icon_smile.gif)
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 :
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 :
![Smile :)](./images/smilies/icon_smile.gif)
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
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
Re: Resize Images
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![Smile :)](./images/smilies/icon_smile.gif)
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
![Smile :)](./images/smilies/icon_smile.gif)
Re: Resize Images
Hello Dorian,Dorian (MJT support) wrote: ↑Thu Mar 26, 2020 2:19 pmSupport 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% */
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
- Dorian (MJT support)
- Automation Wizard
- Posts: 1350
- Joined: Sun Nov 03, 2002 3:19 am
- Contact:
Re: Resize Images
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
Re: Resize Images
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
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
![Sad :(](./images/smilies/icon_sad.gif)
Thanks