- Apr 10, 2017
- Office Version
Option Explicit Dim objFSO, objFile As Object Dim objConnection, objRecordset As Object Dim LastLocalChange, LastDbUpdate As Date Dim DbFile, ConnString As String Sub SyncToDatabase() DbFile = Sheet1.Range("M5").Value 'Customer Database Location LastLocalChange = Sheet1.Range("B12").Value Set objFSO = CreateObject("Scripting.FileSystemObject") On Error GoTo FileMissing Set objFile = objFSO.GetFile(DbFile) If objFile.DateLastModified < LastLocalChange Then 'Local Change was made, update Database Kill (DbFile) 'Delete the current database version ThisWorkbook.Sheets("CustDb").Copy ActiveWorkbook.SaveAs DbFile, FileFormat:=51 ActiveWorkbook.Close False End If Exit Sub FileMissing: MsgBox "Please browse for the database file", vbInformation, "" BrowseForFile End Sub Sub SyncFromDatabase() LastLocalChange = Sheet1.Range("B12").Value DbFile = Sheet1.Range("M5").Value 'Customer Database Location Set objFSO = CreateObject("Scripting.FileSystemObject") On Error GoTo FileMissing Set objFile = objFSO.GetFile(DbFile) If objFile.DateLastModified > LastLocalChange Then 'Database Change was made, update Local Database 'Check Last Database Update Sheet2.Range("A2:G9999").ClearContents 'Clear existing data On Error Resume Next Set objConnection = CreateObject("ADODB.Connection") Set objRecordset = CreateObject("ADODB.Recordset") objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _ Sheet1.Range("M5").Value & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=0"";" objRecordset.Open "Select * FROM [CustDb$]", objConnection Sheet2.Range("A2").CopyFromRecordset objRecordset objRecordset.Close objConnection.Close On Error GoTo 0 End If Exit Sub FileMissing: MsgBox "Please browse for the database file", vbInformation, "" BrowseForFile End Sub
This code is currently used to sync data to and from the database workbook (CustData). The CustData has just one sheet "CustDb" and if the local data is fresher than the data in the database, we update the database and vice versa.
I do not entirely understand what the various lines are doing - making it very difficult for me to do any vital amendments. I believe that if I able to explain exactly what I want to do or achieve, some great mind here could get it fixed ASAP for me.
So instead of having just one sheet in the database, I will be having multiple sheets. Say "CustDb1", "CustDb2" and so on. And the same sheets will be available in the local file as well.
My primary goal here is to be able to sync data to and from the corresponding sheets between the two workbooks. That is, if I am interacting with the "CustDb1" sheet from the local file (workbook), then the data should be synced to and/or from the "CustDb1" sheet in the database.
The database file is a macro-free workbook. I hope someone could fix this for me. Thanks in advance.