HELP NEEDED.. Beer to anyone who has the answer!! (1 Viewer)

[Chris1985]

I'm currently running a database which is bringing data from an excel file. I want to be able to open this as a read only file as i am having problems opening the (excel) file when the database is running (It will not open when you click the file but does display the file in my database (The file is being displayed in a subform)). I have been told that if i open the file as a read only file this will solve my problems but i am unsure as to what code to use or where to put it?

The reason for wanting to have both viewed is so that the user can be running the database which is displaying the excel file but they can then call the actual file by click a command button which opens the file in excel to be edited!

Thanks for any HELP!!:banghead::banghead::banghead:

 

[Pat Hartman]

Create a bound object frame.
Right-click on it and choose - insert object.
Double-clicking on the object will allow you to edit it from within Access.

 

[apr pillai]

Use the following Command Button Click Event Procedure to open the Excel Database:

Private Sub Command8_Click()

Call Shell("C:\Program Files (x86)\Microsoft Office\Office12\excel.exe C:\[B]mdbs[/B]\Book1.xlsx")
End Sub

The Target file folder name should not have any spaces.

NB: Open Excel file first (either directly or by Clicking the above Command Button), before opening the linked table in database.

 

[Chris1985]

hi apr i have tried what you have suggested this only allowed me to open excel. What i am trying to achieve is to be able to open the specific file but as i am already running the excel file within my database it does not allow me to do this so i am lookinf for a way around this. i thought possible having when the database wants to open extract files from the excel file to do it in read only then have a command button that allows the user to open the same file. is this possible or does it have to be done a different way?

Thanks

 

[Chris1985]

I have already created my bound object so i can see the excel file, this is not the problem the problem lies when i try to open the excel file from its original location (whilst the database is running)
- trying to open the excel file gives me the error: "Cannot access excel file.xls". I get this error also when i create a cmd button trying to open the specific excel file.
- when i shut the database down i am able to open the excel file again.
- I am always able to see the excel file within the database just not able to open it up in excel whilst the database is running

 

[apr pillai]

Check the command line I have given above. I will repeat it here:

Call Shell("C:\Program Files (x86)\Microsoft Office\Office12\excel.exe C:\mdbs\Book1.xlsx")

The command have two segments, first part:

C:\Program Files (x86)\Microsoft Office\Office12\excel.exe

after a space the second part, the excel file path you are trying to open:

C:\mdbs\Book1.xlsx

In the file path part there should not be any space in the folder name or file name. If it does then it will only open Excel Program but not the file you are trying to open.

I tried out what you are trying to do (I am using MS-Access2007). I have opened the excel file first (while ms-access database is open, but the linked Excel table was not opened).

After the Excel file is open I have opened the linked excel Table in Access Database.

I was able to edit the data in Excel File and that change was immediately reflected in the linked table in Access.

I tried it differently. Opened the linked Table in Access first. Next opened the excel file. When it is done on this order the excel file was in read only mode and could not edit the contents.

 

[Chris1985]

i have tried what you have suggested still with no luck i have done everything as explained when i have the excel file open first then open the database i can view both but as soon as i want to edit the excel i'm met with an error "the file is locked" the code you suggested to use only gives me errors as well i have changed the folder and file name so it has no spaces but i still get the same errors i have double checked to make sure i am no typing anything incorrectly and i am quite sure that i haven't.
-Do you know another way?

- Thanks for trying to help

 

[apr pillai]

Check whether the Excel File and Access Database Locations (folders) are included in the Trusted Locations List?

Office Buttons - -> Access Options - ->Trust Center - -> Trust Center Settings - -> Add New Locations.

Add Excel & Database locations, if they are not already present. Then you may try the procedure again.

 

[Chris1985]

Hi Apr, i have tried this with no luck i have tried removing the subform which is importing the excel file and the button then works but, when i put the subform back in it stopped working.

 

[Chris1985]

Does anybody else have any ideas about this problem?

 

[Pat Hartman]

I've never actually had to implement a solution like this but when my apps link to spreadsheets, they lock them even though Access isn't actually updating them. I don't see any way to influence this behavior.

You might consider a design change that lets the users get rid of the spreadsheet entirely and just use the Access app. Alternatively, if the spreadsheet isn't used frequently, have the app import a new copy each time rather than linking to the live file. The import only takes a second or two and hopefully the lock will free immediately once Access closes it.

 

[robertbwainwright]

How dynamic is the sheet? If it is not very dynamic You could just use a make table query to create a copy of the data and use the table in your subform.