update data to match worksheets instead of deleting entire workbook

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Cross-posted at:

Reason:
It has been up there for a few days now but no reply apart from my own replies.


Link to work books:

The code below is how I managed to fix my problem - If there is still a better way to get the job done, I am also open to better ways.

Code:
Option Explicit
Dim objFSO, objFile As Object
Dim objConnection, objRecordset As Object
Dim LastLocalChange, LastDbUpdate As Date
Dim DbFile, ConnString As String
Dim db As Workbook, rng As Range, lr&

Sub SyncToDatabase()
    With Application
        .ScreenUpdating = False
        DbFile = Sheet1.Range("M5").Value
        LastLocalChange = Sheet1.Range("B12").Value
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        On Error GoTo FileMissing
        Set objFile = objFSO.GetFile(DbFile)
        If objFile.DateLastModified < LastLocalChange Then
            Set db = .Workbooks.Open(DbFile)
            With ThisWorkbook.Sheets("CustDb")
                If .Cells(Rows.Count, 1).End(xlUp).Row > db.Sheets("CustDb").Cells(Rows.Count, 1).End(xlUp).Row Then
                    lr = .Cells(Rows.Count, 1).End(xlUp).Row
                Else
                    lr = db.Sheets("CustDb").Cells(Rows.Count, 1).End(xlUp).Row
                End If
                If lr < 2 Then lr = 2
                Set rng = .Range("A2:G" & lr)
                db.Sheets("CustDb").Range("A2").Resize(rng.Rows.Count, rng.Columns.Count) = rng.Value
            End With
            db.Close True
        End If
        .ScreenUpdating = True
    End With
    Exit Sub
FileMissing:
    MsgBox "Please browse for the database file"
    BrowseForFile
End Sub

Then the code below here is where I need the next help with.
Code:
Sub SyncFromDatabase()
    LastLocalChange = Sheet1.Range("B12").Value
    DbFile = Sheet1.Range("M5").Value
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    On Error GoTo FileMissing
    Application.ScreenUpdating = False
    Set objFile = objFSO.GetFile(DbFile)
    If objFile.DateLastModified > LastLocalChange Then
        Sheet2.Range("A2:G9999").ClearContents
        On Error Resume Next
        Set objConnection = CreateObject("ADODB.Connection")
        Set objRecordset = CreateObject("ADODB.Recordset")
        objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
        DbFile & ";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
    End If
    Application.ScreenUpdating = True
    Exit Sub
FileMissing:
    MsgBox "Please browse for the database file"
    BrowseForFile
End Sub

Instead of hardcoding the CustDb
Code:
 [CustDb$] [/ccode]
[IMG]http://icons.iconarchive.com/icons/double-j-design/ravenna-3d/24/File-Copy-icon.png[/IMG]
[code] objRecordset.Open "Select * FROM [CustDb$]", objConnection
When I use a variable say Set sh = Sheets("CustDb"), how do I use the sh in the line above?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
This is how I managed to get it working:

Code:
objRecordset.Open "Select * FROM [" & sh & "$]", objConnection

Thanks to you all.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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
Back
Top