Solved

Open a file using VBA

Posted on 2010-11-29
20
761 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
  • 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
 
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 47

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 84
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 47

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now