Copying Data to Workbook sitting at Sharepoint

[kshitij_dch]

Hello All,

I have created a user form which is saving details into excel on a command click , I need same details to be captured in workbook saved at SharePoint , I tried the below code however didn't succeed as it created a new workbook but doesn't shows any data and throws error

[CODE=vba]
Private Sub CommandButton1_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row + 1
lRow = ws.Cells(Rows.Count, 2).End(xlUp).Row + 1
With ws
.Cells(lRow, 2).Value = TextBox1.Value
.Cells(lRow, 3).Value = TextBox2.Value
.Cells(lRow, 4).Value = TextBox3.Value
.Cells(lRow, 6).Value = TextBox4.Value
.Cells(lRow, 7).Value = TextBox5.Value
.Cells(lRow, 8).Value = TextBox6.Value
.Cells(lRow, 11).Value = TextBox7.Value
.Cells(lRow, 12).Value = TextBox8.Value
.Cells(lRow, 13).Value = TextBox9.Value


If OptionButton4.Value = True Then
    .Cells(lRow, 5).Value = OptionButton4.Caption
    Else
   
If OptionButton2.Value = True Then
   
    .Cells(lRow, 5).Value = OptionButton2.Caption
   
 Else
   
If OptionButton3.Value = True Then
   
    .Cells(lRow, 5).Value = OptionButton3.Caption
   
   
    End If
       End If
          End If

End With

Unload Me


'Note needs the workbook name included, not just the path
Const DEST_WB_PATH As String = "https://auh-my.arep\oint.com/:x:/r/personal/n_aa/_layouts/15/Doc.aspx?sourcedoc=%7B4EC65EBB-396D-4EE3-A777-D71578FDA6EE%7D&file=Report%20tracker_20220816.xlsx&action=default&mobileredirect=true/Report tracker_20220816.xlsx"
Dim wbDest As Workbook, wsDest As Worksheet

Set wbDest = Workbooks.Open(DEST_WB_PATH)
Set wsDest = wbDest.Worksheets("Work Order Tracking Form")

With wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).EntireRow
    .With ws
.Cells(lRow, 2).Value = TextBox1.Value
.Cells(lRow, 3).Value = TextBox2.Value
.Cells(lRow, 4).Value = TextBox3.Value
.Cells(lRow, 6).Value = TextBox4.Value
.Cells(lRow, 7).Value = TextBox5.Value
.Cells(lRow, 8).Value = TextBox6.Value
.Cells(lRow, 11).Value = TextBox7.Value
.Cells(lRow, 12).Value = TextBox8.Value
.Cells(lRow, 13).Value = TextBox9.Value


If OptionButton4.Value = True Then
    .Cells(lRow, 5).Value = OptionButton4.Caption
    Else
   
If OptionButton2.Value = True Then
   
    .Cells(lRow, 5).Value = OptionButton2.Caption
   
 Else
   
If OptionButton3.Value = True Then
   
    .Cells(lRow, 5).Value = OptionButton3.Caption
   
   
    End If
       End If
          End If

End With




End Sub

[/CODE]

 

[kshitij_dch]

its opening a new workbook instead of opening workbook given in path