1

My setup involves three computers: 1. An application sits on an Internet-connected Windows Server in the Cloud. 2. This application is connected to a SQL Server instance on another Windows Server in the Cloud, which is disconnected from the Internet 3. I have an Excel sitting on a third computer.

How can Excel send data to the Windows Server database?

One working option is to use Dropbox to synchronize text files between the Excel and the Internet-connected Windows Server, and then to use sqlcmd on the Internet-connected Windows Server to run these text files on the database.

However this solution is not ideal for a number of reasons!

Any better way? As far as I know I can't open up SQL Server to the Excel - using such approaches as described in https://leansoftware.net/Help/Excel-Database-Tasks/Worked-examples/How-to-connect-Excel-to-Remote-Web-Server-SQL-Data.aspx - because my SQL Server is not connected to the Internet.

Note that the SQL Server is the Express edition (we hope to upgrade to Azure in the not-too-distant future)

Improve this question
2
  • 3
    You say that the SQL server machine isn’t connected to the internet. What exactly do you mean by that? Dropbox won’t work if it’s not connected to the internet. Do you actually mean that the port required to access SQL Server directly isn’t open to the internet, and the machine actually does have an internet routeable IP address?
    – Philᵀᴹ
    Commented May 12, 2019 at 12:03
  • @Philᵀᴹ I apologize if I was not clear enough. Dropbox works only to synchronize the Excel computer with the Internet-enabled server via the web. I then use SQLCMD to transfer the text files from the Internet-enabled server to the non-Internet enabled server via a local connection opened for that purpose. Once the files are transferred, they are accessible to SQL Server.
    – gordon613
    Commented May 12, 2019 at 13:59

1 Answer 1

Reset to default
2

We have created an Excel Add-In for updating tables from Excel to SQL Server. The Add-In supports working in offline mode, and I'm pretty sure the off-line mode will handle this case.

You will need to install Excel and the SQL Spreads Add-In on both the non-internet connected server and on the laptop. The process will then be like this:

  1. On your "off-line" server containing your SQL Server databases, create a new Excel file and select the SQL Server table(s) you like to update.
  2. The data from the tables will be loaded into the Excel file. When you save the file, all the SQL Server data will be stored into the Excel file.
  3. Move the Excel file to your laptop and edit the data there. When you save the file, all your changes will be stored into the file.
  4. Move the Excel file back to the server, open the file and click Save in the SQL Spreads tab in Excel to push the updates to the SQL Server tables.

The Add-In can also check for conflicts if the data has been changed in the database while you were editing it in your off-line doc.

If that's of interest, you can read more here and download a free trial.

Disclaimer: I'm the founder of the company.

Improve this answer
1
  • Definitely an interesting idea. However this involves a lot of file synchronization work (laptop -> Internet Server -> off-line Server) and I was particularly interested in a solution that depended less on file synchronization across servers and could work automatically and seamlessly. Reducing the technical issues of communication between Excel and SQL Server would definitely be a nice-to-have though!
    – gordon613
    Commented May 21, 2019 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.