Open (excel) file from Access 2010

[riteoh]

I'm using Access 2010 and need to write some code that will open an excel file (or word, or powerpoint, or ....) independently of Access.

Hunting around the net I found the following code, but I am getting a compile error when I run it. I'm not good with VBA code - can anybody point out the problem or tell me how to amend this so that on clicking the relevant button, I will then get a prompt to select the file (at this stage excel) that I want to open? Thanks in advance.

Code is :

Private Sub FeesPaid_Click()

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
MsgBox "The path is: " & vrtSelectedItem

Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Sub

[nepotist]

Please use the VBA tags for the code.
Private Sub FeesPaid_Click()

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
MsgBox "The path is: " & vrtSelectedItem

Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Sub
Your code works fine in 2007. Where do you get the error?

[nepotist]

Double Post deleted.

[riteoh]

I'm getting a compile error when the macro is run, stating User-Defined type not defined. The code appears to be stopping at the very first dim statement.

Any ideas? I've tried to run the macro using Access 2003 also, and get the same error.

[hansup]

I'm getting a compile error when the macro is run, stating User-Defined type not defined. The code appears to be stopping at the very first dim statement.

Any ideas? I've tried to run the macro using Access 2003 also, and get the same error.Add a reference to Micorosoft Office X Type Library, where X is the library version. With Access 2003, X = 11.0. I don't know what X is for Access 2010.

To add a reference, open your code module, then choose Tools->References from the main menu.

[hansup]

Instead of adding a reference, I would make these changes:'Dim fd As FileDialog
Dim fd As Object

'Set fd = Application.FileDialog(msoFileDialogFilePicker)
Set fd = Application.FileDialog(3) ' msoFileDialogFilePicker
And if you always want to choose a single file to open, use this (msoFileDialogOpen instead of msoFileDialogFilePicker):Set fd = Application.FileDialog(1) 'msoFileDialogOpen

[riteoh]

Thanks for the help - I am getting the message box advising of the path chosen, and the prompt to open the file
But the file is not opening - is there something else I have to add to the code to actually open the selected file?

[hansup]

You can use Application.FollowHyperlink to open a file with its associated application. The first of these 2 examples opens the workbook in Excel. The second example opens the document from a network share with Word.
Application.FollowHyperlink "D:\wip\temp.xls"
Application.FollowHyperlink "\\CMPQ\export\AffiliationMatches.doc"

[riteoh]

Thanks hansup - I was originally going to have access point to the file I wanted to open in Excel - but the particular file is updated quarterly with a new name - e.g 2010Term1, 2010Term2 .... I don't want to have to change the access code each quarter, preferring that it would allow me to select the file I wanted to open.

Any suggestions anybody?

[hansup]

Your code stores the path for the selected file in the variable named vrtSelectedItem. So use that variable with FollowHyperlink.
Application.FollowHyperlink vrtSelectedItem