Excel VBA with Sharepoint

Hi All

I suspect I already know the answer but thought I'd check unless I've missed something.

Basically I have a excel file I use as a template, with VBA code that users save copies without overwriting the template file.

I would like to move this to Sharepoint, so that more users can use it, but I have no idea really how file system stuff would work or if its even possible.

I have three parts of code that I think will be the issue as below.

Backup System:

backupPath = ThisWorkbook.Path & "\Backups\"

baseName = "Quick Quoting Tool-Backup_"

If Dir(backupPath, vbDirectory) = "" Then

MkDir backupPath

End If

latestDate = 0

f = Dir(backupPath & baseName & "*.xlsm")

Do While f <> ""

On Error Resume Next

fileDate = DateSerial( _

Mid(f, Len(baseName) + 1, 4), _

Mid(f, Len(baseName) + 5, 2), _

Mid(f, Len(baseName) + 7, 2))

On Error GoTo 0

If fileDate > latestDate Then

latestDate = fileDate

End If

f = Dir

Loop

If latestDate = 0 Or DateDiff("d", latestDate, Date) > 30 Then

fileName = baseName & Format(Date, "yyyymmdd") & ".xlsm"

ThisWorkbook.SaveCopyAs backupPath & fileName

End If

Save as new file system:

Set currentWB = ThisWorkbook

newFilePath = "T:\Quoting\Client Quotes\Quick Quotes\"

newFileName = Format(Now, "yyyy-MM-dd-hhmm") & " - " & QTEType & " - " & POLPOD & " - " & ClientName & ".xlsm"

currentWB.SaveAs fileName:=newFilePath & newFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Moving expired files system:

sourceFolder = "T:\Quoting\Client Quotes\Quick Quotes\"

expiredFolder = "T:\Quoting\Client Quotes\Quick Quotes\Expired\"

currentYearMonth = Format(Date, "yyyy-mm")

Set fso = CreateObject("Scripting.FileSystemObject")

For Each file In fso.GetFolder(sourceFolder).Files

fileName = file.Name

If Left(fileName, 2) = "~$" Then GoTo NextFile

If LCase(fso.GetExtensionName(fileName)) = "xlsm" Then

fileDate = Split(fileName, " ")(0)

yearMonth = Left(fileDate, 7)

If yearMonth <> currentYearMonth Then

filePath = file.Path

fso.MoveFile filePath, expiredFolder & fileName

End If

End If

NextFile:

Next file

Set fso = Nothing

There is a bunch of file system type code there, can it be change/modified to use a sharepoint location like:

https://companyname.sharepoint.com/sites/NZ/Shared Documents/Quoting/Client Quotes/Quick Quotes/ etc

Thanks in advance.

submitted by /u/Stildawn
[link] [comments]