How to keep a set of Excel ranges in sync with a set of database tables

[markns]

Hi folks,

I'm trying to find the best/least-kludgey way to keep a set of Excel ranges in sync with a set of database tables (can be SQL server or MySQL)

Here are the requirements:

• It must be a one way sync, Excel -> database
• It doesn't have to be an immediate sync, for example a macro running every 5 minutes is fine. But it should happen automatically, without a user clicking a button.
• The state of the Excel table should completely replace the state of the database table, including inserts, updates and deletes.
• There are multiple tables that need to be synced, so something reasonably generic.

Things I've considered:

• VBA macros with an ADO DB connection, using this set of libraries: exSnake/VBTools. My VBA skills let me down here somewhat.
• Excel to SQL Server - use Excel to update data in SQL Server tables - it's a paid tool though, and my organisation isn't very keen on buying new licences
• SQL Server master data management. I've watched some videos, it seems very powerful, but also very complex. Not sure if it fits our use case.
• SSIS Simple way to import data into SQL Server - but this seems to be a one-off import

Sadly nothing seems perfect. Is there anything I'm missing perhaps? Any advice gratefully received.

 

[sandy666]

maybe try Power Query (Get&Transform) and set refresh time in connections

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[markns]

If anyone is interested, this page was very useful to me: Excel-SQL Server Import-Export using VBA - www.Excel-SQL-Server.com

 

[anishpa]

Hi, I was having a similar requirement, wanted one way sync from excel sheet to a central MS SQL db table and all updates to be pushed in a timely fashion to keep the SQL table updated. I failed to find a reliable solution and finally had to develop it myself. I think you can use it too. Let me know if interested.

 

[ngonimuro]

Hi, I was having a similar requirement, wanted one way sync from excel sheet to a central MS SQL db table and all updates to be pushed in a timely fashion to keep the SQL table updated. I failed to find a reliable solution and finally had to develop it myself. I think you can use it too. Let me know if interested.

Hello Anshipa, I am interested in pointers on your solution