Resize Images

[nodochau]

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

[Dorian (MJT support)]

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.

[Dorian (MJT support)]

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

[nodochau]

Thank you Dorian.

[nodochau]

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?

[Dorian (MJT support)]

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.

[Dorian (MJT support)]

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%
*/

[Ranger762]

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

[nodochau]

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

[Marcus Tettmar]

I think some quotes are needed:

RunProgram>"%IMPath%" convert "%TheFile%" -resize 100x100 "%TheFile%"

[Dorian (MJT support)]

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"

[nodochau]

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]

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

[Dorian (MJT support)]

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%")

[nodochau]

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