Using Workbooks.open in onedrive automatically stops de code

[Apoca91]

Hello, my name is Rodrigo Correia, this is my first post in this forum. I tried to search for a presentation Thread but didnt found one.

Im starting in the VBA world. Im currently making a code for the factory im working on.

This is the code:

Enum StatusCheck
Ok = 0
DuplicatedPallet = 1
DuplicatedSN = 2
'DuplicatedPalletAndSN = 3
End Enum

Sub SaveButton_Click()

If ThisWorkbook.ActiveSheet.ProtectContents Then
Call MsgBox("Sheet '" & ThisWorkbook.ActiveSheet.Name & "' is protected!", vbExclamation + vbOKOnly + vbDefaultButton1, "Guardar")
Exit Sub
End If

Set found = ThisWorkbook.ActiveSheet.Range("E:E").Find("0", LookIn:=xlValues, LookAt:=xlWhole)
If Not found Is Nothing Then
Call MsgBox("Número(s)de série duplicado(s)! Ver Coluna E", vbExclamation + vbOKOnly + vbDefaultButton1, "Guardar")
Exit Sub
End If

' LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row
' Valor1 = Mid(ActiveSheet.Range("B" & LastRow).Value, 2, 8)
'
' Call ModelCodeInserted
'
' Modelcode = "20147039"
' LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row
'
' For Row = 2 To LastRow
' InputValue = Mid(ActiveSheet.Range("B" & Row).Value, 2, 8)
' If Not Modelcode = InputValue Then
' Call MsgBox("Model Code EDP não corresponde ao Model Code de 1 ou mais códigos inseridos!! Contactar Engenharia", vbExclamation + vbOKOnly + vbDefaultButton1, "Guardar")
' Exit Sub
' End If
' Next Row


vbsave = MsgBox("Guardar '" & ThisWorkbook.ActiveSheet.Name & "'?", vbQuestion + vbYesNo + vbDefaultButton2, "Guardar")
If vbsave = vbNo Then
Exit Sub
End If

Dim DestFileName As String
Dim DestSheetName As String
Dim DestFilePath As String


DestFileName = "BASE DE DADOS - PALETES E SERIAL NUMBERS.xlsx"
DestSheetName = "BASE DE DADOS"
DestFilePath = "C:\Users\rcorreia\OneDrive - Schréder SA\Voltana\BASE DE DADOS - PALETES E SERIAL NUMBERS.xlsx"

Set wksSrcSheet = Workbooks(ThisWorkbook.Name).Worksheets(ThisWorkbook.ActiveSheet.Name)

Call DestWorkOpen

Set wksDestSheet = Workbooks(DestFileName).Worksheets(DestSheetName)

Call TakeSnapshot(wksSrcSheet, wksDestSheet)

End Sub

Sub TakeSnapshot(wksSrcSheet, wksDestSheet)

lSrcLastRow = wksSrcSheet.Cells(wksSrcSheet.Rows.Count, "B").End(xlUp).Row
lDestLastRow = wksDestSheet.Cells(wksDestSheet.Rows.Count, "B").End(xlUp).Offset(1).Row
wksDestSheet.Activate

Status = SnCheck(wksSrcSheet, wksDestSheet, lDestLastRow, lSrcLastRow)
If Status = StatusCheck.Ok Then

wksSrcSheet.Range("A2:E" & lSrcLastRow).Copy
wksDestSheet.Range("A" & lDestLastRow).PasteSpecial Paste:=xlPasteFormats
wksDestSheet.Range("A" & lDestLastRow).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

Call SaveSnapshotWorkbook(ActiveWorkbook)
Call ProtectWorksheet(wksSrcSheet)

Else
If Status = StatusCheck.DuplicatedPallet Then
Call MsgBox("Número de Palete repetido!", vbOKOnly + vbDefaultButton1)
ElseIf Status = StatusCheck.DuplicatedSN Then
Call MsgBox("Número(s) de série repetido(s)!", vbOKOnly + vbDefaultButton1)
ElseIf Status = (StatusCheck.DuplicatedPallet Or StatusCheck.DuplicatedSN) Then
Call MsgBox("Número de Palete e número(s) de série repetido(s)!", vbOKOnly + vbDefaultButton1)
End If

Call SaveSnapshotWorkbook(ActiveWorkbook)
End If

End Sub

Sub SaveSnapshotWorkbook(wks)
wks.Save
wks.Close
End Sub

Sub ProtectWorksheet(wks)
If Not wks.Cells.Locked Then
wks.Cells.Locked = True
End If
wks.Protect contents:=True
End Sub

Function SnCheck(wksSrcSheet, wksDestSheet, lDestLastRow, lSrcLastRow) As StatusCheck

Status = StatusCheck.Ok

SingleValues = IsError(Application.Match(wksSrcSheet.Cells(2, "A").Value, wksDestSheet.Range("A2:A" & lDestLastRow), 0))

If Not SingleValues Then
Status = Status Or StatusCheck.DuplicatedPallet

End If

wksSrcSheet.Range("B2:B" & lSrcLastRow).Interior.ColorIndex = 0
For Row = 2 To lSrcLastRow
SingleValues = IsError(Application.Match(wksSrcSheet.Cells(Row, "B").Value, wksDestSheet.Range("B2:B" & lDestLastRow), 0))
If Not SingleValues Then
Status = Status Or StatusCheck.DuplicatedSN
wksSrcSheet.Range("B" & Row).Interior.ColorIndex = 3
End If
Next Row

SnCheck = Status
End Function

Sub DestWorkOpen()

Workbooks.Open ("C:\Users\rcorreia\OneDrive - Schréder SA\Voltana\BASE DE DADOS - PALETES E SERIAL NUMBERS.xlsx") <--- CODE STOPS HERE

End Sub
_________________________________________________________

What im trying to make is to copy a range of data from my sourcesheet to my destination sheet in this path: "C:\Users\rcorreia\OneDrive - Schréder SA\Voltana\BASE DE DADOS - PALETES E SERIAL NUMBERS.xlsx"

No matter what i do, in break mode after the code execute the workbooks.open, the code automatically continues until the end, it doesnt stop. I think this has to do with these files being in the onedrive. If i put them on a hard drive none of this happens.

I dont know what to do. If you need any more explanation please feel free to ask. Im not well familiar with all the terms and designations.

thank you for your help!!

 

[Apoca91]

Anyone? thank you