CODEWITHSUNDEEP
vbaexcelsharepoint
mattinwpg
mattinwpg

Reputation: 163

VBA Macro to Save to SharePoint with dynamic file name

I've got an excel sheet that I want to save to SharePoint via a macro. I have it sorted, but the problem is every month the file name is going to be different and I don't want to have to change the macro each time (i.e. filenameSeptember, filenameOctober, etc).

I'm using a very basic macro because I'm not super good at writing them, and it works but it's got the old filename hardcoded in:

Sub savetest()

ActiveWorkbook.SaveAs Filename:= _
    "http://SharePointdirectory/filenameSeptember.xlsm" _
    , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

Context: each month I would be updating this file with new data and saving it with a new filename, sending it to a user to validate - i want this user to be able to upload directly to the SP directory via a macro that automatically uses the filename i've called the file.

I hope that makes sense, and thanks in advance.

Upvotes: 7

Views: 88633

Answers (3)

vlad1490
vlad1490

Reputation: 365

I believe both question and answers are very useful to automate office work. Providing generalized function to store information on the SharePoint Document Library.

Note: User must copy the SharePoint library URL to the specific worksheet

A. My Program Template

'========================================
'SAVE THIS FILE TO A SHAREPOINT
'========================================

Sub Push2SharePoint()

    ' define variables
    Dim SharePointPath As Variant
    Dim FileAsNamed As Variant
    ' retrieve SharePoint path indicated by the user inside Excel Sheet named "Select" on cell B33
    SharePointPath = ThisWorkbook.Sheets("Select").Range("B33").Text
    ' provide some error message if it's not populated
    On Error GoTo NoStorageSelected
    If Not SharePointPath <> False Then
        'Displaying a message if file not choosedn in the above step
        MsgBox "No storage space was selected.", vbExclamation, "Sorry!"
        'And existing from the procedure
        Exit Sub
    Else
        'Create the new file name, note we place data format in ISO 8601 format in front of the file name
        FileAsNamed = SharePointPath & Year(Date) & "-" & Month(Date) & "-" & Day(Date) & "_" & ThisWorkbook.Name

        ' save the copy
        ThisWorkbook.SaveAs FileName:=FileAsNamed, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


    End If

Exit Sub
' Error Management
NoStorageSelected:
           MsgBox "Error: Excel can not reach SharePoint Folder Storage location" & vbCrLf & _
           "Possible reasons are: Storage location was not defined in the Worksheet 'Select' cell B33 or " & vbCrLf & _
           "Not having sufficient previledges to access SharePoint location " & vbCrLf & _
           "Make sure to add forward slash after SharePoint Document Library"
           Exit Sub

End Sub

B. Reference Documentation

Microsoft Documentation ThisWorkbook.SaveAs Method

Upvotes: 1

mattinwpg
mattinwpg

Reputation: 163

I ended up using cells to specify a filename and store the path instead, which seems to work:

Public Sub SavetoSP()
ThisFile = Range("D1").Value
ThisPath = Range("J1").Value

ActiveWorkbook.SaveAs Filename:=ThisPath & ThisFile & ".xlsm"
End Sub

Upvotes: 2

Jeanno
Jeanno

Reputation: 2859

This will work for you

Sub savetest()

ThisWorkbook.SaveAs Filename:= _
    "\\SharepointDirectory\" & ThisWorkbook.Name & MonthName(Month(Date), False) _
    , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

Upvotes: 7

Related Questions