如何从 Excel vba 以编辑模式在 SharePoint 上打开启用宏的 Excel 文件? [英] How to open a macro enabled Excel file on a SharePoint in edit mode from an Excel vba?

问题描述

我在 SharePoint 上有一个启用宏的 Excel 文件，当用户从 SharePoint 打开该文件时，该文件会以编程方式打开同一 SharePoint 上另一个启用宏的 Excel 文件.vba 宏打开的文件需要编辑，并且必须可以被多个用户同时编辑.但是我什至无法在编辑模式下打开它.使用 Office 365

I have an macro enabled Excel file on a SharePoint that when a user opens it from the SharePoint the file opens programmatically another macro enabled Excel file on the same SharePoint. The file being opened by the vba macro needs to be edited, and must be editable by multiple users at the same time. However I can't get it to even open in edit mode. Using Office 365

我试过<<ActiveWorkbook.LockServerFile >> 但总是收到错误消息 <<运行时错误 1004:对象 '_Workbook' 的方法 'LockServer' 失败 >>.

I've tried << ActiveWorkbook.LockServerFile >> but always get an error message << Run-time error 1004: Method 'LockServer' of object'_Workbook' failed >>.

我在下面显示的代码位于用户手动打开的 Excel 文件中，该文件会自动打开另一个 Excel 文件.打开时的另一个 Excel 文件工作正常(如果我删除 LockServerFile 命令)，所有宏都可以正常工作，但它以只读方式打开，无法保存更改.同样，此文件应可供多个用户同时编辑.

The code that I show below is in the Excel file that is opened manually by the user and that opens automatically the other Excel file. The other Excel file when opened works fine (if I remove the LockServerFile command), all it's macro's work fine, but it is open in read only and changes cannot be saved. Again this file should be editable by multiple users simultaneously.

'此代码在ThisWorkbook"标签中

' this code is in the "ThisWorkbook" tab

子工作簿_open()

Sub workbook_open()

设置 DB = Workbooks.Open(DBname, 3, False, , , , True)

Set DB = Workbooks.Open(DBname, 3, False, , , , True)

ActiveWorkbook.LockServerFile ' 这是崩溃的地方

ActiveWorkbook.LockServerFile ' this is where is crashes

'更多代码...

结束子

' 注意:DB 是在模块中声明的

' Note: DB is declared in a module

公共数据库作为工作簿Public Const DBname As String = "https://ledvance365.sharepoint.com ... .xlsm"

Public DB as Workbook Public Const DBname As String = "https://ledvance365.sharepoint.com ... .xlsm"

推荐答案

我遇到了类似的问题.代码在您突出显示的同一点崩溃.

I had a similar issue. The code crashes at the same point you highlighted.

ActiveWorkbook.LockServerFile

当文档已经可编辑时似乎失败了.

Seems to fail when the document is already editable.

On Error Resume Next
    ActiveWorkbook.LockServerFile

修复了我遇到的问题，因为当文件已经可编辑时，代码将继续运行，并且如果文件以前不可编辑，则类似地使文件可编辑.

Fixes the issue I was experiencing since the code will continue on when the file is already editable and similarly makes a file editable if it wasn't previously.

这篇关于如何从 Excel vba 以编辑模式在 SharePoint 上打开启用宏的 Excel 文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！