Solved

Manipulate a .jpg from an Excel list of files?

Posted on 2013-10-23
26
626 Views
Last Modified: 2013-12-20
If I have a list of files in a column of an Excel spreadsheet, has anyone used VBA to do some routine edits to a .jpg?

What I am looking for is to do two things.  One is to change the size, (example would be a 900 x 600 pixel .jpg down to a 300 x 150) and the other is to get a general RGB number or a general color number that the image produces, added to a column in Excel or to appended it to the file name when resaving.

Has anyone used VBA on an excel list, to open a file in a different program, like Photoshop or one that could get us the data needed, adj it and resave it?  Do it again for the next one in the list... etc.

Looking for some direction..  The file attached was provided by this Excel area, and works nicely to rename a list of files from a specific folder.  It contains the folder path to save back to after the two tasks above are completed, but to do this for over 20K files would took weeks, perhaps months.

Please advise and thanks. -R-
FileRename.xlsm
0
Comment
Question by:RWayneH
  • 14
  • 12
26 Comments
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
>Has anyone used VBA on an excel list, to open a file in a different program, like Photoshop or one that could get us the data needed, adj it and resave it?  Do it again for the next one in the list... etc.

There are several parts to this.  The easier one is to use Excel to launch another application, possibly with a set of parameters.  The basic command would be something like:

Shell "Calc"

when this is run from VBA, the Windows calculator will open.

However, Calc is typically in the Windows "path" list so it is easily found.  To do the same with, for example, MSWord, you'd need to put the full path like this:

Shell "C:\Some Apps\Microsoft\Office\Word\MSWord.exe"

When launching with parameters, such as to have the app open a file, you would need to understand how the app uses command line parameters, which might be something like this:

Shell "C:\Program Files\Adobe\PhotoShop.exe /file=""My file.png"""
(Note the doubled and tripled quotes so that the file name is surrounded by quotes when sent to the Shell object (aka the Window Command line processor.)  There might be some additional command-line switches (like /file) for other things too.

If these are not enough, for example if you want Excel to continue to communicate with the application via "automation", you'll need to use some Windows APIs to have Excel launch the app in a command shell and retain an ID to the process so it can continue to communicate with it.  This is sufficiently more complex (and I don't have access to the code for that readily available right now.)  Once you start going in this direction, you may be better off going toward VB.Net, which has a much richer set of tools for managing such things.
0
 

Author Comment

by:RWayneH
Comment Utility
wouldn't the file listed in the spreadsheet, just get launched?  If the file is C:\TEST\TESTFILES\Test1.jpg  wouldn't just launch whatever program is associated with .jpg?  And gather data by some sort of scrapping..  Perhaps others may have more insight to this?  I understand the Shell and launching..  what I do not understand is getting the data into Excel.. saving it, or renaming it.  I have a quesiton posted in the PhotoShop area too, but they would not understand the loop to next cell and the next file, until all files are checked.  -R-
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
The file listed in the spreadsheet would be file name used by the application.  Depending on your windows configuration, it may or may not launch the related application.  To be sure, you'd need to pass it in as a parameter such as:

Shell "C:\Program Files\Adobe\Photoshop.exe /file=""" & Cells(1,1).Value & """"

Getting the data to Excel, using automation...let me call in the cavalry to see if anyone can dig into the archives to find that.
0
 

Author Comment

by:RWayneH
Comment Utility
I am not a scripter either, thus these quesitons about the specific task..

Ok cavalry would be great.  It seems to me that if you can get the value in PS, it should be able to be passed back to Excel to put value in a cell different checking the next file or within a filename save_as suffix.  Also this really is not that new, and I would have thought that someone, somewhere has done this already to chg and large qty of files all at the same time.  -R-
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
So far no response from the "team"...let me see about digging up something over the weekend.
0
 

Author Comment

by:RWayneH
Comment Utility
Ok sounds good.  I would much rather do this from the Excel side then the PhotoShop side, but was not sure that it was possible.  Sounds like it is.  I have in the past, opened files from within Excel and manipulated them, imported tabs of imformation and saved them, but those where files from within the Office pkg.  I have heard of others doing this same thing with non-MS Office applications but never did it myself.  We will see, and thanks. -R-

PS: Any sample .jpg file would work, they are flat files of color samples.  If we can get the RGB color number added to the filename, a prefix of an "_" would be awesome so we can parse that out.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
The biggest challenge I will have at this point is that I don't have Photoshop installed so I can only guess what commands it uses for automation.  However, I can guide you through the process so you should be able to figure it out.  Meanwhile, I'll get you the code so at least the automation can begin.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Here's something that might get you started:
Private Const SW_MAXIMIZE = 3
Private Const SW_SHOWMINIMIZED = 2
Private Const SW_SHOWNORMAL = 1

Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" _
            (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Sub LaunchAndControlIE()
    'reference "Microsoft Internet Controls (ieframe.dll)", and
    'cast ie as "InternetExplorer" if you wish to use intellisense
    Dim ie As IWebBrowser2
    'Dim ie2 As InternetExplorer' this also works
    Set ie = CreateObject("InternetExplorer.Application")
    
    ie.Visible = True
    ie.navigate2 "www.dogopoly.com"

    apiShowWindow ie.hwnd, SW_MAXIMIZE

    AppActivate Application.Name & " - " & ActiveWorkbook.Name, True
    MsgBox "Click OK to close IE", vbOKOnly Or vbInformation
    ie.Quit
    Set ie = Nothing
End Sub

Open in new window


See if this works.  If so, then try adding a reference to Photoshop (in VB, menu Tools | References, then look for Photoshop and select the checkbox.)  From there, you might be able to find it in the list if you go to a new blank line near the top of the sub and type in "Dim PShop As" then hit the space bar and look through the list.
Doing that, we can add some code to manage photoshop.  If not, we'll need to launch Photoshop and manipulate it through a different API (which I'm having trouble finding at the moment.)
0
 

Author Comment

by:RWayneH
Comment Utility
Fails on line 5...  Honestly I am not sure how to approach this..  I was looking for someone that has done this before.  Perhaps this needs to be approached from the PhotoShop side.  Get it to work on one file first.  After that post to Excel side to see if there is a way to pull the files names from there instead of doing them one at a time..

I am having a hard time following the code posted, unless it is commented.  -R-
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Hmmm...line 5 shouldn't be a problem unless you're missing the library/dll, and "user32" is just one of those basic ones.  If that doesn't work then the other technique of launching Photoshop then linking to it and controlling it probably won't work either.

Another approach is more flaky: just have VB send commands to photoshop and hope that the commands are received okay.  You could try something like this:

AppActivate "Photoshop ...", True' The title of the Photoshop app, including the file name

SendKeys "%FS" ' send an Alt-F then S (while will probably activate the File menu then save.

Likewise, you could then start sending the necessary key-strokes to perform the action you want.
The biggest problem is that there's no timing control so the keys are sent and you have to hope that the application captures them without any being lost.  For relatively simple things, it can work, but I suspect this one will not work well.

--
Does Photoshop offer built-in automation tools anymore?  They used to support VBA but I think they took it out.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
BTW
Maybe a better approach will be to use VB.Net or C#.Net to handle this task.  It can relatively easily open Excel to read a list, then perform those automation pieces in Photoshop (assuming they are supported) without the hassles of things like Declaring Function APIs.  For that you may want to extend the reach of this question in the Photoshop and VB.Net / C#.Net topic areas.

While you're at it, you really don't need Photoshop since .Net can pretty easily manipulate JPGs.  Hmmm...technically, Excel VBA can do that too if you want to take this question in a different direction.
0
 

Author Comment

by:RWayneH
Comment Utility
If this could all be handled in VBA that would be great and this was the intent of the original question.  The two main tasks.  Chg the image size of every file in the folder to make sure it is specific size (set by user), and second, grab the RGB setting for the image so it can be grabbed together with like color numbers. -R-
0
 

Author Comment

by:RWayneH
Comment Utility
not needing PhotoShop would be awesome.. and the RGB info does not really need to be in the filename.. I was just trying to figure out how to gather that information to use.  To sort by a range that are all reds, oranges, blues,  etc.  -R-
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
It's been a few years since I've done the picture loading/resizing, and may still require an API with VBA so a .NET solution may still be the way to go for this...will check when I have access to the right computer.

BTW I'm not sure what the RGB requirements are since I don't have too much experience with the way Photoshop does that.
0
 

Author Comment

by:RWayneH
Comment Utility
RGB maybe the wrg term, but if you analyze a flat color file, it can get you a number that means that is a red, that is a blue this is a green etc.  We want to group the color together.

-R-
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
So in .NET, it's easy enough to grab an image and "dissect" it by examining each pixel and determining the magnitude of each of the RGB components (from 0-255) plus the transparency, if applicable.

In Excel, it requires API's, which can be a pain since you have to check to see if the system is 32-bit or 64-bit processor and apply the appropriate function, although it's a matter of adding both then surrounding them with a compiler directive such as something like this
#IF win64
   ' 64-bit function to check pixels in an image
#ELSE
   ' 32-bit function to check pixels in an image
#ENDIF

At this point, I'm not sure how this may transfer over to Windows 8's version of Office 2013.

--
Again, the approach I would use is to create a VB.Net project, have it open the Excel file to read the path names, then use that to open each of the jpg files and maybe re-save them according to your desired changes.  Do you have the 2010 VB.Net Express edition, which you  can download from Microsoft.com for free (with a free account)?
0
 

Author Comment

by:RWayneH
Comment Utility
No I do not have 2010 VB.Net Express edition..  do you have a download site? -R-
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Go here: http://www.microsoft.com/visualstudio/eng/downloads

Scroll down to the gray section and pick: Visual Basic 2010 Express
Click the Install Now button and follow the directions.
0
 

Author Comment

by:RWayneH
Comment Utility
Autofiler on MasterList returned a blank page.  Column C, on that pg is the one that will declare the ones that need to chg font.  We need to return the MasterList to it original state after TemplateLayOut is chged.  If there is no red font in column C, exit sub should be one of the first checks, correct?  Some more observations on the purposed script.  -R-
0
 

Author Comment

by:RWayneH
Comment Utility
disregard last post... posted to wrg one.. sorry. -R-
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
OK...would you like me to build the VB.Net version and send you the code before you install?  That would let you examine it to see how it works.
0
 

Author Comment

by:RWayneH
Comment Utility
Yes, that would be a great idea.. prior to install and you allow for a test to see if it works would be good.  Are you talking it doing all items? The pixel resize, the color number, and the renaming?  Please advise and thanks. -R-
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Let me see if I can do it in stages for you.  Since I only have limited time for this, it may take a while to get all the parts, but at least you can see whether you want to pursue that direction and I could always give you tips on how to get there (since I don't have access to my production machines as frequently as I'd like).
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
Comment Utility
Here's a starting file. (I can't upload an exe here but can put a path to where you can find it)

1. You pick a path where the files will be (we can change that to read an excel file if needed, but this might just be more flexible.)  This will load a list of files in that folder.
2. Select a file that you'd like to examine. If it's a readable image file, the image will appear next to the list.
3. Double-click the file and it will examine the RGB components.  This will probably need a bunch of work.

I've also attached a file of the VB code.  If you want to work on the project yourself, I can attach all of the parts.  Here's a sample of some of the VB code:

    Private Function getRgbInfo() As String
        Dim RedCount As Int64 = 0
        Dim GreenCount As Int64 = 0
        Dim BlueCount As Int64 = 0

        Dim lPixelRow As Long
        Dim lPixelColumn As Long

        Dim pic1Image As Bitmap = picImage.Image
        Dim pixelColor As Color

        Me.Cursor = Cursors.WaitCursor
        For lPixelRow = 0 To pic1Image.Height - 1
            For lPixelColumn = 0 To pic1Image.Width - 1
                pixelColor = pic1Image.GetPixel(lPixelColumn, lPixelRow)
                RedCount += pixelColor.R
                GreenCount += pixelColor.G
                BlueCount += pixelColor.B
            Next
        Next
        Me.Cursor = Cursors.Default
        Return "Red=" & RedCount & vbNewLine & "Green=" & GreenCount & vbNewLine & "Blue=" & BlueCount
    End Function

Open in new window

Sample image of form.frmMain.vb
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Did this help at all?
0
 

Author Closing Comment

by:RWayneH
Comment Utility
Yup..  this helped a lot.  Just forgot to close question. Sorry -R-
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now