Open Excel File From Form 1

[wonderwhy]

Hello,

I have looked around on Tek-Tips a bit for some help with this but haven't been able to get anything to work yet. Please help with the code to open an excel file from an Access form based on a field's value displayed on the form. I am going to name an excel file (.xls) with the same name as the key field in my form. So, when a command button is clicked I want Excel to open the following path:

H:\home\research\ResearchFund\Accounting

and find a file with the same name as the key field on the Access form. The form name is 'frmProtocol', the command button is called 'cmdOpenExcel' and the field on the form is called 'ProtocolNumber'.

Any help will be appreciated.

Thanks!

Julia

 

[Mike555]

I always do this -

1. Right-click on the command button and select Properties.
2. Enter or browse for the path to the file.

Now, the file you specified will open just like a hyperlink does.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[Mike555]

I forgot to mention - You want to enter the file path under the Hyperlink Address of the button properties.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[wonderwhy]

Thanks for the solution, Mike! I will give that a try. I am interested to know if it would be possible to find the file based on the field on the form, though. If anyone knows how to do this - thanks.

Julia

 

[ethorn10]

try storing the field you want to search for in a variable of the same name. then create a new object (FileSystemObject).

then

object.FileExists("H:\home\research\ResearchFund\Accounting\" & variable_name & ".xls")

this returns true if exists, false if it does not. if it exists, launch it.

 

[ethorn10]

perhaps a better example to be placed in the on_click event of the command button:

Dim fso as Object

Set fso = CreateObject("Scripting.FileSystemObject")

If(fso.FileExists("H:\home\research\ResearchFund\Accounting\" & variable_name & ".xls")) Then
     'launch .xls file
     Exit Sub
Else
     'some message or notification that file doesn't exist in that directory
End if

 

[wonderwhy]

ethorn10: This looks great except that I don't know enough about VB to make it work. I did copy it in to the on_click event, changed 'variable_name' to 'ProtocolNumber' and added a message box after the Else. Now it does display the message box when I click on the button for records that don't have a matching excel file. But for the record that does, it doesn't do anything. It looks like it is missing a line that tells it to open the matching file. I'm not sure what I'm doing...

Thanks for your patience,

Julia

 

[ethorn10]

Ok, I'll post the full code and you can pick out what you need to add to your code:

Dim fso as Object
Dim ProtocolNumber as String

Me!ProtocolNumber!SetFocus
ProtocolNumber = Me!ProtocolNumber!Text

Set fso = CreateObject("Scripting.FileSystemObject")

If(fso.FileExists("H:\home\research\ResearchFund\Accounting\" & ProtocolNumber & ".xls")) Then
     Shell("H:\home\research\ResearchFund\Accounting\" & ProtocolNumber & ".xls", vbNormalFocus)
     Exit Sub
Else
     'some message or notification that file doesn't exist in that directory
End if

There are different options to replace "vbNormalFocus" and they are (taken straight from MS Visual Basic help):

vbHide - Window is hidden and focus is passed to the hidden window. The vbHide constant is not applicable on Macintosh platforms.
vbNormalFocus - Window has focus and is restored to its original size and position.
vbMinimizedFocus - Window is displayed as an icon with focus.
vbMaximizedFocus - Window is maximized with focus.
vbNormalNoFocus - Window is restored to its most recent size and position. The currently active window remains active.
vbMinimizedNoFocus - Window is displayed as an icon. The currently active window remains active.

 

[wonderwhy]

ethorn10,

Here's the code as I have it (I changed the path). I compiled it and everything but the line that starts with 'shell' is OK. I'm not sure what is wrong with that line. Any ideas? I get a Compile Error/Syntax Error message.

J

 

[wonderwhy]

oops, here it is....

Private Sub cmdOpenExcel_Click()
Dim fso As Object
Dim ProtocolNumber As String

Me!ProtocolNumber.SetFocus
ProtocolNumber = Me!ProtocolNumber!Text

Set fso = CreateObject("Scripting.FileSystemObject&quot

If (fso.FileExists("H:\home\clintrial\BRFAccounting\" & ProtocolNumber & ".xls&quot) Then
Shell("H:\home\clintrial\BRFAccounting\" & ProtocolNumber & ".xls", vbNormalFocus)
Exit Sub
Else
'some message or notification that file doesn't exist in that directory
End If
End Sub

 

[wonderwhy]

ethorn10,

With your help, I've finally got it! See below the final code which does work.
*************************************
Private Sub cmdOpenExcel_Click()
Dim fso As Object
Dim ProtocolNumber As String

Me!ProtocolNumber.SetFocus
ProtocolNumber = Me!ProtocolNumber.Text

Set fso = CreateObject("Scripting.FileSystemObject&quot

If (fso.FileExists("H:\home\clintrial\BRFAccounting\" & ProtocolNumber & ".xls&quot) Then
Call Shell("excel.exe H:\home\clintrial\BRFAccounting\" & ProtocolNumber & ".xls", vbNormalFocus)
Exit Sub
Else
MsgBox "No Excel file exists for this record"
End If
End Sub

***********************************
Happy New Year!

Julia

 

[ethorn10]

Congrats. I realized after I posted that Shell wants executables (*.exe), but you got it by passing excel.exe the file as a parameter.

Happy New Year to you too...