Opening Shared Excel documents from Access (2 Viewers)

[M3opa]

Hi All,

I am having a bit of a unique problem with a database I am trying to create to improve processes at my work. I would like to open a shared Excel document using a button click and whilst this works perfectly on my own computer, the moment it is opened on a colleagues computer, the code breaks down due to how the file path on our computer system works. (it goes C:\Users\UsersName\SharepointDocuments). My current code is as per the below but please bare in mind that I am very new to access and may not understand complicated responses about specific VBA!

Function OpenExcelComponentShortageR()

    Dim MyXL As Object
    
    Set MyXL = CreateObject("Excel.Application")
    
    With MyXL
        .Application.Visible = True
        .Workbooks.Open "C:\Users\(MyName)\(Company Name)\(Company Name) Shared Documents - Documents\Planning Reports - under construction\(Company) CPP Files\Other Excel Files\Component Shortage Report.xlsx"
    End With

End Function

I hope the above makes sense, Let me know if you need further information! I am looking for a solution so that it will open on anyone's computer!

With Thanks,

M3opa

 

[Gasman]

Get the username from Environ() function and amend path to suit.

 

[M3opa]

Thank you. I will learn about this function later and see how I can amend my code to suit.

Am I right in thinking that I would "Dim UserName as String" and then concatenate that variable and the rest of the file path together?

 

[Gasman]

Yes, that would be the way I would do it.

 

[Pat Hartman]

If the documents are shared, how can they be on your c: drive?

 

[M3opa]

@Pat Hartman, I am not sure. It might be the way that the Sharepoint folders are set up. When I copy the file path from my computer, it starts from my C: drive, if the same link is copied from a colleagues computer it starts with theirs. I do not know enough about computers to be able to guess why it is like this.

@Gasman, your solution appears to have worked. I added the Dim statement at the top of my code above and then used concatenation to create a variable file path. The spreadsheet still opens on my computer with this new code, I just need to test it on someone's else's computer tomorrow now. Thank you again for your help.

 

[Pat Hartman]

I've never used SharePoint so I wasn't aware of that quirk. Keep in mind that when Access opens a shared document, it might lock others out. Access does not play well with others when "sharing" files.

 

[M3opa]

Oh no, I was not aware that users might get locked out. I will have to test and see what happens. Thank you for the heads up!

 

[The_Doc_Man]

There is also the matter that EXCEL itself doesn't like to share workbooks either. It has varied a bit from version to version so it bears checking, but the last time I heard about it, even two direct users of Excel cannot open the same workbook at the same time when both want to update the file. I know for a hard fact that for Office 2007, two Access users could not share an Excel workbook opened through an Excel Application Instance. The second such user would get a "File Locked" message and their Access program would have difficulties due to that failure. I "inherited" that poorly designed database and had to work like heck to get it to where it was properly shareable.

 

[raziel3]

From what I know, Access cannot link to an external (non-unc path) Excel file. The file needs to be in the XML format. Once the Excel file is on your local PC you can link to it, no problem.

Is the file being shared through a SharePoint path or are the users opening the file on the LAN? These are 2 very different things. If the file accessed over LAN with the sharepoint folder being "shared" the file will become locked. If you have shared the file using a Sharepoint link, everyone can work on it at the same time.

But even this is iffy. The file can get corrupted. Sharepoint is great but still kinda buggy.

 

[M3opa]

@The_Doc_Man, this seems very strange to me as there are many shared spreadsheets at my work that have all been working fine for quite some time.

@raziel3, I believe everyone is accessing it through a sharepoint path.

The spreadsheet in question is not one that everyone edits, it is a reference point for checking what component shortages there are in the company. The data in the spreadsheet is updated automatically based on the reports from a program we use called WINMAN. Not sure if this makes a difference though. :/

Currently, I will get a colleague to try the link through access again tomorrow now that I have updated the code and then try opening it myself as well whilst they still have it open on their laptop to see if I get locked out or not.

I will update here with the results either way so as to conclude the thread.

 

[Gasman]

@The_Doc_Man, this seems very strange to me as there are many shared spreadsheets at my work that have all been working fine for quite some time.

@raziel3, I believe everyone is accessing it through a sharepoint path.

The spreadsheet in question is not one that everyone edits, it is a reference point for checking what component shortages there are in the company. The data in the spreadsheet is updated automatically based on the reports from a program we use called WINMAN. Not sure if this makes a difference though. :/

Currently, I will get a colleague to try the link through access again tomorrow now that I have updated the code and then try opening it myself as well whilst they still have it open on their laptop to see if I get locked out or not.

I will update here with the results either way so as to conclude the thread.

We used to use Shared Excel workbooks in Lloyds bank. They tended to get corrupted often as we were all updating them, albeit on different rows, sheets.

 

[M3opa]

Morning All, I can confirm that all is now working thanks to the Environ Function! I can also confirm that both myself and my colleague were able to be in the spreadsheet at the same time.

Thank you all for you help on this. It has been an excellent learning exercise for me.

 

[Gasman]

If the excel workbook does not need to be updatable then

open excel file read only vba - Google Search

I have used shared workbooks in the past when working for a bank, but they would corrupt quite often and never tried it via VBA, just normal user UI.

 

[M3opa]

Thank you. I think I will look into that as some added protection then.

 

[Cronk]

See Microsoft article on co-authoring

I was of the opinion that a spreadsheet was read only for second and subsequent users until an Excel guru proved me wrong.

 

[The_Doc_Man]

@The_Doc_Man, this seems very strange to me as there are many shared spreadsheets at my work that have all been working fine for quite some time.

@raziel3, I believe everyone is accessing it through a sharepoint path.

The spreadsheet in question is not one that everyone edits, it is a reference point for checking what component shortages there are in the company. The data in the spreadsheet is updated automatically based on the reports from a program we use called WINMAN. Not sure if this makes a difference though. :/

Currently, I will get a colleague to try the link through access again tomorrow now that I have updated the code and then try opening it myself as well whilst they still have it open on their laptop to see if I get locked out or not.

I will update here with the results either way so as to conclude the thread.

As I said, it has varied from version to version of Office. Notice also that Gasman (post 12) reports sharing with corruption for shared Excel sheets. There is a lot of anecdotal evidence that read-only sharing doesn't have the same problems that read-write sharing does.

Further web research suggests that if you open a shared workbook for co-authoring, you might have better results. This is new to me, though it might not be brand new. It has been a while since I have had to work with Excel. The last time I did so was 2007, and several new versions of Office have debuted since then.

EDIT: I see that Cronk beat me to it on the issue of co-authoring.

 

[M3opa]

Apologies to bring up this old thread but I am having problems getting the ENVIRON function to work all the time.

Whilst the code works fine to open up a shared spreadsheet on someone else's computer now, I have one bit of code which kills the old exported spreadsheet, exports the updated query and data into the place where the old file was and then open up the excel spreadsheet that is connected to said exported file. (so that the spreadsheet being opened is always opened with the latest data information once the user refreshes the data connection within the spreadsheet.)

The code is as follows:

Private Sub Command3_Click()

    Dim UserName As String
    UserName = Environ("USERPROFILE")
    
    Kill UserName & "\-company name-Limited\-company name- Group Shared Documents - Documents\Planning Reports - under construction\-company name- CPP Files\Excel Exports\FIN Production Plan.xlsx"
    DoCmd.OpenForm "PlanningF"
    
    DoCmd.OutputTo acOutputQuery, "FinProdPlanExportQ", acFormatXLSX, UserName & "\-company name- Limited\-company name- Group Shared Documents - Documents\Planning Reports - under construction\-company name- CPP Files\Excel Exports\FIN Production Plan.xlsx"
    DoCmd.Close acForm, "PlanningF"
    
    Dim MyXL As Object
    Set MyXL = CreateObject("Excel.Application")
    
    With MyXL
        .Application.Visible = True
        .Workbooks.Open UserName & "\-company name- Limited\-company name- Group Shared Documents - Documents\Planning Reports - under construction\-company name- CPP Files\CPP Production Plan SheetM.xlsm"
    End With

End Sub

The code seems to break at exporting the query. It works fine on my computer, but my colleagues will not accept the code for some reason. I know it breaks here because the kill command has always executed and I have to manually run the export again to fix it.

Any idea what could be wrong with the code here considering it works fine on mine?

 

[Pat Hartman]

Do you get an error message?

 

[M3opa]

yes, something about not finding the file path. C:\users\MyName\rest of the file

It is as if the export is ignoring the ENVIRON function completely