Help Required! Opening Excel not as easy as i thought!

[Chris1985]

I am trying to open MX Excel using a command button. I have tried using a hyperlink and various codes that i have found online with no luck. The Excel file is being used by Access as it bring data from the spreadsheet and displays it within a subform. (I think this is where the problem occurs) The subform displaying some data from Excel is working fine but when i click my hyperlinked button (To open the excel file) i am met with errors and the file will not open.

Does anyone know a way around this?

Thanks in advance to anyone who thinks they can help

 

[pr2-eugin]

What is the error you are meeting with? Error Number and Error Description might be able to identify what is wrong.

What is the current code you have placed.

 

[Chris1985]

I am currently not using any code just a hyperlink to open the file. Each time i try i get two error messages one saying the hyperlinks can be harmful do you want to continue -> Yes.... Then second error message Cannot access 'file name.xls'

 

[pr2-eugin]

So do you have permission to physically access the file, talking about windows file system here. Do you have access to the Drive/Folder/File?

 

[Chris1985]

Yes i can access the file no problem the file is stored on my desktop with the access files. I have the excel displaying data within the access program which is where i think the problem lies as i the access program i taking data from the excel but when i want to open it, it does not allow me to.

 

[pr2-eugin]

Wait, so you are trying to Open a file that is already been opened by Access application, which you are currently using?

 

[Chris1985]

Yes the Access is bring data from the Excel file but i also need to open the Excel file as well.

 

[pr2-eugin]

You cannot as far as I know !

When the file is opened in Access, it will have exclusive rights. So it cannot be opened as you wish. Unless you use some code to open a Read only copy.

 

[RichP]

I suspect that Paul is correct. You could check by trying to open the file from Excel itself without using the Access command button but with Access open so it also has the Excel file open. If you get the error then that is a good clue as to what is going on

 

[Chris1985]

Is there a way of opening the excel within Access as read only this way i would be able to open and edit the file. Hope that makes sense!
Thanks

 

[RichP]

If you open the Excel file using your command button before opening the linked table from within Access then Access does indeed seem to open the link as read only which is what you need.

However, if the point of having the button is so that people can click on the button if they have to fix something that may defeat the point as they have to see the thing that they want to fix first. In that case you have two options in my view:

1. Open the Excel file before linking to the file from Access. You can keep it hidden and only show it when the button is pressed.

2. Read the excel data into a temporary table and then display it in the form. That way the excel file isn't locked.

I would probably go the temporary table route if it were me

Good luck

edit: there is a 3rd option which is to close the form if the button is clicked but I'm not really sure that is a good option

 

[Chris1985]

closing the form wouldn't be a problem could you give me some instruction where that be code on how to do this? Thanks for any help!

 

[RichP]

Closing the form is a bit difficult because you want the code to close the form to be part of the same command button that opens the excel file. You may therefore get into problems with things happening in the correct order. However try this in the on click code for the command button:


me.recordsource="" 'This should close the link to the excel file
docmd.close

'then your code to open the excel file
application.followhyperlink "C:\pathtoyourfile\filename.xlsx"

Hopefully you get the idea