A Review of Sharepoint

Now that I’ve worked with SharePoint for a couple of weeks, it’s time for me to put down what I’ve noticed thus far. I think it has the potential to be really cool but we might need to learn some new habits. I have worked with SharePoint as far back as 2007 or 2008 and the current version is way cooler. But, it bears repeating, some habits may need to be tweaked.

Key Points about SharePoint

From my limited perspective, here’s what I’ve learned about this new version of SharePoint:

• I had hoped that once everybody got on SharePoint, we would all have the same kind of Excel and thus the same approach to doing things such as opening files, maneuvering one’s way around SharePoint, saving files, etc. But sadly, that is not to be. At least in our company, we appear to have different licenses – some are on 365, some on 2019, some on 2016, and so on.
• Some people have additional log in requirements. Me, once I’m logged in, I don’t need to log in again. Others might have to do another sign in, especially if their Excel is not 2019 or Office 365. If the machine has 2016, you might have to do an additional sign on.
• Our company recently changed our email address so some of us may be on machines with old accounts. If you open up Excel that is under the old account, then you will have to log in. Sometimes that might not be enough; you might have to change the account on the machine.
• That last bullet item suggests that you might have Excel under one account and your machine under another account. That’s a possibility but I think that situation will cause you problems. You definitely will have problems with opening files from SharePoint so you are better off setting your machine account and your Excel/Word/PowerPoint account as one single account.
• Saving files could be a problem. One person needed to change her Trust Center Settings so that the file blockage was unchecked. And for a rare few, I have to send “Share” with “enable editing” before they can save the file. I usually share a file without having to do an additional step of clicking on “enable editing” but for a few people, it seems I have to. I suspect it has to do with the permission set up for them – some kind of rights may be missing in the original set up.
• Downloading files from SharePoint may be different for each machine: I’ve seen at least two different ways of downloading files from SharePoint.
• I hate to say this, but SharePoint seems to be unstable sometimes. There have been a few times when I want to move or copy files from one place to another (I’m still trying to figure out how I want to approach SharePoint, OneDrive and my hard drive), SharePoint won’t do it. SharePoint just flat out won’t do it. I have to wait a while to try again before I can actually move or copy.
• Depending on your set up, when you open files from SharePoint, you might have an Autosave feature that shows up on the upper left-hand corner and set to “on” by default. Most of the time this is fine – you want it to autosave. However, I do a lot of testing, so I’ll delete a file and re-uploading a new testing file with the same name. And when I open up that new testing file, SharePoint brings back the old file that I just deleted. The autosave feature may be playing a role here, but it is aggravating that I get the old deleted file rather than the new uploaded file. What I’ve taken to doing is use a new name for the new upload – close to the old but still slightly different.

Macros and SharePoint

Macros also have to be slightly different, especially in the area of accessing the files and directories.

For starters, while a normal file directory or Dropbox directory will look similar to this, with forward slashes:

C:\Users\vfrizzell\Documents\Veronique\Testing moving files or C:\Users\vfrizzell\Dropbox\Testing moving files (“Testing moving files” is a folder)

SharePoint will have back slashes rather than forward slashes like:

https://yourcompany.sharepoint.com/sites/

Accessing your SharePoint directory will have to be done completely different. My current method is to create a “network connection to a drive” which I’ve seen as described as the WebDAV method. There is another method described as the SOAP method and probably uses html. Currently, the “network connection to a drive” is working.

Also, you might have to keep the file containing the VBA program on SharePoint so that SharePoint will recognize the file as authenticated. If the file is saved to your hard drive and you open it from the hard drive, the VBA may not work because the file is not authenticated. To get around that blockage, I found keeping the program in SharePoint and opening the file as a Desktop app from Sharepoint got around the authentication issue.

Depending on your security feature, you may not be able to move or copy, files between SharePoint and OneDrive via VBA. In my situation, I can’t copy or move files from SharePoint to my OneDrive for back up purposes in case a manager messes up the file and we need to retrieve an original copy. To resolve this situation, I save copies in another area of SharePoint rather than on OneDrive.

Sample Codes for SharePoint

To wrap up this post, I am going to provide some sample codes for moving or copying files, and for creating or deleting folders just to show what are the slight differences you may have to make in the code for the SharePoint environment.

Moving or Copying Files

Here’s a sample code for moving files within SharePoint. Because we have a list of business units (or sites for simplification), each with their own individual folder, I have column BD on a tab called “Start Page” that lists the business units. Cell D2 on the “Start Page” provides the main SharePoint path and column BD provides the business units that would need to be added to the D2 path.

Sub MoveSharepointFiles()
'This macro is designed for Sharepoint. It needs to reside in Sharepoint in order to work.

'Macro to move Report files from sites' Weekly folder to Historical folder within the sites’ 
folder. Managers can retrieve copies of their historical reports.

    Dim FSO As Object
    Dim objNetA As Object
    Dim objNetB As Object
    Dim HostFolder As String

    Dim FromPath As String
    Dim ToPath As String
    Dim lastk As Integer

    lastk = Sheets("Start Page").Range("BD65000").End(xlUp).Row
    HostFolder = Worksheets("Start Page").Range("D2")

'STEP 1: Start the iterative process of pulling up the site folders in SharePoint
' Get the from and to path
 For k = 2 To lastk
         Sheets("Start Page").Select
         Site = Range(Cells(k, 56), Cells(k, 56))
         FromPath = HostFolder & Site & "/Financials/Weekly/"
         ToPath = HostFolder & Site & "/Financials/Historical/"

'STEP 2: Create/map a network drive to SharePoint

'Map "from" path
        Set objNetA = CreateObject("WScript.Network")
        Set FileSystemA = CreateObject("Scripting.FileSystemObject")
  
        'Clear out anything first - in case of a prior abort
        If FileSystemA.DriveExists("A:") Then
          objNetA.RemoveNetworkDrive "A:"
        End If
  
        'Now map network
        objNetA.MapNetworkDrive "A:", FromPath

'Map "to" path
        Set objNetB = CreateObject("WScript.Network")
        Set FileSystemB = CreateObject("Scripting.FileSystemObject")
  
        'Clear out anything first - in case of a prior abort
        If FileSystemB.DriveExists("B:") Then
          objNetB.RemoveNetworkDrive "B:"
        End If
  
        'Now map network
        objNetB.MapNetworkDrive "B:", ToPath

'STEP 3: Now move the files
         Dim File As Variant
         Dim MyObj As Object
         Dim Folder As Object
         Dim SubFolder As Object
    
        
         Set FSO = CreateObject("scripting.filesystemobject")
         Set MyObj = CreateObject("scripting.filesystemobject")
         Set Folder = MyObj.GetFolder("A:")
        
   
        For Each File In Folder.Files
                 If Left(File.Name, 8) = "Report -" Then
                     FSO.MoveFile Source:="A:" & "\" & File.Name,
                                  Destination:="B:" & "\" & File.Name
                 End If
       Next File
       
'STEP 4: Remove both network drives    
         objNetA.RemoveNetworkDrive "A:"
         Set objNetA = Nothing
         Set FileSystemA = Nothing
    
         objNetB.RemoveNetworkDrive "B:"
         Set objNetB = Nothing
         Set FileSystemB = Nothing

Next k
    
MsgBox ("Reports moved")

End Sub

To copy files from one place to another, you would replace FSO.MoveFile with FSO.CopyFile.

Sometimes your “from” and “to” paths have a different structure, even if within the SharePoint environment. In the example above, I was moving files from one folder in a site’s folder to another folder within the same site. But, if I wanted to make a copy of a file in a different spot in SharePoint, then I might have something like the following where D4 represents another path within SharePoint:

 HostFolder = Worksheets("Start Page").Range("D2")

'STEP 1: Start the iterative process of pulling up site folders in Sharepoint
' Get the from and to path
    For k = 2 To lastk
         Sheets("Start Page").Select
         Site = Range(Cells(k, 56), Cells(k, 56))
         FromPath = HostFolder & Site & "/Financials/Weekly/"
         ToPath = Worksheets("Start Page").Range("D4")

Creating a Folder

Here is an example to create a folder. It is slightly bit different, although we are still connecting a network to a drive. I couldn’t use a direct ToPath, or in this case ToFolder. I had to use an extra ToFolderA to create a new folder.

Sub CreateSharepointFolders()
'Create "Historical" folder on Sharepoint to hold the reports, using the suggested path

    Dim ToFolder As String

    Dim fsoFSO As Object
    Dim objNetA As Object
    Dim HostFolder As String

    Dim FromPath As String
    Dim ToPath As String
    Dim lastk As Integer

    lastk = Sheets("Start Page").Range("BI65000").End(xlUp).Row
    HostFolder = Worksheets("Start Page").Range("D3")

'STEP 1: Start the iterative process of pulling up site folders in Sharepoint
' Get the from and to path
    For k = 2 To lastk
         Sheets("Start Page").Select
         Site = Range(Cells(k, 61), Cells(k, 61))
         ToFolder = HostFolder & Site & "/Reports"


'STEP 2: Create/map a network drive to Sharepoint

'Map Path
        Set objNetA = CreateObject("WScript.Network")
        Set FileSystemA = CreateObject("Scripting.FileSystemObject")
  
        'Clear out anything first - in case of a prior abort
        If FileSystemA.DriveExists("A:") Then
          objNetA.RemoveNetworkDrive "A:"
        End If
  
        'Now map network
        objNetA.MapNetworkDrive "A:", ToFolder

'STEP 3: Now create the folder
        ToFolderA = "A:" & "\Historical"
        Set fsoFSO = CreateObject("Scripting.FileSystemObject")
        fsoFSO.CreateFolder (ToFolderA)

        
'Close network drive
         objNetA.RemoveNetworkDrive "A:"
         Set objNetA = Nothing
         Set FileSystemA = Nothing

    Next k

MsgBox ("Folders created")

End Sub

Deleting a Folder

Finally, deleting a folder requires yet another slight variation. In the prior codes, I can “remove a network drive” if I want to move or copy files or create folders. But to delete a folder, it looks like that once I create a network drive, simply removing it does not work. It looks like I have to force a disconnect by tacking on “TRUE” to the RemoveNetworkDrive line.

Sub DeleteSharepointFolders()
'Delete folders on Sharepoint, using the suggested path.
'Uses range BI as a list of sites and cell D3 as a starting point for the path.

    Dim ToFolder As String

    Dim fsoFSO As Object
    Dim objNetA As Object
    Dim HostFolder As String

    Dim FromPath As String
    Dim ToPath As String
    Dim lastk As Integer

    lastk = Sheets("Start Page").Range("BI65000").End(xlUp).Row
    HostFolder = Worksheets("Start Page").Range("D3")


'STEP 1: Start the iterative process of pulling up site folders in Sharepoint
' Get the from and to path
    For k = 2 To lastk
         Sheets("Start Page").Select
         Site = Range(Cells(k, 61), Cells(k, 61))
         ToFolder = HostFolder & Site & "/Reports"
'         ToFolder = HostFolder

'STEP 2: Create/map a network drive to Sharepoint

'Map Path
        Set objNetA = CreateObject("WScript.Network")
        Set FileSystemA = CreateObject("Scripting.FileSystemObject")
  
        'Clear out anything first - in case of a prior abort
        If FileSystemA.DriveExists("A:") Then
          objNetA.RemoveNetworkDrive "A:"
        End If
  
        'Now map network
        objNetA.MapNetworkDrive "A:", ToFolder

'STEP 3: Now create the folder
        ToFolderA = "A:" & "\Dummy Weekly"
'       Test if folder exists
        FolderExists = Dir(ToFolderA, vbDirectory)
        If FolderExists = vbNullString Then
            MsgBox ("Folder does not exist: " & Site)
        Else
            Set fsoFSO = CreateObject("Scripting.FileSystemObject")
            fsoFSO.DeleteFolder (ToFolderA)
        End If
        
'Close network drive
        'Force a network disconnect with True
         objNetA.RemoveNetworkDrive "A:", True
         Set objNetA = Nothing
         Set FileSystemA = Nothing

    Next k

MsgBox ("Folders deleted")

End Sub