使用动态文件名保存到 SharePoint 的 VBA 宏

VBA Macro to Save to SharePoint with dynamic file name

提问by mattinwpg

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).

我有一个 Excel 工作表，我想通过宏将其保存到 SharePoint。我已经排序了，但问题是每个月的文件名都会不同，我不想每次都更改宏（即 filenameSeptember、filenameOctober 等）。

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.

上下文：每个月我都会用新数据更新这个文件并用新文件名保存它，将它发送给用户进行验证 - 我希望这个用户能够通过一个自动使用的宏直接上传到 SP 目录文件名我已经调用了该文件。

I hope that makes sense, and thanks in advance.

我希望这是有道理的，并提前致谢。

回答by Jeanno

This will work for you

这对你有用

Sub savetest()

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

End Sub

回答by mattinwpg

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

回答by vlad1490

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

我相信问题和答案对于自动化办公非常有用。提供在 SharePoint 文档库上存储信息的通用功能。

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

注意：用户必须将 SharePoint 库 URL 复制到特定工作表

A. My Program Template

A. 我的程序模板

'========================================
'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

B. 参考文档

Microsoft Documentation ThisWorkbook.SaveAs Method

Microsoft 文档 ThisWorkbook.SaveAs 方法