Hi,

I want to open in Access a (variable) excel file from a specific directory, say d:\temp

I have written the following code:

Dim xl As Object

currentDir = CurDir
ChDrive ("D")
ChDir ("D:\Temp")
Set xl = CreateObject("Excel.Application")
OpenBoolean = xl.Application.Dialogs(xlDialogOpen).Show
ChDrive (Left(currentDir, 1))
ChDir (currentDir)

However it doesn't seem to work, i get an error at the line "OpenBoolean=xl.."
The error is "application-defined or object-defined error"

i think it may have someting to do with the fact that access doesn't recognize the xlDialogOpen object.

Can anyone tell me how I can solve this?

Many thanks

From a comman button's on click event

FollowHyperlink "D:\temp\" & strFile & ".xls"

where strFile is a String Variable of the Excel filename

Thanks for your reply

But unfortunately you have to manual select the file, the filename is variable, only the directory is fixed. So I want to open the excel open file dialog but starting from a specific directory instead of the standard.

This should help you. Can't remember where I found it but its good.

This may help as well:

http://www.access-programmers.co.uk/forums/showthread.php?t=97787&highlight=dialog

Thanks for your hints.

After a lot more searching this method works fine

Excel.Application.DefaultFilePath = "d:\temp"
Dim x As New Excel.Application
x.Dialogs(xlDialogOpen).Show

but you have to set the defaultfilepath back to default when you're finished