LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1882
Members Online:
2
Guests Online:
93

Total Post History
Posts:
81456
Topics:
18718

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
justanails28

Other

FAQ
All times are UTC [ DST ]

Use Excel as an Image Viewer

Please wait...

Use Excel as an Image Viewer

Postby Emtucifor on Wed Dec 10, 2008 3:41 am

In my job I am constantly having to mess around with images whose paths are stored in a database. So there I am, having done all that I can using automated methods, even to the point of performing OCR on thousands of images and using fuzzy matching and so on. But then there is always some subset that I have to look at individually. Well, I have a run command I've set up where I can press Win-R, just type a few letters, and then paste in the image path and press enter and it will load. But this image view is unconnected to the row I took it from. I have to keep closing the images I open, and it's easy to get the images confused or have to open them again.

I kept thinking about writing my own version of Query Analyzer/Management Studio that could support plugins, and the first plugin I'd write would be an image viewer that could be pointed to a column in a resultset so that as I select columns the image displays. This and many other ideas make me want to do this. But of course I've never exactly had the spare time to do such a thing.

I also often have to deal with a bunch of images because I'm analyzing a problem I've never seen before. There IS no automatic script developed, since I'm the only one who develops these and I have no clue what the problem is. So I'm jumping around looking at images, opening them multiple times, trying to figure out if some are duplicates of others, and so on. I often work with these file in Excel because it has certain row-correlative capabilities with its formulas that become quite difficult right in the SQL Server (that is, tracing chains and seeing what's going on is much easier in Excel when I'm still inventing it all--it's later that I could put it back into the server and automate everything). Today I finally realized that it would in fact be quite simple to use Excel as my poor-man's image viewer. You still have to copy and paste your rowset into Excel, but that at least is a single copy and paste to view many rows' of images, instead of copying and pasting to open each one. And if you are using SQL 2005 and turn on the "copy rowset with column headers" option, it's quite painless to paste rowsets into Excel.

So here's how to use Excel as an ImageViewer for any spreadsheet where you have a column full of paths and you want to see the image that path refers to when you click on the same row.

1. In your desired workbook, highlight the entire column with the paths and click Insert->Name->Define. Type the name Path and click OK. Remember the displayed name of this worksheet.
2. Click Window->New Window. Arrange one window for navigating around and the other to display the image. They can be side by side, or one above the other, or anything you like.
3. In the second window, navigate to a sheet and location in the workbook for the image to display. If you're using two windows, you'll probably want the image to display in its own worksheet.
4. Select the cell you want the upper-left corner of the image to cover, and again do Insert->Name->Define with the name ImageTarget.
5. Go to the VB editor (Tools->Macro->Visual Basic Editor or Alt-F11) and find the workbook in the Project Pane. In the list of Microsoft Excel Objects, locate the sheet name you remembered earlier. Make sure you look at the name inside of parentheses, since it may not be the same as the one outside the parentheses. Double click on that sheet and in the code window paste the following code:

  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.     On Error GoTo SelectionChange_Error
  3.     Static LastRow As String
  4.     Static ImageTarget As Worksheet
  5.     If Target.Rows.Count <> 1 Then Exit Sub
  6.     Application.ScreenUpdating = False
  7.     If ImageTarget Is Nothing Then
  8.         Set ImageTarget = ThisWorkbook.Names("ImageTarget").RefersToRange.Parent
  9.     End If
  10.     If Target.EntireRow.Address <> LastRow Then
  11.         LastRow = Target.EntireRow.Address
  12.         If ImageTarget.Shapes.Count > 0 Then ImageTarget.Shapes(1).Delete
  13.         If Application.Intersect(Target.EntireRow, Me.Range("Path")).Value <> "" Then
  14.             ImageTarget.Pictures.Insert Application.Intersect(Target.EntireRow, Me.Range("Path")).Value
  15.             ImageTarget.Shapes(1).Top = ThisWorkbook.Names("ImageTarget").RefersToRange.Top 'or set these to 0 for top left of the worksheet, slightly faster, though you still need the ImageTarget named range set up so it knows the sheet to put the image on.
  16.             ImageTarget.Shapes(1).Left = ThisWorkbook.Names("ImageTarget").RefersToRange.Left
  17.         End If
  18.     End If
  19.     Application.ScreenUpdating = True
  20.     Exit Sub
  21. SelectionChange_Error:
  22.     Application.ScreenUpdating = True
  23.     On Error GoTo 0
  24.     Resume
  25. End Sub

Now you'll find that as you move around the rows of your worksheet in the first window, the image for the path in that row will display in the second window. Cool!

Erik

P.S. I'd appreciate someone trying this out to see if my instructions work. I couldn't figure out a way to change the source of an image shape, so I had to delete and recreate it. It seems to perform pretty reasonably.

P.P.S This code could be placed in the personal.xls Personal Macro Library so it doesn't have to be added to each workbook you want this functionality with. It would go in a class module with a procedure that accepts a workbook, and has a worksheet object variable declared WithEvents so that it could sink the SelectionChange Event. Then you could insert a button on one of your toolbars or on a custom toolbar that calls code to instantiate the class module and point it to the correct workbook. You could even have a form that lets you select the path column and the image target cell. The one drawback of all this is that any time variables are reset (such as when there's an error or you press Ctrl-Break and then choose End, or you click Stop in the VB editor) Excel would lose the sheet/book reference and you'd have to set it all up again. Let me know if you want this code, since I'm content to just quickly go paste this thing into the worksheet I want it to function in.
Last edited by Emtucifor on Wed Dec 22, 2010 6:30 pm, edited 2 times in total.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: Use Excel as an Image Viewer

Postby Remou on Wed Dec 10, 2008 12:47 pm

I am curious, why not a table linked to Access? There is a lot of code floating around for viewing images in Access.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: Use Excel as an Image Viewer

Postby Emtucifor on Wed Dec 10, 2008 8:10 pm

Because Access doesn't work the same way. If it can do the same thing, it's much slower. Formulas in cells, apply conditional formatting, move contents around cells and insert rows.

By switching between Word and Excel I can do some things that would take many many minutes of programming to accomplish.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: Use Excel as an Image Viewer

Postby archiecoles80 on Wed Dec 22, 2010 1:49 pm

Nice posting you provide such good information. This can help many people.
Thank you.
archiecoles80
Newbie
Newbie
 
Posts: 3
Joined: Wed Dec 22, 2010 1:46 pm
Location: United Kingdom
Unrated

Re: Use Excel as an Image Viewer

Postby archiecoles80 on Thu Dec 23, 2010 8:43 am

I will try but i don't have VB Editor. But when i will find it , i definitely do that.
archiecoles80
Newbie
Newbie
 
Posts: 3
Joined: Wed Dec 22, 2010 1:46 pm
Location: United Kingdom
Unrated

Re: Use Excel as an Image Viewer

Postby SQLDenis on Thu Dec 23, 2010 12:00 pm

archiecoles80 wrote:I will try but i don't have VB Editor. But when i will find it , i definitely do that.



ALT + F11
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Use Excel as an Image Viewer

Postby archiecoles80 on Mon Jan 03, 2011 12:06 pm

Thank you Denis. It works properly.
archiecoles80
Newbie
Newbie
 
Posts: 3
Joined: Wed Dec 22, 2010 1:46 pm
Location: United Kingdom
Unrated