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
307 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

708 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

16 Experts available now in Live!

Get 1:1 Help Now