Solved

Using VBA in Excel, how do I trigger a list of TIF files located in different folders on server to play one after another in MS Picture Viewer?

Posted on 2013-01-31
8
353 Views
Last Modified: 2013-02-05
In an Excel spreadsheet, I have a short list of TIF files (filenames & server location paths), each of which is its own folder on a server.  Via a VBA macro hooked up to a user button that says "Play / View All Files In A Sequence", I need to trigger this list to play the TIF files in MS Picture Viewer (probably using a hyperlink field that is dynamic and looks at each file name), one after the other, though they are in separate folders on the server and have to remain in those separate folders.

I am not sure how to write this code to make this work, but I suspect that it has something to do with a For Next loop.

This is a real challenge!  Thank you for your time and help!!
0
Comment
Question by:AvantGuardDog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38843277
on way is to shell out to the file.... it would open in the default viwer for that file type though.

the user would have to close the viewer, then the next one will automatically start ? i.
s that ok ?  and msPV may reload each time, causing delay.
0
 

Author Comment

by:AvantGuardDog
ID: 38844196
Hello robberbaron, thank you for your response.  Unfortunately, I do not know what you mean by shell out to the file, I don't know how to do that, what it is, or how to accomplish it.

To clarify,I am already able to bring up ONE TIF file into MS Picture Viewer from Excel via a hyperlink (this already works, and is a featured part of my system); but now I have a list of such TIF files with their exact file names and associated server folder paths - what I need to do now is -- with one macro button click -- set off a VBA macro that will "play" the entire list (and the list changes in number of records on a daily basis).  So the individual "file viewing" is already possible, how do I "play" the list using VBA?

Thank you!!!
0
 

Author Comment

by:AvantGuardDog
ID: 38844206
Hello robberbaron, thank you so much for your response!

Unfortunately though, I don't know how to do that (shell out to a file), what it is, or how to accomplish it -- I would really like to know how to do it.  VBA 'For Next' loop?  If so, I am not sure how to write one, as I am relatively new to using VBA, maybe beginner to intermediate.  I am on the expert level though, regarding every other feature of Excel, but with VBA, not very experienced beyond basics, and customizing, editing someone else's VBA code.  Any help you could give in this regard would be very much appreciated!

Just to clarify and reiterate, I am already able to bring up one TIF file into MS Picture Viewer from Excel via a hyperlink (this already works, and is a featured part of my system); but now I have a list of such TIF files with their exact file names and associated server folder paths.

What I need to do now is -- with one macro button click -- set off a VBA macro that will "play" the entire list (and the list will change in number of records on a daily basis).  In other words, individual "file viewing" is already possible, but now, how do I "play" the list using VBA?

Thank you!!!

PS: oh yes, you also so helpfully stated: "the user would have to close the viewer, then the next one will automatically start ? is that ok ?  and msPV may reload each time, causing delay."

Yes, defintely, all of that is what I expected, and that would be just great!  Problem is getting that list to play in a sequence from Excel with a VBA macro tied to a button.

Again, thanks!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 38846192
this works for me... takes a while between  loads as it starts command line, then open image with whatever application is associated.

would be possible to replace cmd with the full path to MS PicView, assuming it takes a commandline app.

Each image has to be closed (rather the viewer app has to be closed), before the next image will show.

'---module level declares---
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Const PROCESS_QUERY_INFORMATION = &H400
Private Const STATUS_PENDING = &H103&



Sub PlayList()
    Dim xc As Range, procId As Double
    Dim startapp As String
    Set picList = Range("F3:F6")
    For Each xc In picList
        startapp = "cmd /c " & """" & xc.Value & """"
        proid = ShellSync(startapp, vbHidden)
    Next xc

End Sub

'---
Function ShellSync(action As String, windowstyle As Integer) As Long
''''from VB5 help''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'  The standard Shell function runs other programs asynchronously.
'  This means that a program started with Shell might not finish
'  executing before the statements following the Shell function are executed.
'------------
'  ShellSync start the program asynchronously but waits for completion
'----------------------------------------------------------
' updated by Robb    June 2003
'---module level declares---
'Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
'Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
'Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
'Private Const PROCESS_QUERY_INFORMATION = &H400
'Private Const STATUS_PENDING = &H103&

    Dim hProcess As Long
    Dim lProcessId As Long
    Dim lexitCode As Long

    lProcessId = Shell(action, windowstyle)
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, lProcessId)

    Do

        Call GetExitCodeProcess(hProcess, lexitCode)
        DoEvents: DoEvents
   
    Loop While lexitCode = STATUS_PENDING

    Call CloseHandle(hProcess)

    ShellSync = lProcessId       'return the process handle to mimic Shell
End Function

Open in new window

openpic.xlsm
0
 

Author Comment

by:AvantGuardDog
ID: 38851706
ROBBERBARON !  Your code worked like a charm, I am in awe, am very grateful, and cannot thank you enough!

I would like to ask you about a couple of refinement tweaks, which will really be a piece of cake for you, and you may appreciate as well:

1) My range is not static but rather is dynamic (changing all the time):
     In your code:                    Set picList = Range("F3:F6")
     Currently in my code:       Set picList = Range("J8:J99")

Right now, running the VBA macro with this static range loads the TIF addresses in that range, and then there are many blank rows underneath through Row 99 in that column -- as the macro loads the existing TIF file/drive paths in each cell (maybe there are usually going to be anywhere from 6 to 35 of these files to call) the macro as I had to put a static range of j8:j99 -- "flashes" for about 10 extra seconds as it loads all the blank rows.  

I am wondering if there is ...

1) a way to either shut off screenupdating (FALSE) at the start of the macro and turn it back on (TRUE) completely after all 99 cells of TIF addresses and followng blanks have loaded, so the user never sees that flashing:

Application.ScreenUpdating = FALSE
Application.ScreenUpdating = TRUE

-- or --

2) a way to prevent the blank rows from loading/"playing" after whatever TIF addresses happen to appear in column J starting in cell J8 and may end at cell J12 or cell J25 or cell J45, depending on what user has selected in a previous macro that copies these TIF addresses to this range for playback of all the selections using your code.


The second refinement may or may not apply to this macro, but in general for all macros that might yield an error and cause the macro to stop running, bring up VBA screen and programs, and ask to debug -- which would totally confound any lay person using the macros.  This currently happens with a delete macro (tied to a button) that I have -- if the user puts the cursor in the wrong place to delete a row, and that place happens to be a locked cell (with worksheet protection on), everything "breaks" goes to VBA code and asks to debug.  No good.  I'm wondering if there is any kind of "ON ERROR" command or coding that can be placed at the very beginning of the macro, so if the user does anything wrong, NOTHING will happen, the program will simply go to SUB END.

THANK YOU ROBBER BARON!
0
 

Author Comment

by:AvantGuardDog
ID: 38853198
Hi Robberbaron, instead of using this fixed range:

In your code:     Set picList = Range("F3:F6")
In my code:       Set picList = Range("J8:J99")

I tried this:

Set picList = Range("J" & ActiveCell.Column, Selection.End(x1Down)).Select

I'm wanting to do it this way because I want the program to just pick up the cells with TIF addresses in them, and not all the blanks that follow, which cause endless "flickering" as the MS Picture Viewer is invoked 92 times.  Just setting the picList for just the range that has addresses will only call the MS PictureViewer those few, appropriate amount of times.  But it doesn't work, debugger comes up when I tried this new replacement line of code.  

Robberbaron, why won't this replacement line of code work?  What did I do wrong?
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 500 total points
ID: 38853242
1/ Applciation.screenupdating true ... false would help.  I didnt use it as i was testing
2/ can skip blank rows easy enough
    For Each xc In picList
        if xc.Value <> "" then
           startapp = "cmd /c " & """" & xc.Value & """"
           proid = ShellSync(startapp, vbHidden)
        end if
    Next xc

Open in new window


3/ range update I will have to tets tonight. there will be an solution.  Actually easiest way would be to create a named range within the worksheet and use that. <highlight a range, rightclick... Name a range>
then ...
 
     
set picList = Range("MyImages")

Open in new window

0
 

Author Comment

by:AvantGuardDog
ID: 38855653
Hi Robberbaron, your new piece of vb code above -- to eliminate blank rows beneath the always dynamically changing range in column J -- worked perfectly, and is the perfect solution to the last part of this problem.  Thank you!!!  This entire component -- to play sequences of TIF files from a list of their server names and addresses in Excel, dynamically chosen by a user and constantly changing as a "playlist" -- was a particularly challenging and difficult problem, and your solution is truly extraordinary.  I am very grateful.  You deserved more than the maximum 500 points Experts Exchange allows, for this one!  If I had my way, I would award you 50,000.  Thanks again, Robberbaron.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

628 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