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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
The variables
Code:
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:
Code:
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:
Code:
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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
Cross posted at :
The reason is that it has been up here for a while but no solutions yet.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top