Pro Tips Or Advise Needed Concerning A Code A Found On YouTube - Multiple Users Editing A Workbook At A Time

[kelly mort]

The variables

Option Explicit
Dim objFSO, objFile As Object
Dim objConnection, objRecordset As Object
Dim LastLocalChange, LastDbUpdate As Date
Dim DbFile, ConnString As String

This part of the code sync to the database:

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, "SlimSoft Systems"
    BrowseForFile
End Sub

This part of the code sync from the database:

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, "SlimSoft Systems"
    BrowseForFile
End Sub

I have tested the code and it is working very great and I want to implement it into my projects. But I am having a few doubts that I want to clear first before making the big move.
From the test example used, the file on the server was named custData (macro-free workbook) and had just one sheet named custDb. My question now is that can I have multiple sheets in the custData workbook and be able to direct my data to specified sheets or that is beyond the scope of what VBA can do in terms of this kind of jobs?

Thanks for the time to read this and making the effort to assist me.

 

[kelly mort]

Bump

I still need help with this please

So I tried to run a few tests and what I saw was that when I added new sheets to the custData workbook and sync the data to the database, all the sheets are deleted and a new sheet named CustDb is created.

Is there a way I can, maybe delete only the CustDb sheet, update it with the new data and leave the other sheets undeleted?

I want to be able to sync data from multiple sheets to their respective sheets in the CustData workbook.

It seems to me as if the version of the code I have here was meant for just one - to - one sheet transfer.

I hope someone can help me with this. It has been a very long journey and I am having the feelings I am getting very close to solving the puzzle.

Thanks in advance.

 

[kelly mort]

Bump

 

[kelly mort]

Cross posted at :

Pro Tips Or Advise Needed Concerning A Code A Found On YouTube - Multiple Users Editing

Cross posted at: https://www.mrexcel.com/board/members/kelly-mort.401365/ The reason is that it has been up there for a few days now and I can't get solution for it. The variables Option Explicit Dim objFSO, objFile As Object Dim objConnection, objRecordset As Object Dim...

www.excelforum.com

The reason is that it has been up here for a while but no solutions yet.