ZamfirescuT
New Member
- Joined
- Jan 30, 2022
- Messages
- 1
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
Hello,
Need your help because I did not find any solution yet to my issue. I have a userform used by multiple users. This form input data into a sheet which will save a db file on the network. This syncs based on last local change. If the db file on the network is newer, then it sync into the workbook containing the userform, otherwise does nothing.
I am quite new into VBA, and do not find any solution for the following issue : the code works great, except if from the source file (the db file) sone rows are deleted, it syncs only the number of rows that I have in the source file. Even if the db is newer and sync should happen, it does, but not for the extra rows (if any) in the source file.
Because I have to clean data weekly, if on the db file I reduce the number of rows, the destination file on sync will not delete the extra rows from the destination file(so users will not have the updated data). Bellow is the code for the sync :
Sub SyncToDatabase()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
DbFile = Worksheets("Form").Range("AN2").Value
LastLocalChange = Worksheets("Form").Range("AM2").Value
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(DbFile)
If objFile.DateLastModified < LastLocalChange Then 'Local Change was made, update Database
Kill (DbFile) 'Delete current database version
Worksheets("DataSh").Visible = True
ThisWorkbook.Worksheets("DataSh").Copy
ActiveWorkbook.SaveAs DbFile, FileFormat:=51
ActiveWorkbook.Close False
Worksheets("DataSh").Visible = xlVeryHidden
Call cmdrefresh_Click
Call cmdReset_Click
Call StartTimer
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If
End Sub
Sub SyncFromDatabase()
On Error Resume Next
ThisWorkbook.Activate
LastLocalChange = Worksheets("Form").Range("AM2").Value
DbFile = Worksheets("Form").Range("AN2").Value
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(DbFile)
If objFile.DateLastModified > LastLocalChange Then 'Database change made, update LocalDatabase
Worksheets("DataSh").Visible = True
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Worksheets("Form").Range("AN2").Value & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes:IMEX=0"";"
objRecordset.Open "Select * FROM [DataSh$], objConnection"
Worksheets("DataSh").Range("A2").CopyFromRecordset objRecordset
objRecordset.Close
objConnection.Close
'Worksheets("DataSh").Visible = xlVeryHidden
WIPPS.Hide
WIPPS.lstdisplay.RowSource = "WipPS"
WIPPS.Show
End If
End Sub
Thank you!
Need your help because I did not find any solution yet to my issue. I have a userform used by multiple users. This form input data into a sheet which will save a db file on the network. This syncs based on last local change. If the db file on the network is newer, then it sync into the workbook containing the userform, otherwise does nothing.
I am quite new into VBA, and do not find any solution for the following issue : the code works great, except if from the source file (the db file) sone rows are deleted, it syncs only the number of rows that I have in the source file. Even if the db is newer and sync should happen, it does, but not for the extra rows (if any) in the source file.
Because I have to clean data weekly, if on the db file I reduce the number of rows, the destination file on sync will not delete the extra rows from the destination file(so users will not have the updated data). Bellow is the code for the sync :
Sub SyncToDatabase()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
DbFile = Worksheets("Form").Range("AN2").Value
LastLocalChange = Worksheets("Form").Range("AM2").Value
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(DbFile)
If objFile.DateLastModified < LastLocalChange Then 'Local Change was made, update Database
Kill (DbFile) 'Delete current database version
Worksheets("DataSh").Visible = True
ThisWorkbook.Worksheets("DataSh").Copy
ActiveWorkbook.SaveAs DbFile, FileFormat:=51
ActiveWorkbook.Close False
Worksheets("DataSh").Visible = xlVeryHidden
Call cmdrefresh_Click
Call cmdReset_Click
Call StartTimer
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If
End Sub
Sub SyncFromDatabase()
On Error Resume Next
ThisWorkbook.Activate
LastLocalChange = Worksheets("Form").Range("AM2").Value
DbFile = Worksheets("Form").Range("AN2").Value
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(DbFile)
If objFile.DateLastModified > LastLocalChange Then 'Database change made, update LocalDatabase
Worksheets("DataSh").Visible = True
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Worksheets("Form").Range("AN2").Value & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes:IMEX=0"";"
objRecordset.Open "Select * FROM [DataSh$], objConnection"
Worksheets("DataSh").Range("A2").CopyFromRecordset objRecordset
objRecordset.Close
objConnection.Close
'Worksheets("DataSh").Visible = xlVeryHidden
WIPPS.Hide
WIPPS.lstdisplay.RowSource = "WipPS"
WIPPS.Show
End If
End Sub
Thank you!