Writing text file to a sharepoint folder with VBA

Question

I am using the VBA in MS Project and I am using the following piece of code to create a text file in a folder on my desktop and also in a folder on SharePoint when there is the need.

    Dim t                       As Task
    Dim td                      As TaskDependency
    Dim str                     As String
    Dim sep                     As String
    Dim i                       As Long
    Dim fso                     As Object
    Dim f                       As Object
    Dim SharepointAddress       As String
    Dim LocalAddress            As String
    Dim Address                 As String
    
    ' Create Local and SharePoint address.
    LocalAddress = Environ("USERPROFILE") & "\Desktop\Test"
    SharepointAddress = "https://Company.sharepoint.com/the_rest_of_the_path"
    
    ' Preparing the sharepoint address
    SharepointAddress = SharePointURLtoUNC(SharepointAddress)
    
    ' print the sharepoint address to immediate window for checking
    Debug.Print SharepointAddress
    
    ' Use the Address variable for switching between the local address and sharepoint address
    Address = SharepointAddress
    
    ' If the user pressed the cancel button exit
    If MsgBox("Your project data Is being exported. Press OK To continue", vbOKCancel) = vbCancel Then
        Exit Sub
    Else
    
    ' Create a fso object
        Set fso = CreateObject("Scripting.FileSystemObject")
 
    ' If the folder exists write the text file to the local or sharepoint address
        If fso.folderexists(Address) Then
            Address = Address & "\mydata.txt"
            Set f = fso.opentextfile(Address, 2, True, -1)      ' The problem is here
        Else
            MsgBox "Connection Is Not established, please try again later"
            Exit Sub
        End If
        
    End If

I can easily create the file on the folder on my desktop but the problem I face is that when it gets to the SharePoint folder, then the file gets created most of the time but there are times that it does not get created though there is a connection to SharePoint folder, and I am browsing it in my explorer.

I don't know what I have done wrong.

I changed the SharePoint address with the SharePointURLtoUNC function that I found here but I still have this problem.

I would be grateful if someone could help.

In the above code, as it stands, you only create a local file, never a SharePoint file ... is this your full code? — JohnM, Jun 25 '23 at 13:33

Writing text file to a sharepoint folder with VBA

0 Answers0