use excel spreadsheet to fill web form

Question

I have a spreadsheet in Excel, it was generated from a form of a system and then some data were selected. After that I have to put this data in another web form, this form is in another system other than what generated the initial spreadsheet. To do this use Ctrl C + Ctrl V :( and I waste a lot of time, and it is necessary to check the data again later.

I wonder if anyone knows any specific program that does this automatically, transfers the data from a spreadsheet in excel to a web form?

If you do not know, you can tell me if it is possible to do this using VBA?

Answer 1

The simplest and cheapest way can be just by exporting to a CSV format or any other format that is easy to import through the web application.

You will only have the job of creating the data importer on the web platform. But you’ll still have manual labor.

Alternatively, you can also create a more sophisticated automation.

By VBA, send the data by GET or POST method.

Example taken from SO-en

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "http://www.somedomain.com"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHTTP.send ("var1=value1&var2=value2&var3=value3")

Consult: https://stackoverflow.com/questions/158633/how-can-i-send-an-http-post-request-to-a-server-from-excel-using-vba

Thus, just create an API to receive the data in the web application (on the website). Then the whole process will continue to be done inside Excel, without having to copy and paste to a folder on the web or without having to export and import to the web manually.

Another alternative is to connect directly to the database remotely. For this you need to free a user in the database for remote access.

One advantage is that you do not need to create any API on the website because the connection to the database is done directly.

This can be the simplest and most practical means for automation.

An example in SO-en: https://stackoverflow.com/questions/13558921/how-can-vba-connect-to-mysql-database-in-excel

A topic right here in the OS: Mysql connection failed via VBA

Obviously we won’t talk here about security, concepts and other related topics as it would make the response extensive, complicated and tiring.