R
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
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