Open a file using VBA

I have a form with one text field(txtfilename) on it.  I want the end-user to type in the name of a file on the C drive (to keep things simple) and using VBA the file will be opened.  

I want the ability to open any type of file (could be a word document, excel, pdf, etc).

mtrussellAsked:
Who is Participating?
 
VTKeganConnect With a Mentor Commented:
I would use the dialog file picker.  The code below will work for you.
With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select file"
    .AllowMultiSelect = False
    .Filters.Clear
    
    ' Add Filters to the bottom of dialog
    .Filters.Add "All", "*.*", 1
    .InitialFileName = ""
    If .Show Then
        Application.FollowHyperlink .SelectedItems(1)
    Else
    ' If the user selects cancel
    End If

Open in new window

0
 
VTKeganCommented:
Place a button on a form and put this code in the on click event.  This will allow the user to select a file as opposed to having to type it in.
0
 
mtrussellAuthor Commented:
Thanks for this... 2 things...

1  what reference do I need to use the msoFileDialogOpen?  I am getting an error.


2  This is the thing - the end user needs to store the name of the file in the database (for example, the scanned copy of the invoice) for a particular record.  After lengthy discussion the attachment functionality is not going to meet the project owner's deliverable expectations so I need to be able to have the end user type in the name of the file and when it is clicked, the file will open.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
VTKeganCommented:
1. Microsoft Office 12.0 Object Library

I see what you are talking about.  I would do two things then.  

Place one button beside the text box with a caption '...' and have the file dialog picker come up when the user clicks that button.  Then place another button beside that button that says 'Open File'.

For the first button ( '...' ):

With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select file"
    .AllowMultiSelect = False
    .Filters.Clear
   
    ' Add Filters to the bottom of dialog
    .Filters.Add "All", "*.*", 1
    .InitialFileName = ""
    If .Show Then
        Me.txtFileName = .SelectedItems(1)
    Else
    ' If the user selects cancel
    End If
End With


For the second button ( 'Open File' ) use
If Not IsNull(Me.txtFileName) Then
Application.FollowHyperlink Me.txtFileName
Else
MsgBox "No File Selected"
End If

0
 
Dale FyeCommented:
No points please.

You can also go to http://www.mvps.org/access/api/api0001.htm and copy the Windows File Open/Save dialog using the API.

I use the same technique as VTKegan, putting the Application.FollowHyperlink method in the click event of a button.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note this may not work if your users will be running this via the Access Runtime (since the Office 12.0 Object Library may not be available). That may not be a concern for you, but I thought I'd mention it just in case.

If it is a concern, then use the API to show the FileOpen dialog. Here's one way:

http://www.mvps.org/access/api/api0001.htm

There are others, of course, but this seems to be the defacto standard.

0
 
mtrussellAuthor Commented:
This works but it doesnt solve the issue I have.  I want to save the name of the file in a text box in Access and then open the file when a button is clicked.  This opens Explorer but not the file itself.
0
 
VTKeganCommented:
If you used the code in my second post.  It should place the path directory into the text box on your form.  and then when you click the open button it will open the path that is specified in the text box.
0
 
mtrussellAuthor Commented:
For example if I have a file called test.doc (it could be test.xls or test.pdf, the extension is irrelevant) on C:\Documents, I want to type, test.doc (in this case) in the field and if I click a button, the document will open.  
0
 
mtrussellAuthor Commented:
I get that but there could be several hundred files in this folder.  Is there not a way to code saying when a button is clicked, all files will be in this folder and the name of the file is this - so open this folder?

I get the code you sent and it would work great in other areas but in this, I need to open a specific file, going to a specific folder is not enough.
0
 
VTKeganCommented:
The code as written should operate like this.

Assuming on the form there is a txt box called txtFileName.

Click on ... button

Opens the file dialog.  You select the file you want to be selected from the dialog and click open.

For Example C:\Documents\Icon.jpg

Then on your form in Me.txtFileName it should have the path 'C:\Documents\Icon.jpg'.

Then when you click Open File, it should open the file (not folder)  C:\Documents\Icon.jpg in its default application.

Can you explain which of these steps is not happening as described?
0
 
Dale FyeCommented:
as VT mentioned, you need two command buttons if you want to take advantage of the Open/Save file dialog box.  One to select the file and place the file name in your textbox, and the second to actually open the file (with the application.FollowHyperlink method.

0
 
VTKeganCommented:
I think I see what you are saying now.

If all files are in a predetermined directory you can do this.

First include this function somewhere in your code.

Public Function IsFile(fil$) As Boolean
 If Len(Dir$(fil$)) > 0 Then
  IsFile = True
 Else
  IsFile = False
 End If

End Function

OnClick:

strFilePath = "C:\[YourFolderName]\" & Me.txtFileName
If IsFile(strFilePath) Then
Application.FollowHyperlink strFilePath
Else
MsgBox "File Does Not Exists"
End If
0
 
mtrussellAuthor Commented:
OK - this works... now one last step... All the files are in the same spot.  c:\documents for this example... what is the code to use so all I would have to do is type in the file name and VBA would add the 'C:\Documents\' automatically?
0
 
mtrussellAuthor Commented:
Sorry about this - I didn't hit refresh till after sending my question... this makes sense.... ok - I am getting

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

User-defined type not defined
---------------------------
OK   Help  
---------------------------


on the strFilePath = "C:\[YourFolderName]\" & Me.txtFileName  line


I have renamed it to the network path which is: strfilepath = "\\jassrv02\jas-fp$\Documents\" & Me.Text0


0
 
mtrussellAuthor Commented:
Solved my own question - was using the wrong variable...  


I have


Private Sub Text0_DblClick(Cancel As Integer)

Dim strfilepath As Variant


strfilepath = "\\jassrv02\jas-fp$\Documents\" & Me.Text0
If IsFile(strfilepath) Then
Application.FollowHyperlink strfilepath
Else
MsgBox "File Does Not Exists"
End If




End Function


Now I am getting Isfile is not a defined Sub or Function but I have saved this function as a module.  Any ideas?  It looks like this is close.   Thanks for your help on this.

0
 
VTKeganCommented:
Add the line

Dim strFilePath as String

at the top of the onclick event.

and change the line to this

strfilepath = "\\jassrv02\jas-fp$\Documents\" & Me.Text0.Value
0
 
mtrussellAuthor Commented:
Thanks!
0
 
mtrussellAuthor Commented:
EE admin - Why is the grade so low???    It should be much higher!  I gave an A and 500 points.   Please ensure grade reflects VTKegan's input!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.