Solved

Open a file using VBA

Posted on 2010-11-29
20
809 Views
Last Modified: 2012-05-10
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).

0
Comment
Question by:mtrussell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
  • +1
20 Comments
 
LVL 10

Accepted Solution

by:
VTKegan earned 500 total points
ID: 34229646
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
 
LVL 10

Expert Comment

by:VTKegan
ID: 34229649
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
 

Author Comment

by:mtrussell
ID: 34229724
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:VTKegan
ID: 34229783
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 34229825
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
 
LVL 85
ID: 34229827
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
 

Author Comment

by:mtrussell
ID: 34229993
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
 
LVL 10

Expert Comment

by:VTKegan
ID: 34230015
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
 

Author Comment

by:mtrussell
ID: 34230023
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
 

Author Comment

by:mtrussell
ID: 34230055
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
 
LVL 10

Expert Comment

by:VTKegan
ID: 34230102
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 34230181
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
 
LVL 10

Expert Comment

by:VTKegan
ID: 34230195
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
 

Author Comment

by:mtrussell
ID: 34230835
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
 

Author Comment

by:mtrussell
ID: 34230931
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
 

Author Comment

by:mtrussell
ID: 34230987
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
 
LVL 10

Expert Comment

by:VTKegan
ID: 34231035
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
 

Author Closing Comment

by:mtrussell
ID: 34231120
Thanks!
0
 

Author Comment

by:mtrussell
ID: 34231135
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question