MSSQL – Import data from Excel to SQL Server with VBA

Let’s imagine the following situation. You work in a small company, where one excel spreadsheet is used by many users locally. But you still want to see some kind of track of the results in a collected option. E.g.Β some information, when the users from their desks have run a specific program. And what was their result. Their parameters? And anything else. πŸ™‚

This can be done with the all-mighty MS SQL Server. Let’s imagine that you need information like this:

sql2

It is quite ok if you know the username of the user, the date and the time he ran a program plus the current location of the file. A status as a result of the file is also ok to be known. This is the sql code, used to generate this table in a db:

sql3

So, what should you do? The way is to simply kindly make a connection to the MSSQL database with VBA, thus trying to make everything as smooth as possible πŸ™‚ Something like this:

As you see, you

  1. Generate the data in the code in the first not empty row in excel;
  2. Use this data to assign it to variables;
  3. Use these variables as parts of the insert query;

Pretty much that is all. The random could be anything else, I just liked it. Last but not least, to avoid errors due to missing library for DBO.Connection, make sure you have added the MS ActiveX Data Objects 2.8 Library like here:

sql1

Once it is there, you probably would be able to run the code smoothly. Available also in Github! πŸ™‚

That is all! πŸ™‚

 

Tagged with: ,