Forum Discussion

kheldar's avatar
kheldar
Iron Contributor
May 27, 2022
Solved

VBA and SharePoint URL Longer Than 255 Characters

Hello,

 

Is there a workaround to bypass this error?

 

 

Sub CallFile(ByVal File As String)
Workbooks("Master Workbook.xlsm").Activate
Debug.Print File
Workbooks.Open File
End Sub

 

 

This is my macro. The argument that's passed to the macro through a command button is longer than 255 characters and I'm getting an error. URLs that are shorter than 255 characters work without a problem.

 

I'd really appreciate your assistance.

 

 

excel
Macros and VBA
  • kheldar's avatar
    kheldar
    Jun 01, 2022

    kheldar 

    I managed to do it with network drive mapping

     

    my code:

    Public Function SPDrive(Drive As String, Url As String)
On Error Resume Next
Dim objNet As Object
Dim FS As Object
Dim objFolder As Object
Set objNet = Nothing
Set FS = Nothing
Set objFolder = Nothing
Set objNet = CreateObject("WScript.Network")
Set FS = CreateObject("Scripting.FileSystemObject")
Set objFolder = FS.getfolder(Drive)
objNet.RemoveNetworkDrive Drive
objNet.MapNetworkDrive Drive, Url

Debug.Print Url
End Function
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 27, 2022

    kheldar 

    Maybe you could put in a memo field and call the hyperlink using the "FollowHyperlink Method".

    You start this either by clicking in the text field or by using the command button.

    Everything only theoretically, I do not know such a long link to try :).

     

    or

    Split hyperlink into two cells. write a part in A1 and a part in A2, then you could put the hyperlink together and call it.

    =HYPERLINK(A1&A2,"ClickMe")

     

    or

    look at this link there is a VBA code that promises to work around it (untested).

     

    Everything only theoretically, have not tried such a long link:).

     

    Add. Info. :

    Microsoft has increased the maximum length for path names in OneDrive for Business and SharePoint Online from 256 Unicode characters to 400.

     

     Hope I was able to help you with this information / links.

     

    NikolinoDE

    I know I don't know anything (Socrates)

    • kheldar's avatar
      kheldar
      Iron Contributor
      May 27, 2022
      I forgot to mention that the reason I'm trying this method is that Online functionalities of the document needs be enabled since all the files will be edited collaboratively.
      • kheldar's avatar
        kheldar
        Iron Contributor
        Jun 01, 2022

        kheldar 

        I managed to do it with network drive mapping

         

        my code:

        Public Function SPDrive(Drive As String, Url As String)
On Error Resume Next
Dim objNet As Object
Dim FS As Object
Dim objFolder As Object
Set objNet = Nothing
Set FS = Nothing
Set objFolder = Nothing
Set objNet = CreateObject("WScript.Network")
Set FS = CreateObject("Scripting.FileSystemObject")
Set objFolder = FS.getfolder(Drive)
objNet.RemoveNetworkDrive Drive
objNet.MapNetworkDrive Drive, Url

Debug.Print Url
End Function
    • kheldar's avatar
      kheldar
      Iron Contributor
      May 27, 2022
      I'll try that now! Thanks. My friend suggested me to use CHDIR and open the file name only. However, chdir returns path not found error
      the path : company.sharepoint.com/Shared%20Documents/Business%20Planning%20-%20Account%20Planning/4.%20Schedule%20Management/2.%20Schedule%20Records/Actual%20Schedules/

Resources