时间戳

首页 > 解决方案 > 将工作簿中的值写入 Sharepoint 上存储的值

excel - 将工作簿中的值写入 Sharepoint 上存储的值

问题描述

我正在尝试从工作簿中的特定单元格中获取数据，然后将其保存到我们公司 SharePoint 上工作簿中的新行。

我认为我的主要问题是“查找”SharePoint 文档。我在下面的代码中有一个示例路径。

我从一个网站上提取了代码，以便在工作簿之间共享，这是我的代码中被大量评论的部分，也是我认为可能存在问题的区域。

Sub depositSummaryUpdater()

ActiveSheet.Unprotect "password"

Dim managerName As String
managerName = ActiveSheet.Cells(6, 4).Value

Dim depositActual As Currency
depositActual = ActiveSheet.Cells(11, 9).Value

Dim depositRevel As Currency
depositRevel = ActiveSheet.Cells(18, 3).Value

Dim depositVariance As Currency
depositVariance = ActiveSheet.Cells(15, 3).Value

Dim hundredCountDeposit As Currency
hundredCountDeposit = ActiveSheet.Cells(12, 7).Value

Dim fiftyCountDeposit As Currency
fiftyCountDeposit = ActiveSheet.Cells(11, 7).Value

Dim twentyCountDeposit As Currency
twentyCountDeposit = ActiveSheet.Cells(10, 7).Value

Dim tenCountDeposit As Currency
tenCountDeposit = ActiveSheet.Cells(9, 7).Value

Dim fiveCountDeposit As Currency
fiveCountDeposit = ActiveSheet.Cells(8, 7).Value

Dim twoCountDeposit As Currency
twoCountDeposit = ActiveSheet.Cells(7, 7).Value

Dim oneCountDeposit As Currency
oneCountDeposit = ActiveSheet.Cells(6, 7).Value

Dim quarterCountDeposit As Currency
quarterCountDeposit = ActiveSheet.Cells(9, 10).Value

Dim dimeCountDeposit As Currency
dimeCountDeposit = ActiveSheet.Cells(8, 10).Value

Dim nickelCountDeposit As Currency
nickelCountDeposit = ActiveSheet.Cells(7, 10).Value

Dim pennyCountDeposit As Currency
pennyCountDeposit = ActiveSheet.Cells(6, 10).Value

Dim tillDate As Date
tillDate = Format(ActiveSheet.Cells(4, 4), "YY-MM-DD")

Dim wsDest As Worksheet
Dim destLastRow As Integer

'Dim depositPathString As String
'depostPathString = "https://ourcorporatesharpointaddress.sharepoint.com/sites/tills/Shared%20Documents/Safe%20Deposit/Safe_Deposit_Summary"

'Set variables for destination sheet
'Set wsDest = Workbooks(depositPathString).Worksheets("Deposit Summary")
Set wsDest = Workbooks("https://ourcorporatesharepointaddress.sharepoint.com/sites/tills/Shared%20Documents/Safe%20Deposit/Safe_Deposit_Summary").Worksheets("Deposit Summary")

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
destLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

'3. Copy & Paste Data
Set wsDest.Cells(destLastRow, 1) = tillDate
Set wsDest.Cells(destLastRow, 2) = hundredCountDeposit
Set wsDest.Cells(destLastRow, 3) = fiftyCountDeposit
Set wsDest.Cells(destLastRow, 4) = twentyCountDeposit
Set wsDest.Cells(destLastRow, 5) = tenCountDeposit
Set wsDest.Cells(destLastRow, 6) = fiveCountDeposit
Set wsDest.Cells(destLastRow, 7) = twoCountDeposit
Set wsDest.Cells(destLastRow, 8) = oneCountDeposit
Set wsDest.Cells(destLastRow, 9) = quarterCountDeposit
Set wsDest.Cells(destLastRow, 10) = dimeCountDeposit
Set wsDest.Cells(destLastRow, 11) = nickelCountDeposit
Set wsDest.Cells(destLastRow, 12) = pennyCountDeposit
Set wsDest.Cells(destLastRow, 13) = depositActual
Set wsDest.Cells(destLastRow, 14) = depositRevel
Set wsDest.Cells(destLastRow, 15) = depositVariance
Set wsDest.Cells(destLastRow, 16) = managerName

ActiveSheet.Protect "password", True, True

End Sub

标签： excelvbasharepointsharepoint-online

解决方案

你可以这样做：

'Note needs the workbook name included, not just the path
Const DEST_WB_PATH As String = "https://tangandbiscuitcom.sharepoint.com/sites/tills/" & _
                            "Shared%20Documents/Safe%20Deposit/Safe_Deposit_Summary/Data.xlsx"
Dim wbDest As Workbook, wsDest As Worksheet

Set wbDest = Workbooks.Open(DEST_WB_PATH)
Set wsDest = wbDest.Worksheets("Deposit Summary")

With wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).EntireRow
    .Cells(1).Value = tillDate
    .Cells(2).Value = hundredCountDeposit
    '... etc etc
End With

推荐阅读