Code amendment needed for a workbook sharing macro (vba)

kelly mort

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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows
The SharedWorkbook.xlsm file has two sheets while the CustData.xlsx file has just one sheet.

B12 contains the last changes made date and M5 contains the link to the CustData.xlsx file as the database
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
I don't see any particular use made of the last changes made date. Do you know of any reason why that exists?

Never mind - I see it. Everything stops if the "database" has changes. Seems like that is used for reverse changes. Don't know how you really handle this if you have changes on both sides at the same time ...
 
Last edited:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows
Don't know how you really handle this if you have changes on both sides at the same time ...

That's a very valid concern.

But at the moment I think it's very rare to have same timestamp at both sides.
Maybe that will be worried about later - but not now.

Better still if there is a way to avoid the timestamp and get the data to be synced to and from the database, I am open to that as well.

Thanks
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
What's happening on the other end ... who is using the "database file"?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

What's happening on the other end ... who is using the "database file"?

No one will use it. It is only there for data to be synced to and from.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
Well, let's put the question another way...who is syncing to and from this file? If its just the workbook we are working with now then its really just a "backup". Are you really saying that nobody and nothing is using the other file?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows
Oh okay I get it now.

users on the network will be using it.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows
Bump.

I Still need help with this please.

Can someone help me out?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,980
Messages
5,599,160
Members
414,295
Latest member
Dolenhil

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