Re: Open files from a file register

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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



"RocketRod" <RocketRod@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:386E0542-3A50-4E73-8A59-7E5343945840@xxxxxxxxxxxxxxxx
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" wrote:

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" <RocketRod@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1740B035-E453-4A57-9963-FB4C0A124A24@xxxxxxxxxxxxxxxx
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







.

Relevant Pages

  • Re: Open files from a file register
    ... Sub OpenDoc() ... Dim strFullName As String ... Set fso = CreateObject ... I want to be able to open the selected file - I am using some code in ...
    (microsoft.public.excel.programming)
  • Re: vb.net search network drive
    ... Beware I air-modified it just now to let you specify the extension ... Sub Main ... Private Sub ListAllFiles(ByVal path As String, ... Dim filenames() As String = ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Getting the filename from a path to that file
    ... End Sub ... Function GetRawFileName(FullFileName As String) As String ... > I would like to create a combo box that lists filenames of all files ... > in a certain directory without their extension, ...
    (microsoft.public.vb.general.discussion)
  • Re: Newbie-question: Regexp to match the file extension
    ... > I have a String containing the file extensions that i am looking for ... I split that String up and try to match the ... I would use File::Basename to isolate the extension rather than rolling your ... At the beginning of the sub{} you defined $abortvalue to be 'true' and you ...
    (comp.lang.perl.misc)
  • Re: Office 2007 Document contents must match file extension
    ... file will be opened based on its contents as opposed to the file extension. ... Sub ExtendMyName() ... Dim sFile As String ...
    (microsoft.public.office.misc)