How To Insert Data From Excel To Sql In Vba

Past: | Updated: 2022-04-07 | Comments | Related: More > Import and Export

Trouble

There are several ways to import data into SQL Server from Excel. In this tutorial, we will take a wait at how this could be accomplished past creating an Excel macro with some VBA code to import information from an Excel spreadsheet into SQL Server.

Solution

Below is a sample of what nosotros will put together. We volition take data entered into the spreadsheet and and so create a push to import the data into SQL Server.

Excel data

When we printing Salvage the information will exist saved in SQL Server in a tabular array that is already created.

SQL Server data in a table

We will use Excel 2017 in this case, but other versions will work but may require a few changes.

Create the table in SQL Server

Kickoff, we will create a table named email in SQL Server. The following code will create the table.

CREATE TABLE [dbo].[email](    [id] [smallint] NULL,    [electronic mail] [varchar](l) NULL ) ON [Principal] GO

Working with Excel

Create an Excel file with some information. In this example, we add the id and the email.

Excel information to export

To create the button in Excel, we demand to add the developer menu. If y'all do not run into the Developer carte du jour, go to File > Options.

In Excel Options, click on Customize Ribbon and check the Developer checkbox and press OK.

Enable Developer in Excel

Now, y'all will run into the Programmer in the Menu. Click on Developer and select the option Insert and drag and drop the Button to the Excel sheet.

Button in Excel

You lot tin can Edit the text push past correct-clicking the button and selecting Edit Text. And change the text to "Save".

export excel data to sql server vba 006

Right-click the button and select Assign Macro.

Assign Macro button

The Macro is the code that we will use to export the Excel data to SQL Server.

In the Assign Macro window, click the Edit button.

Edit button for macro

This volition open the Microsoft Visual Basic for Application software. This allows to create Visual Basic code for Applications (VBA). By default, yous do not have the Edit toolbars. To view it, get to View > Toolbars > Edit.

View Toolbars Edit option

The Edit Toolbar allows commenting code and increasing or decreasing indentation and other options.

Ident and comment code

Now, it is fourth dimension to add the code. The following lawmaking will do what we need.

Sub Button1_Click() 'Create a connection     Dim id, email As String     Dim row As Integer     Dim connexion Every bit New ADODB.connection          With Sheets("Sheet1") '        Connection to SQL Server         connexion.Open "Provider=SQLOLEDB;Information Source=.;Initial Itemize=Adventureworks2019;Integrated Security=SSPI;" '        Start in the second row because row 1 is the header         row = 2              'Run until the row is empty         Do Until IsEmpty(Cells(row, ane)) '        Get the id from column 1 and the row is dynamic             id = .Cells(row, one) '            Become the electronic mail from column ii and the row is dynamic             electronic mail = .Cells(row, two)                 '          Insert the cell values into the email table             connectedness.Execute "insert into dbo.email (id, e-mail) values ('" & id & "', '" & e-mail & "')" ' increase the row by 1             row = row + 1         Loop                  '   Close and make clean the connection         connection.Close         Set connection = Nothing                   End With Stop Sub

Code Explanation

In the first part of the code, we create the variables for the columns id and email and a row to insert row by row. We will too create the connection variable.

Sub Button1_Click() 'Create a connection     Dim id, email As String     Dim row Every bit Integer     Dim connection As New ADODB.connectedness

We will work with Sheet1 of the Excel file.

The connection will connect to SQL Server. Information Source is the name of the server. In this case, is the local server and then a . ways to use the local server. You can use the proper noun of the SQL Server also. Initial Catalog is the database name. In this example, we created the tabular array in the Adventureworks2019 database. You tin can use whatever database in your SQL Server. Just make sure that your table is in that database. Finally, Integrated Security ways using Windows Authentication. So, the user that runs the code needs to have permissions to SQL Server.

Finally, we will start in row ii, because row 1 has the column headers.

            With Sheets("Sheet1") '        Connection to SQL Server         connection.Open "Provider=SQLOLEDB;Information Source=.;Initial Catalog=Adventureworks2019;Integrated Security=SSPI;" '        Offset in the 2nd row because the row 1 is the header         row = 2

Finally, we volition do a loop for each row of the Excel canvas until the row is empty and insert the data from the Excel cells into the SQL Server tabular array.

'Run until the row is empty         Practise Until IsEmpty(Cells(row, 1)) '        Go the id from column 1 and the row is dynamic             id = .Cells(row, 1) '            Go the email from column 2 and the row is dynamic             electronic mail = .Cells(row, 2)                 '          Insert the jail cell values into the electronic mail table             connexion.Execute "insert into dbo.e-mail (id, e-mail) values ('" & id & "', '" & email & "')" ' increase the row by 1             row = row + 1         Loop

The terminal function will only close the connection.

'   Close and make clean the connection         connectedness.Close         Set connection = Nothing                   End With

Execute the Code

One time saved, if you execute the code past clicking Save, you might receive the following fault message in Excel.

User-divers type not defined

This is because yous practice not take the libraries to create the SQL Server connexion. To gear up this problem, become to the carte and click Tools > References.

Add references in macro

In this example I checked Microsoft ActiveX Data Object 6.1 Library. If y'all do non take that version, you tin use a lower or higher version.

Microsoft ActiveX Data Objects

Now, if you printing the Salvage button in Excel in should save the data to the table.

I you exercise a query in SQL Server we should be able to see the data.

Data imported in excel

Next Steps

If you inquire me, to export data from Excel into SQL Server, my starting time option would exist to use the SQL Server import and export wizard. It is easier to consign the data. The second option, if yous demand more transformations and the Consign wizard is not enough, I would apply SSIS in a project to customize the export process. Finally, as a third option, I would use a Linked Server to Excel. I would use VBA only if I have a lot of lawmaking in Macros and I love to use Macros.

This is a good option if you lot need to create a unproblematic utility for end users and don't want to give them access to SSIS or SSMS.

Apply the links below to review other ways to import data into SQL Server:

Simple way to import information into SQL Server
Using a SQL Server Linked Server to Query Excel Files
Consign SQL Server Data to Multiple Excel Worksheets using SQL Server Integration Services