Changing file path to shared drive path

[Excelquestion35]

Hi all,

Having some issues with with my macro to work on a shared drive. I already tried changing the file path to the UNC structure but I keep getting the message that the file can not be found and therefore not be processed.
The file path of the document on the shared folder is as follows: C:\Users\bw\DPD\RvD- RPA-_UserID\WIP\Kronos Centraal Formulier v3.2 - Template (zonder check) - RPA versie.xlsm

Any clue on what the right file path should be? (skronos)

Below is the part of the code that concerns the file:

Dim src   As Worksheet
     Dim tgt   As Worksheet
     Dim filterRange As Range, PasteRange As Range
     Dim copyRange As Range
     'Dim lastrow As Long
     Dim customer As String
     Dim Kronos As Workbook

     Set src = Workbooks("Copy of Site overview (003) - 2.xlsm").Sheets("FLMs")
     customer = Workbooks("Copy of Site overview (003) - 2.xlsm").Sheets("FLM-change2").Range("G17")

     On Error Resume Next
     skronos = "\\DPD\RvD- RPA-_UserID\WIP\Kronos Centraal Formulier v3.2 - Template (zonder check) - RPA versie.xlsm"     'path en filename Kronos
     sp = Split(skronos, "\")                                   'splitten op "\"
     Set Kronos = Workbooks(sp(UBound(sp)))                     'workbook Kronos
     bclosed = (Kronos Is Nothing)                              'Kronos is niet open
     If bclosed Then
          Set Kronos = Workbooks.Open(skronos)
          If Kronos Is Nothing Then MsgBox "Het Kronosformulier kan niet worden gevonden.": Exit Sub
    Else: Worksheets("Supervisor (leidinggevende)").Unprotect 'later toegevoegd
     End If
     On Error GoTo 0

 

[rollis13]

If you navigate to the folder (that contains your .xlsm file) and from it's Contest menu you choose Property and then the tab Sharing, is this the shared path you see ?

\\DPD\RvD- RPA-_UserID\WIP

Sorry, no other idea if it isn't.

 

[Excelquestion35]

Yes, somehow I can not find the tab Sharing under properties, but the path file that I see is indeed the one you mentioned.

 

[rollis13]

One last thought, do you have sufficient permissions in the folder (tab Security) to access the file, I mean, if you navigate and launch the file manually, does it open ?

 

[Excelquestion35]

Yes, that is arranged, I am able to open the file without the macro

 

[rollis13]

I tested your macro with my shared path and file and it correctly opened the file. So:
1) try creating your path/file name in macro by Copy/Paste from the shared path/folder/file instead of typing it.
2) maybe your anti-malware doesn't like vba scripts, disable temporarily and try (but can't be this since you stated "file can not be found").

 

[Excelquestion35]

The approach still did not work for me unfortunately. I tried to store the data on a sharepoint folder instead. The file path of this folder is equal for all people who have access.
Is there any way to handle sharepoint files and use them in macros with the code mentioned in the OP?

The link to the file is https://COMPANY-my.sharepoint.com/❌/r/personal/FOLDER OF PERSON/_layouts/15/Doc.aspx?sourcedoc=%7B822719C7-CC0A-4C0A-BD4F-1A815EF26192%7D&file=Copy%20of%20Site%20overview%20(003)%20-%202.xlsm&action=default&mobileredirect=true

 

[rollis13]

Sorry, till post #7 I didn't notice we where talking about SharePoint folders .

 

[Excelquestion35]

We weren't indeed, but the file path turned out to be different when we were using the shared folder (on OneDrive). Therefore, we decided to change to another platform since we couldn't get it to work with the approach suggested.