Open files from a file register

[RocketRod]

I am using Excel 2007 as a file/document register.
It stores the path and file name in the spreadsheet.
I want to be able to open the selected file - I am using some code in the
workshet to detect when a particular cell with the file name to be selected
is double clicked
this part works fine as shown below

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
' This section opens the selected document
If Intersect(Target, Range("L:L")) Is Nothing Then
Else
Call Opendoc
End If
End Sub


What I need is some code sitting in Opendoc (below) which opens the file.
The file type can be anything such as doc*, xls*, ppt*, pdf* (- perhaps
others?) but it will always be opening the program associated with that file
extension.
Hence I need some code to go in where I have put ########### below


Sub Opendoc()
'open the selected file
Dim directory As String 'an example would be "D:\Extra Documents"
Dim filename As String 'an example would be "bus timetable.xlsx"
Dim file As String
directory = ActiveCell.Offset(0, -3).Value
filename = ActiveCell.Offset(0, -2).Value
file = directory & "\" & filename

If Not DocExists(file) Then
MsgBox "Error!" & Chr(13) & _
file & Chr(13) & _
"does not exist." & Chr(13) & _
"Please check directory and file name" & Chr(13) & _
"update this register." & Chr(13) & _
"Thank you."
Range("A1").Select
Exit Sub
End If


#########


End Sub
---------------------------------------------
Function DocExists(ByVal file As String) As Boolean
On Error Resume Next
If Dir(file) <> "" Then
DocExists = True
Else
DocExists = False
End If
End Function

 

[A possible solution]

The VB equivalent to this C# code should do the trick:

Process p = new Process();
p.StartInfo.FileName = file;
p.Start();

I believe this translates to:

Dim p as Process = new Process()
p.StartInfo.FileName = file
p.Start()

Hope this helps.

 

[RocketRod]

I copied this in

Dim p as Process = new Process()
p.StartInfo.FileName = file
p.Start()

but got a Compile Error
Expected end of statement and the "=" sign is highlighted on the first line
and on the third it says an "=" was expected

 

[RocketRod]

sorry but I don't understand this suggestion at all??

 

[A possible solution]

Ooops. Of course, this code only works from within .NET not with VBA! SORRY
about that. You might need to consider to use .NET for this problem if you
have access to to it. If you do and are interested you can find a lot of good
examples on how to program Excel at the CodeProject web site. I am about to
submit an article describing how to interface Excel and .NET. Please stay
tuned.

 

[RocketRod]

sorry but I have no idea what .NET is
I'm just looking for some code to put in my bog standard Excel macro.

 

[A possible solution]

OK, sorry about the .NET detour. Let me suggest a VBA solution to your
problem. You could run the following macro on the column that contains your
file paths (I assumed that it might be column A, but you can easily change
that). This turns all cells into hyperlinks. Clicking on those cells will now
open the documents the file path points to.

Option Explicit
Sub InsertHyperlinks()
Dim r As Range
Set r = Range("A1")
Dim i As Integer
Dim hyperLink As String
i = 0
Do
r.Offset(i, 0).Select
hyperLink = Selection.Value
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=hyperLink,
SubAddress:="" _
, TextToDisplay:=hyperLink
i = i + 1
Loop Until r.Offset(i, 0).Value = ""
End Sub


Hope this helped,
Rolf

 

[Steve Yandl]

RocketRod,

There are several approaches you can take. You could use the Shell function
in VBA but it launches executable files with the files to be run provided as
arguments. Basically, you lose out on Windows reading the registry to
determine file associations and launching the appropriate executable.

I think the most simple approach is to utilize several objects that are part
of the scripting runtime library that should be available on nearly any PC.
In the example below I use late binding but you could also run the
'References' tool at the top of your VBE window and put a check by Scripting
Runtime.

The example below should be used in place of your sub OpenDoc and you should
not need the function you show in your example. In testing, I found some
applications opened on top of my Excel Window and others behind but didn't
take the time to add code to correct that.


'________________________________________________

Sub OpenDoc()

Dim strFullName As String

If Len(ActiveCell.Text) < 6 Then
MsgBox "Please check for valid file name in cell"
Else
strFullName = ActiveCell.Text
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(strFullName) Then
Set wsh = CreateObject("WScript.Shell")
wsh.Run strFullName
Set wsh = Nothing
Set fso = Nothing
Else
Set fso = Nothing
MsgBox "It appears the file doesn't exist"
Exit Sub
End If
End If
End Sub


'________________________________________________

Steve Yandl

 

[RocketRod]

Steve
soooo close!
this worked for a docx type file in the register but not for either xlsx,
pptx or pdf files (the only other three test files I've set up on the
register so far)

The debug shows this line as the problem wsh.Run strFullName
the debug explanation window had the following message
Run time error '-2147024894 (80070002)':
Method 'Run' of object 'IWshShell3' failed

 

[Steve Yandl]

The filesystemobject makes it fairly simple to determine the file extension
after you confirm that the file exists. If the number of file types isn't
too extreme, you could probably use a Select...Case to set up different run
commands based on extension. I'll experiment with a couple other things and
see if there isn't a simple approach that works.

Steve