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

 

[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