Write values from workbook to one stored on Sharepoint

Question

I am attempting to take data from specific cells in a workbook, and then save to a new row in a workbook on our corporate SharePoint.

I think my primary issue is "finding" the SharePoint document. I have a sample path in my code below.

I pulled code off of a website for sharing from workbook to workbook and that is the heavily commented section of my code and the area I believe may have the issue.

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

Answer 1

You can do something like this:

'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