Export data from Excel to SQL Server

Need to get data from Excel to SQL Server?

Normally this can be achieved by using SSIS. Excel can be used as a source and/or target in a Data flow. But there may be situations where you need more granular control over which data gets imported to SQL Server.

Perhaps there is a process where a user is involved in deciding which data gets inserted to the database, and the import may be initiated be the user. Or maybe some advanced calculations needs to be made to decide which values get inserted.

Anyway, using VBA (Visual Basic for Applications) in Excel macro, it is rather simple to open a connection to a SQL Server database, generate the statements that will select values from the Excel cells and import them to a table. This blog post will show a simple example of how to export data from Excel to SQL Server.

I am using Excel 2013 and SQL Server 2012 in the example.

First, we need a database and a table. In the example I am going to use a simple Customer table with a primary key, and first and last name of the customer:

CREATE DATABASE ExcelDemo
GO

USE ExcelDemo
GO

CREATE TABLE dbo.Customers
	(
		CustomerId VARCHAR(10) NOT NULL CONSTRAINT CustomerId_PK PRIMARY KEY CLUSTERED
		,FirstName VARCHAR(100) NOT NULL
		,LastName VARCHAR(100) NOT NULL
	)
GO

Next, we need a matching “table” in Excel:

Excel To SQL Server VBA Macro Sample Table
Excel To SQL Server VBA Macro Sample Table

We are going to create a button that, when clicked, imports the data from the Excel sheet into the Customer table.

To create a button in Excel, we first need to enable the Developer menu in Excel (if not done already). Open the “Excel Options” menu, and select the “Customize Ribbon” page. Make sure the “Developer” checkbox is set and click “Ok”:

Excel Options Developer Checkbox
Excel Options Developer Checkbox

Now we can create the button. From the “Developer” menu, select “Insert” and “Button”:

Excel Button
Excel Button

Draw a button anywhere in the sheet and an “Assign macro” dialog will appear. To create a macro for the button, click “New”:

Excel Assign Macro To Button
Excel Assign Macro To Button

This will open a code window for Visual Basic for Applications (VBA):

Excel Macro VBA code window
Excel Macro VBA code window

Replace all the code in the window with the code below:

Sub Button1_Click()
    
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sCustomerId, sFirstName, sLastName As String
 
    With Sheets("Sheet1")
           
        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;Data Source=ASUSBOOK\SQL2012;Initial Catalog=ExcelDemo;Integrated Security=SSPI;"
           
        'Skip the header row
        iRowNo = 2
           
        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sCustomerId = .Cells(iRowNo, 1)
            sFirstName = .Cells(iRowNo, 2)
            sLastName = .Cells(iRowNo, 3)
               
            'Generate and execute sql statement to import the excel rows to SQL Server table
            conn.Execute "insert into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', '" & sFirstName & "', '" & sLastName & "')"

            iRowNo = iRowNo + 1
        Loop
           
        MsgBox "Customers imported."
           
        conn.Close
        Set conn = Nothing
            
    End With

End Sub

On row 10 above, you need to replace my server and instance (ASUSBOOK\SQL2012) with your own.

We also need to add a reference to “Microsoft ActiveX Data Objects 2.8 Library” from the “Tools” -> “References” menu:

Excel VBA Add Reference
Excel VBA Add Reference

Close the code window and save the Excel workbook. To include the macro in the save, you need to save as “Excel Macro-Enabled Workbook” with an *.xlsm extension.

Now press the button (“Button 1”). If everything works as expected, the “Customers imported” message box should be shown:

Excel Macro Message Box
Excel Macro Message Box

If you look in the table in SSMS, our customers are imported as expected:

Excel Data To SQL Server Table
Excel Data To SQL Server Table

Note that this is just a simple demonstration. For simplicity I have hard coded the connection string and there is no error handling.

[yellow_box]
***** UPDATE *****

This simple demonstration tries to insert new records into the table each time the macro is run.

But I have been getting a lot of questions like:

* If the customer already exists in the database, how do I update it?
* How can I remove a customer from the database if it is removed from Excel?

So I have expanded this demonstration to use the T-SQL Merge statement instead. Now this solution can handle inserts, updates and deletes that you do in the Excel file. Check out the blog post here!
[/yellow_box]

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has 106 Comments

  1. amit

    Thanks ,its very use full link.

  2. Arnab

    Thank You so much for such a simple solution Thomas!!!
    You ROCK!

  3. Guru

    Hey Thomas,

    Will this work for all datatype, or should we declare different datatypes in the macro for different datatypes in the sql server table.

    1. Tomas Lind

      That’s a good question 🙂 As you can see I only used VARCHAR datatypes in SQL Server and String datatypes in the macro precisely to avoid datatype issues which would otherwise make the solution more complex. I wanted to avoid that in this post since it was meant to show the basics only. But in a more “serious” project I would definitely use more exact datatypes than strings.

      However a more complex solution is required then since there isn’t an exact match between SQL Server datatypes and the datatypes in VBA. Further, you’d need some way to handle datatype constraint violations. Perhaps via error handling in the VBA macro.

      1. Rajesh nanda

        Can you suggest same for numeric or float no…
        I try the same but always type error 13
        Showing…
        2: suppose in you example static three fields one is
        In forumal .
        I’d column name is id_2 as I’d*Len(lastname) then what will be the solution

  4. Noble Bell

    I needed a solution at work and your tutorial is spot-on. Thank you.

  5. Chams

    Nicely explained. Simple and straight.

    Thanks.

  6. Sohail

    Thanks Tom for making the Macros concept such simple…was looking something like this for understanding Macros. 🙂

  7. Ernesto

    Thank you! This worked great after trying out many other options1

  8. Palle

    Fantastisk Guide Tom, thankyou for the help :)!

    I hope you will make guide with different datatypes one day maybe ;)?

  9. Ed

    Dear Tomas,

    Thank you very much for your post. This solution worked like a charm for my requirements.

    Best,
    Ed

  10. Mouni

    Thank you so much!

  11. Rohan

    Hi Tomas,

    Thanks for this post.
    I have one problem with macro. Can you please tell me how to insert URL value into SQL table column. When i run macro all columns value of excel gets imported in SQL except column which has URL links which is shown as blank in SQL table.
    I have used string data type to store reference. URL Value sample “http://www.w3schools.com/html/html_headings.asp”
    Please advice where I am wrong.

    Thanks,
    Rohan

    1. Tomas Lind

      Hi Rohan!

      That is probably caused by the ” signs at the beginning and at the end of the string. The string will be truncated at those positions.

      Regards,
      Tomas

  12. meer

    Good article, i was searching for that frm many days . How can we update particular roll cell .

    1. Tomas Lind

      Hi, to update a specific cell in a VBA macro is easy. The following code changes cell A1 to the value of string sSomeValue:

      Cells(1, 1).Value = sSomeValue
      

      But if you want to update the value of a cell to a value from a database, that would require a longer answer…. But perhaps the following (untested) code could point you in the right direction:

      Dim conn As New ADODB.Connection
      Dim rs As ADODB.Recordset
      Dim fldEach As ADODB.Field
      Dim sSomeValue, sConn, sSQL As String
      
      sConn = "Provider=SQLOLEDB;Data Source=<YourInstance>;Initial Catalog=<YourDatabase>;Integrated Security=SSPI;"
      
      Set rs = New ADODB.Recordset
      conn.Open sConn
              
      sSQL = "SELECT 'ABC' AS SomeValue"
      Set rs = conn.Execute(sSQL)
      rs.MoveFirst
                  
      For Each fldEach In rs.Fields
          sSomeValue = fldEach.Value
      Next
              
      Cells(1, 1).Value = sSomeValue
      

      Regards,
      Tomas

  13. Andre

    Hi Tomas, i’m not too knowledgable about vba and mysql but this seems be what i’m looking for. If I wanted to point the above code to my sql database/server to import data do I change the sConn= part? If so which piece of info exactly? Thanks in advnace

    1. Tomas Lind

      Hi Andre, I don’t have MySQL installed, but yes the sConn parameter should be changed. To create a connection string you can for instance create an ordinary text file, rename the file extension from .txt to .udl and double click the file. Then create a working connection to your MySQL database. When done open the file in notepad to see the connection string. All this assumes you have the necessary drivers installed to connect to MySQL.

      Regards,
      Tomas

  14. Ananya

    Hi Tom,
    Thanks for the code .I had a small doubt ,is it possible to export the entire table or atleast one column at a time as sending data cell by cell may be time consuming

    Regards,
    Ananya

    1. Tomas Lind

      Hi Ananya,

      Actually it is not cell by cell, it is row by row. But regarding how to send the entire table, the traditional solution would be to use TVP’s (Table-Valued Parameters) for this but I don’t think it can be done in VBA.

      An alternative would be to use XML, but I’m not sure that would improve performance really…

      Another solution that would probably be the best performance wise, would be to turn it around and instead pull the data from SQL Server. Either by using openrowset, or by using SSIS. If you have the possibility.

      1. Anthony

        I tried the above example at http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx and received the error message:

        Msg 7308, Level 16, State 1, Line 1
        OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

        I am using:
        SQL Server 2008 R2
        Office 2010 SP-1 32-bit
        Windows 7 Ultimate SP1 64-bit

        My query looks like this:
        SELECT *
        FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
        ‘Excel 8.0;Database=D:\WORK_STUFF\EXCEL_SPREADSHEETS\testopenrowset.xls;IMEX=1’,
        ‘SELECT * FROM [Sheet1$]’)

        I also tried it as Excel 2010 version: testopenrowset.xlsx and received the exact same error.

  15. Sonam

    Hello,

    it works for me.

    I have a doubt over here. i want to set some permissions for SQL table. if i did, then in that case this is not working for me.

    And if i remove the permissions this works.

    but i want to set permissions to SQL tables. please give me some solution.

    thanks

    1. Tomas Lind

      Hi, if you want to grant insert permissions to a table for a specific user, use the following syntax:

      GRANT INSERT ON [TableName] TO [UserName]

      Regards
      Tomas

  16. Sundaram

    I am Getting Error in Connection String. The Error Message is “SQL Server Does Not Exist or Access Denied”. I am using SQL SERVER 2014. and Excel is 2013.

    1. Tomas Lind

      Hi, are you sure the server reference in the connection string is correct? That is, following the format SERVERNAME or SERVERNAME\INSTANCENAME? If that seems to be correct, I’d say there is some permission issue.

      1. Sundaram

        Hi

        conn.Open “Provider=SQLOLEDB;Server=local;Database=excelSheetUsingMacros;Integrated Security=SSPI;

        This is the Conection string which i gave.

        1. Sundaram

          Thanks !!! . Got Output Man !!! Thanks Alot !!!!!

  17. Mousumi

    Hi Tomas,

    Above code is really helpful but I am experiencing an issue. If I have duplicated customer id ,it’s not exporting.
    Is there any option to allow that.

    Thanks in advance for your knowledgeable tips.

    Warmest Regards,
    Mou

  18. Mou

    Hi Tomas,

    Above codes are really helpful but if I wanted to export duplicate customer id , then it’s not working.
    It would be highly appreciated, if you could provide any suggestion.

    Warm Regards,
    Mou

    1. Tomas Lind

      Hi, yes that is because CustomerId is the primary key on the table. But the table is just for demonstration purposes. To be able to insert duplicates, use the following table instead:

      CREATE TABLE dbo.Customers
          (
              CustomerId VARCHAR(10) NOT NULL
              ,FirstName VARCHAR(100) NOT NULL
              ,LastName VARCHAR(100) NOT NULL
          )
      GO
      

      But I stongly suggest that you use primary keys on tables however…

      Regards
      Tomas

      1. Mou

        Thanks a lot! Now I am experiencing another issue,if I am importing a sql data to excel and then
        after modifying something in excel ,export the same data in sql,then instead of replacing, it”s appending.
        Is that possible to replace.Any help wiuld be highly appreciable.

        Kind Regards,
        Mou

        1. Tomas Lind

          That is because I’m using an INSERT statement in the demonstration. To get the behaviour you want, try the MERGE statement.

  19. Molly

    Hiya,

    Firstly, thank you so much, this code has saved me a massive headache! It works almost exactly as I needed to. Which leads me to…

    Secondly, I have birth and death date columns in my database, but not everyone put into the database is dead (it’d be easier if they were.), and whenever I pass a table to SQL that has a blank death date, it sets the date to 01/01/1900… Is there any way to make it pass null?

    Thanks!

    1. Tomas Lind

      Hi Molly, you can use the T-SQL function NULLIF to set it to NULL if the value is 01/01/1900. So if I wanted to insert NULL if the “FirstName” column was “abc” in my example above, it would look like this:

      conn.Execute "insert into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', NULLIF('" & sFirstName & "', 'abc'), '" & sLastName & "')"
      

      Regards
      Tomas

      1. Molly

        Thanks very much!

        This has worked perfectly where birthdate is unknown, but for some reason it’s not working for deathdate.

        This is my line of code:

        conn.Execute “insert into dbo.Components (ArtistID, Forename, Surname, Nationality, BirthDate, DeathDate) values (‘” & sArtistID & “‘, NULLIF(‘” & sForename & “‘, ”), ‘” & sSurname & “‘, NULLIF(‘” & sNationality & “‘, ”), NULLIF(‘” & sBirthDate & “‘, ”), NULLIF(‘” & sDeathDate & “‘, ”))”

        Could it be because of the double )) at the end? I can’t see why it wouldn’t be working…

        1. Tomas Lind

          It is easier to verify the syntax if you extract the SQL that will be executed. To do that, simply replace “conn.Execute” with “MsgBox”. Then you’ll get a dialog that you can copy from (Ctrl+C) and then test in management studio. Hopefully, if there are any errors they will be more visible.

          I actually tried your SQL string that way, and with some changes this:

          MsgBox "insert into dbo.Components (ArtistID, Forename, Surname, Nationality, BirthDate, DeathDate) values ('" & sArtistID & "', NULLIF('" & sForename & "', ''), '" & sSurname & "', NULLIF('" & sNationality & "', ''), NULLIF('" & sBirthDate & "', ''), NULLIF('" & sDeathDate & "', ''))"
          

          …generated this SQL:

          insert into dbo.Components (ArtistID, Forename, Surname, Nationality, BirthDate, DeathDate) values ('A', NULLIF('B', ''), 'C', NULLIF('D', ''), NULLIF('E', ''), NULLIF('F', ''))
          

          … which seems correct to me (forget the A,B etc, I hardcoded the variables).

          You can go mad composing strings i VBA 🙂

          There might be a character problem also, check so that you use ” and ‘. Or is that a browser thing… ***edit*** yes, it is… use the characters from the boxes above.

          1. Molly

            So I finally got around to fixing the problem… I used the MsgBox like you suggested, and saw that for some reason the macro was reading the cells in the death date column as 0, but blank in the others. I got around this by changing the code to read:

            NULLIF(‘” & sDeathDate & “‘, ‘0’), which worked a treat!

            Thanks again!

            I know this is a little off topic, but I don’t suppose you know how to get around date formats between Excel and SQL?

            Another upload macro I will be using requires the date of the upload to be recorded, and I used sUploaded = Now()
            This worked fine on 11th of March, but wouldn’t work on 14th… I was confused for ages until I realised it’s a difference in date conventions… so SQL had actually stored the date as the 3rd November.

            I’ve tried many ways to get around this, but they don’t seem to work, so I’m sure I must be missing something?

            Again, thank you so much!!

          2. Tomas Lind

            Hi, dates in SQL Server are stored as integers internally. When displayed the format depends on local settings on the computer. For the date of the upload you can use a default value in the SQL Server table column like this:

            UploadDate DATETIME2 DEFAULT(GETDATE()) NOT NULL

            Then you wont have to specify a value for that column in the insert statement.

            Dates are represented differently in SQL Server and Excel. So perhaps it is sometimes better to convert the date to a string before inserting in to the table.

  20. VJ

    Hi Tomas Lind,

    Thank you so much for the code! It works flawlessly and has saved me a lot of time and efforts in getting my job done.

    Quick question – After importing the excel data to the SQL server table, if there are any data/ field changes made to a particular set of rows – I might need an option to refresh the existing data in the SQL table. Say, i have 5 customer ID’s alone in my excel table and i imported them to the SQL using your code. Now, i added 2 more customer ID’s to the excel. How will I be able to append these extra customer ID’s to the SQL table without having to import all 7 again creating 5 duplicates in the table?

    It would be of much needed help if you could provide us a coding sample for the requested data.
    Thanks much

    1. Tomas Lind

      Hi, glad to hear the code is useful 🙂

      You can check that the records doesn’t exists like this:

      conn.Execute "if not exists (select * from dbo.Customers where CustomerId = " & sCustomerId & ")" & " begin insert into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', '" & sFirstName & "', '" & sLastName & "') end"
      

      Regards
      Tomas

      1. VJ

        Hi Thomas,

        Thanks for your prompt response. Quick question- I am trying to push a set of Record ID’s (250+) into the SQL server from Excel using the below mentioned code. However, i see that the order of the record ID’s in excel doesn’t match with the order of record IDs in the database table. Any thoughts and help on cleaning up the code to send data in the exact same order would be highly appreciated :

        Sub ImportSQL()

        Dim conn As New ADODB.Connection
        Dim iRowNo As Integer
        Dim sSysID As String

        With Sheets(“BTF_Baseline”)

        ‘Open a connection to SQL Server
        conn.Open “Provider=SQLOLEDB;Data Source=****;Initial Catalog=****;Integrated Security=SSPI”

        conn.Execute “delete from ****”

        ‘Skip the header row
        iRowNo = 3

        ‘Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = “”
        sSysID = .Cells(iRowNo, 1)

        ‘Generate and execute sql statement to import the excel rows to SQL Server table
        conn.Execute “insert into ****(SysID) values (‘” & sSysID & “‘)”

        iRowNo = iRowNo + 1
        Loop

        MsgBox “SysID imported.”

        conn.Close
        Set conn = Nothing

        End With
        End Sub

        1. Tomas Lind

          Hi, the rows in Excel are processed in order. In SQL Server however, rows are only stored in a specific order if there is a clustered index on the table. Such as the CustomerId column in my example.

          Regards
          Tomas

  21. Harry Aungst

    Thanks for your post as it has helped tremendously.
    Is there a limitation on number of items defined and imported /
    In the following VBA, which written as is, works, I have 8 more cell/rows to reference.
    At the moment I am using static number values to have the query run.

    But if I add on more item definition, i.e.

    fttonsmovedtpd = .cells(44, IColNo).Value

    even though it is Dim’ed previously
    and make no other change to the sql,
    I get an error.

    Any ideas?

    Sub InsertForecastCol()

    Dim conn As New ADODB.Connection
    Dim iColNo As Integer
    Dim iRowNo As Integer
    Dim fGeneral As String
    Dim fdays, fthrs, fcoga, fcogb, fplmt, fplmttcu, fplmtrcu, fplmtqlt, fplktpd, frcvcr, fcl_plt, fcl_plttcu As Integer
    Dim fcl_pltrcu, fcl_pltrehtpd, fstons, fstonstcu, fstonsrcu, fstonstpd, fcltonssp, fcltonssptcu, fcltonssprcu As Integer
    Dim fcltonssptpd, fwtons, fwttonsm, fwtonstpd, fwttonsmtpd, frehorep, frehoret, frehclt, fttonsmoved, fttonsmovedtpd As Integer
    Dim fplmrlbs, fcplmrlbs, fpllbs, fcllbs, fstklbs, frcv, frcvg As Integer

    On Error GoTo ErrHandle

    With Sheets(“Fcst”)

    ‘Open a connection to SQL Server
    conn.Open “Provider=SQLOLEDB.1;” & _
    “Data Source=xxxxxxxx;Initial Catalog=yyyyyy;” & _
    “User ID=111111;PWD=22222;” & _
    “Connect Timeout=50”

    conn.Execute “DELETE FROM [dbo].[Forecast]”

    ‘Start with data on Column 6

    iColNo = 6

    ‘Loop until empty cell in yearfull
    Do Until .Cells(6, iColNo) = “”

    fGeneral = .Cells(5, iColNo).Value
    fdays = .Cells(6, iColNo).Value
    fthrs = .Cells(7, iColNo).Value
    fcoga = .Cells(8, iColNo).Value
    fcogb = .Cells(9, iColNo).Value
    fplmt = .Cells(11, iColNo).Value
    fplmttcu = .Cells(12, iColNo).Value
    fplmtrcu = .Cells(13, iColNo).Value
    fplmtqlt = .Cells(14, iColNo).Value
    fplktpd = .Cells(15, iColNo).Value
    frcvcr = .Cells(16, iColNo).Value
    fcl_plt = .Cells(18, iColNo).Value
    fcl_plttcu = .Cells(19, iColNo).Value
    fcl_pltrcu = .Cells(20, iColNo).Value
    fcl_pltrehtpd = .Cells(21, iColNo).Value
    fstons = .Cells(23, iColNo).Value
    fstonstcu = .Cells(24, iColNo).Value
    fstonsrcu = .Cells(25, iColNo).Value
    fstonstpd = .Cells(26, iColNo).Value
    fcltonssp = .Cells(28, iColNo).Value
    fcltonssptcu = .Cells(29, iColNo).Value
    fcltonssprcu = .Cells(30, iColNo).Value
    fcltonssptpd = .Cells(31, iColNo).Value
    fwtons = .Cells(33, iColNo).Value
    fwtonstpd = .Cells(34, iColNo).Value
    fwttonsm = .Cells(36, iColNo).Value
    fwttonsmtpd = .Cells(37, iColNo).Value
    frehorep = .Cells(39, iColNo).Value
    frehoret = .Cells(40, iColNo).Value
    frehclt = .Cells(41, iColNo).Value
    fttonsmoved = .Cells(43, iColNo).Value

    conn.Execute “insert into [dbo].[Forecast] ([Time Period], [Days], [Total hrs], [COGa], [COGb], [PL Mined Tons], [PL Mined Tons TCU] ” & vbCrLf & _
    “, [PL Mined Tons RCU], [PL Mined Tons QLT], [PL Mined Tons PL KTPD] ” & vbCrLf & _
    “, [PL Mined Tons % Recovery to Crusher], [CL-> PL Tons], [CL-> PL Tons TCU], [CL-> PL Tons RCU], [CL-> PL Tons Rehandle CL KTPD] ” & vbCrLf & _
    “, [Stacked Tons], [Stacked Tons TCU], [Stacked Tons RCU], [Stacked Tons YPD], [CL Tons (Stockpile)], [CL Tons (Stockpile) TCU] ” & vbCrLf & _
    “, [CL Tons (Stockpile) RCU], [CL Tons (Stockpile) Mined CL KTPD], [Waste Tons], [Waste TPD],[Total Tons Mined], [Total tons TPD] ” & vbCrLf & _
    “, [Rehandel PL ore %], [Rehandel PL ore], [Rehandled CL], [Total Tons moved], [Total Tons Moved TPD], [PL Mined Rec Lbs] ” & vbCrLf & _
    “, [Cumulative PL Mined Rec Lbs], [PL Mined total Lbs], [CL MINED (crushRec) Lbs], [STACKED Rec lbs], [Recovery (recoverable lbs/TCU lbs)], [Recoverable grade]) ” & vbCrLf & _
    ” values (‘” & fGeneral & “‘,'” & fdays & “‘,'” & fthrs & “‘,'” & fcoga & “‘,'” & fcogb & “‘,'” & fplmt & “‘,'” & fplmttcu & “‘,'” & fplmtrcu & “‘ ” & vbCrLf & _
    “,'” & fplmtqlt & “‘,'” & fplktpd & “‘,'” & frcvcr & “‘,'” & fcl_plt & “‘,'” & fcl_plttcu & “‘,'” & fcl_pltrcu & “‘ ” & vbCrLf & _
    “, ‘” & fcl_pltrehtpd & “‘,'” & fstons & “‘,'” & fstonstcu & “‘,'” & fstonsrcu & “‘,'” & fstonstpd & “‘,'” & fcltonssp & “‘,'” & fcltonssptcu & “‘ ” & vbCrLf & _
    “,'” & fcltonssprcu & “‘,'” & fcltonssptpd & “‘,'” & fwtons & “‘,'” & fwtonstpd & “‘,’9999’,'” & fwttonsmtpd & “‘,'” & frehorep & “‘,'” & frehoret & “‘ ” & vbCrLf & _
    “,'” & frehclt & “‘,'” & fttonsmoved & “‘,’9999′,’7′,’8′,’9′,’1′,’2′,’3′,’4’)”

    iColNo = iColNo + 1

    Loop

    MsgBox “Forecast imported.”

    GoTo CleanUp

    ErrHandle:
    MsgBox (“An error occurred. Check your input and try again.”)

    CleanUp:
    Application.StatusBar = False
    If conn.State Then conn.Close

    End With

    End Sub

    1. Tomas Lind

      Hi, what’s the actual error message? Might have missed it but I can’t see that in your comment.

      1. Harry Aungst

        Actually I discovered it was a data type mismatch in the sql table, so once I changed the data type it worked, so issue resolved.
        Thank you as it was your original post that led me to this project. Not much info on excel to sql via vba on the net.

  22. lakshana

    Thanks!! excellent post

  23. Bob

    This is exactly what I’ve been searching for. I can’t wait to go to work on Monday and try it out. Thanks for a great post!

  24. Harry Aungst

    When running the sql below, I only get 17 rows of data instead of the 121 rows that exist.

    Can you see anything that is incorrect?

    With Sheets(“Summary”)

    ‘Open a connection to SQL Server
    conn.Open “Provider=???;” & _
    “Data Source=??????;Initial Catalog=??????;” & _
    “User ID=????;PWD=?????;” & _
    “Connect Timeout = 3000”

    conn.Execute “delete from [dbo].[SAF_Forecast_Weekly]”

    iRowNo = 257

    ‘Loop until cell = 999
    Do Until .Cells(iRowNo, 1) = 999

    fUpdate = .Cells(10, 3)
    fRegion = .Cells(1, 1)
    fMonth = .Cells(iRowNo, 1).Value
    fDate = .Cells(iRowNo, 2).Value
    fday = .Cells(iRowNo, 3).Value
    fLeachpad = .Cells(iRowNo, 4).Value
    fFCPDown = .Cells(iRowNo, 5).Value
    fFCPRun = .Cells(iRowNo, 6).Value
    fTONS = .Cells(iRowNo, 7).Value
    fTCU = .Cells(iRowNo, 8).Value
    fSAP = .Cells(iRowNo, 9).Value
    fRCU = .Cells(iRowNo, 10).Value
    fQLT = .Cells(iRowNo, 11).Value
    fASCU = .Cells(iRowNo, 12).Value
    fCalcite = .Cells(iRowNo, 13).Value
    fTCLAY = .Cells(iRowNo, 14).Value
    fBIO = .Cells(iRowNo, 15).Value
    fQKP = .Cells(iRowNo, 16).Value
    fClayFines = .Cells(iRowNo, 17).Value
    fCumined = .Cells(iRowNo, 20).Value
    fCuPlacedSAP = .Cells(iRowNo, 21).Value
    fCuPlacedRCU = .Cells(iRowNo, 22).Value
    fCuproduced = .Cells(iRowNo, 23).Value
    fMined = .Cells(iRowNo, 25).Value
    fMoved = .Cells(iRowNo, 26).Value
    fRehandle = .Cells(iRowNo, 27).Value
    fRehandle2 = .Cells(iRowNo, 28).Value
    fAcidtoaggl = .Cells(iRowNo, 30).Value
    fActual = .Cells(iRowNo, 31).Value
    fAcidtoaggl2 = .Cells(iRowNo, 32).Value
    fRaffSXEW = .Cells(iRowNo, 33).Value
    fTotalAcid = .Cells(iRowNo, 34).Value
    fTonsXTcu = .Cells(iRowNo, 38).Value
    fTonsXRcu = .Cells(iRowNo, 39).Value
    fCure = .Cells(iRowNo, 40).Value

    conn.Execute “insert into [dbo].[SAF_Forecast_Weekly] ([Updated], [Region], [Month], [Date], [day], [Leachpad Location]) ” & vbCrLf & _
    ” values (‘” & fUpdate & “‘,'” & fRegion & “‘,'” & fMonth & “‘,'” & fDate & “‘,'” & fday & “‘,'” & fLeachpad & “‘)”

    iRowNo = iRowNo + 1

    Loop

    MsgBox “Summary Forecast imported.”
    GoTo CleanUp
    ErrHandle:
    MsgBox (“An error occurred. Check your input and try again.”)
    CleanUp:
    Application.StatusBar = False
    If conn.State Then conn.Close
    End With

    1. Tomas Lind

      Hi, I can’t see any errors really… but try to show the insert statement for each loop iteration in an MsgBox. Perhaps then you’ll be able to see what the problem is.

  25. Tomas Corrales

    Hi Tomas,
    Great Solution, there isn´t much excel vba to sql on the net and this is great.

    one question, does this also handle updates? for example if i change a value in one of the columns and export it to SQL will it create another row or update the one already there?

    If not could you provide some code for and update please..

    1. Tomas Lind

      Hi, this solution will only add new rows.

      To get the behavior you want, one solution is to use a MERGE statement instead of the INSERT statement that is used now.

  26. Jessica Miller

    I just need to update 2 specific cells from excel to SQL. How would I do that?
    Thanks
    Jessica

    1. Tomas Lind

      Hi, you can just skip the looping of rows and hard code the cell reference. So instead of:

      sCustomerId = .Cells(iRowNo, 1)
      

      you can use

      sCustomerId = .Cells(1, 1)
      

      …for instance.

      And remove the rows that handle looping.

  27. chandrashekhar

    Hi Tomas,
    Very knowledgeable article. I’m facing one issue which I’m unable to resolve. There are many tables in the database I’m using and a lot of constraints are applied on them. I’m getting the error that insert query having conflict with the constrainst on table.column name and constraint name…. I want to bypass this thing. Can you help me what changes should be in my query… Will really appreciate the help

    1. Tomas Lind

      Hi, I’d strongly suggest not bypassing constraints since they are probably there for a reason. That said, I’m guessing you run into foreign key constraint violations, since you do inserts in many tables? If so, and if your data is “healthy”, you probably just need to change the order of the inserts. E.g. you need to insert “products” before “orders” if there is a foreign key constraint that states that an order must have a product.

  28. Astaklat

    I need Help.

    I need use WHERE NOT EXIST FUCNTION , I need COMPARE of PRIMARY KEYS IN THE EXCEL WITH OF SQL for Insert Rows IF NOT EXIST in the Database.

    And Update the ROWS with EXISTS in EXCEL WITH THE SQL

    “CODE”

    conn.Execute “INSERT INTO dbo.DM (REGION, COUNTRY_KEY, CUSTOMER, CUST_NUM, KAM, COMPANY_CODE, TRIGRAM, COUNTRY, CU_UPDATE, COLLECTOR, CC_SUPPORT,AR) values (‘” & REGION & “‘, ‘” & COUNTRY_KEY & “‘, ‘” & CUSTOMER & “‘, ‘” & CUST_NUM & “‘, ‘” & KAM & “‘, ‘” & COMPANY_CODE & “‘, ‘” & TRIGRAM & “‘, ‘” & COUNTRY & “‘, ‘” & CU_UPDATE & “‘, ‘” & COLLECTOR & “‘, ‘” & CC_SUPPORT & “‘, ‘” & AR & “‘) WHERE NOT EXISTS(SELECT CUSTOMER FROM MD WHERE CUSTOMER=(‘” & CUSTOMER &”‘)) )”

    I not work.

    Please HELP

    1. Tomas Lind

      Hi, try “WHERE CUSTOMER NOT IN (SELECT CUSTOMER FROM MD)” instead.

      1. Astaklat

        I AM USE SQL, THE SOLUTION IS WHERE NOT EXISTS( SELECT CUSTOMER FROM MD WHERE CUSTOMER='” & CUSTOMER & “‘))”

  29. Astaklat

    I have other Question

    I run the macro in my file, after uploading 32767 row the macro crash with the next Error “Overflow(6)”, i try uploading one file excel with more 200 thousand rows in my sql.

    Your have idea for fix this.

    1. Tomas Lind

      Sounds like some sort of data type overflow, but I can’t answer without more information. With that much data, investigate if you could use SSIS instead.

    2. Florian Cartier

      This is due to the limitation on the int datatype. Changing your field datatype from Int to Double should fix the issue.

  30. Florian Cartier

    It works like a charm for me ! Just had to add the “User Id” and “Password” in the connection string and remove the Integrated Security part. Thank you very much for this article !

      1. milan

        Thank you, it helped me a lot! How can I add to the connection the user ID and Password, please?

  31. Zszelin

    Tomas, I Insert the data, but I am wanting to now merge with another table, and do it from within the macro. I’m thinking the best way to go about this would be to call the .sql file I have written up to accomplish this in MySQL at the end before I close my connection. I cannot find the proper syntax for this though. Would you know how to do this?

    1. Tomas Lind

      Hi, I’m not sure I understand what you are trying to do exactly, but why don’t you store the sql file as a stored procedure in the database and call that?

  32. Debs

    Excellent. It worked! Thank you

  33. Charles

    Hi Thomas many thanks for this.

    I have modified the code to read from an Excel Table using

    With TempFcast.ListObjects(1).DataBodyRange
    For i = 1 To .Rows.Count
    s1 = .Rows(i).Columns(1): s2 = .Rows(i).Columns(2): s3 = .Rows(i).Columns(3): s4 = .Rows(i).Columns(4): s5 = .Rows(i).Columns(5): s6 = .Rows(i).Columns(6)
    s7 = .Rows(i).Columns(7): s8 = .Rows(i).Columns(8): s9 = .Rows(i).Columns(9): s10 = .Rows(i).Columns(10): s11 = .Rows(i).Columns(11): s12 = .Rows(i).Columns(12)
    s13 = .Rows(i).Columns(13): s14 = .Rows(i).Columns(14): s15 = .Rows(i).Columns(15): s16 = .Rows(i).Columns(16): s17 = .Rows(i).Columns(17): s18 = .Rows(i).Columns(18)
    s19 = .Rows(i).Columns(19): s20 = .Rows(i).Columns(20): s21 = .Rows(i).Columns(21): s22 = .Rows(i).Columns(22): s23 = .Rows(i).Columns(23): s24 = .Rows(i).Columns(24)
    s25 = .Rows(i).Columns(25): s26 = .Rows(i).Columns(26): s27 = .Rows(i).Columns(27): s28 = .Rows(i).Columns(28): s29 = .Rows(i).Columns(29): s30 = .Rows(i).Columns(30)
    s31 = .Rows(i).Columns(31): s32 = .Rows(i).Columns(32): s33 = .Rows(i).Columns(33): s34 = .Rows(i).Columns(34): s35 = .Rows(i).Columns(35): s36 = .Rows(i).Columns(36): s37 = .Rows(i).Columns(37)

    all the variables (s1, s2, s3 etc.) are Dim’d as Variant because some columns are text and some numeric. The variables load correctly as String or Double.

    The SQL Server table to which the data has to be appended has the columns that correspond to numeric entries set as Numeric(12, 2)

    When I run my code I get the error: “Error converting data type varchar to numeric”

    I tried changing

    “Values (‘” & s1 & “‘, ‘” & s2 & “‘, ‘” & s3 & “‘, ‘” & s4 & “‘, ……….. and so on up to s37

    to this for each of the variables that could be either numeric or empty (the sql table is set to allow Null)

    Values (‘” & s1 & “‘, ‘” & s2 & “‘, ‘” & s3 & “‘, ‘CAST(” & s4 & “AS Numeric(12,2)’, …………

    But I still get the same error, any suggestions greatly appreciated.

    Thanks.

    1. Charles

      Thought I had it fixed because of apostrophe in wron place and missing end bracket,

      Fixed that with

      “Values (‘” & s1 & “‘, ‘” & s2 & “‘, ‘” & s3 & “‘, CAST(‘” & s4 & “‘ AS Numeric(12,2)), CAST(‘” & s5 & “‘ AS Numeric(12,2)) …..

      But still same error!

  34. Charles

    Thought I had it fixed because of apostrophe in wron place and missing end bracket,

    Fixed that with

    “Values (‘” & s1 & “‘, ‘” & s2 & “‘, ‘” & s3 & “‘, CAST(‘” & s4 & “‘ AS Numeric(12,2)), CAST(‘” & s5 & “‘ AS Numeric(12,2)) …..

    But still same error!

  35. Abhay

    Hi Tomas,

    I am looking for excel VBA macro which will copy multiple file from folder and move them to another folder ? and also would like to provide like operator in file name. Can you please tell if its possible and how?

  36. I think this is one of the most important information for me.

    And i’m glad reading your article. But wanna remark on few general
    things, The web site style is ideal, the articles is really excellent
    : D. Good job, cheers

  37. Michel

    Hi! first of all sorry for my english skills to write are not to good…

    I’ve tried your example and everything was fine till i tried to add more arrows under the once you created in the example.
    The pop up window says:
    Run-time error ‘-2147217873(80040e2f)
    Violetion of primary key constraint ‘Customer_PL’. Cannot insert duplicate key in object ‘dbo.Customers’. The duplicate key is 345
    and VBA tell me the error is in
    conn.Execute “insert into dbo.Customers(CustomerId, FirstName, LastName) values (‘” & sCustomerId & “‘, ‘” & sFirstName & “‘, ‘” & sLastName & “‘)”

    i have not duplicated CustomerId, i think it’s because the loop starts again reading the rows when i press the button for “add” new rows.

    i’m doing this because the company where i’m doing my interships exported a heavy .xls file into sql server so now i have to find a way to manage the data from excel(Insert, delete, update) they are 70 columns :s

    Thanks for share your knowledge

  38. Michel

    By the way…i forgot to tell that the excel file converted to sql server doesn’t have primary key, so… i supposed is easier?

    1. Tomas Lind

      Hi Michel,

      I think you are right. Each time you press the button the same rows are inserted to the SQL Server table and the primary key prevents that.

      I don’t know anything about your data, but a very common scenario is to check if the record is already imported. If not, then import it. If it is, then check if it is changed and if so update it. Else do nothing. All this is handled by the MERGE statement.

  39. debbie

    have anyone checked out datarails.io ? they seems to offer a seamless solution for this…

  40. Ian

    Thank you for this. This made exporting data into our database a snap. Using this in a template makes manual creation a thing of the past.

  41. Al

    This is awesome! Thanks so much! I’d been triggering SSIS packages from Excel before, which was just messy!

  42. Drew Shaffer

    Hi There
    This is amazing. I am using this to push data in excel with a button, to a table in SQL. I do have a question as I am receiving a fault.
    Also I am showing you my database table column datatypes.

    **
    **SQL TABLE COLUMN DATATYPES**
    **

    RecordedPeriod (datetime, not null)
    EventDate (varchar(8), not null)
    ID (int, not null)
    DeptCode (varchar(2), not null)
    OpCode (varchar(2), not null)
    StartTime (time(0), not null)
    FinishTime (time(0), not null)
    Units (int, not null)

    **
    **CODE**
    **

    Sub Button1_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sRecordedPeriod, sEventDate, sID, sDeptCode, sOpCode, sStartTime, sFinishTime, sUnits As String

    With Sheets(“Sheet1”)

    ‘Open a connection to SQL Server
    conn.Open “Provider=SQLOLEDB;Data Source=db1\db1;Initial Catalog=ProdTrack;Integrated Security=SSPI;”

    ‘Skip the header row
    iRowNo = 2

    ‘Loop until empty cell in FirstName
    Do Until .Cells(iRowNo, 1) = “”
    sRecordedPeriod = .Cells(iRowNo, 1)
    sEventDate = .Cells(iRowNo, 2)
    sID = .Cells(iRowNo, 3)
    sDeptCode = .Cells(iRowNo, 4)
    sOpCode = .Cells(iRowNo, 5)
    sStartTime = .Cells(iRowNo, 6)
    sFinishTime = .Cells(iRowNo, 7)
    sUnits = .Cells(iRowNo, 8)

    ‘Generate and execute sql statement to import the excel rows to SQL Server table
    conn.Execute “insert into dbo.TimeLog (RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) values (‘” & sRecordedPeriod & “‘, ‘” & sEventDate & “‘, ‘” & sID & “‘, ‘” & sDeptCode & “‘, ‘” & sOpCode & “‘, ‘” & sStartTime & “‘, ‘” & sFinishTime & “‘, ‘” & sUnits & “‘)”

    iRowNo = iRowNo + 1
    Loop

    MsgBox “Data Successfully Exported.”

    conn.Close
    Set conn = Nothing

    **
    **FAULT**
    **

    Microsoft Visual Basic
    Run-time error ‘2147217913 (80040e07)’:
    Conversion failed when converting date and/or time from character string.

  43. jen

    Hi Tomas thank you for this very useful post but I have a question. I did everything as shown but getting this “Operation cannot be completed when object is closed ” error at the below line.
    conn.Execute “insert into dbo.Customers (CustomerId, FirstName, LastName) values (‘” & sCustomerId & “‘, ‘” & sFirstName & “‘, ‘” & sLastName & “‘)”
    I checked it online but couldn’t find a solution..

  44. Mary

    hi. would you please hint me about passing textbox value to SQL?

    1. Tomas Lind

      Have you tried: Worksheets(“Sheet1”).TextBox1.Value

      BR
      Tomas

      1. Mary

        Thanks for reply. yes, but I receive error in “con.Execute” line when I trace it by F8.
        The error is : ” Run-Time error 2147217873 (80040e2f) Automation error. ”
        Here is some of my codes .

        Dim sDate, sFullName as String ‘The Data type in SQL table is nvarchar .

        sDate = Worksheets(“Sheet1”).TextBox1.Value
        sFullName = Worksheets(“Sheet1”).TextBox2.Value
        con.Execute “insert into dbo.DefinitionForm (Date1,FullName) values (‘” & sDate & “‘,'” & sFullName & “‘)”
        MsgBox “Successful.”

        Could you help me please?

        1. Mary

          Dear Mr.Tomas
          I found it finally. I picked a command button from ActiveX Controls. It didn’t know text boxes n option btn.
          But now I pick one from Form Controls and it works.
          I am thankful for all your help, Sir.
          Br,

          1. Tomas Lind

            Glad to hear it is working! 🙂

  45. Katie Sikorski

    I followed these instructions step by step it worked once but now it keeps giving me Sub Or Function not defined and I have checked everything and can’t see what I am doing wrong?

    1. Tomas Lind

      Hi, check for syntax errors in the script. Else, you can try to recreate the macro.

      /Tomas

  46. Drew Shaffer

    Tomas
    How can we delete rows with the same style?

  47. Deepa Rajaram

    hi I have a comment column which has ‘ character as a value. Since this is a key word the SQL is breaking is there a work around for the same?

  48. bang hieu dep

    What’s up, constantly i used to check blog posts here in the early hours in the morning, as i enjoy to
    find out more and more.

  49. Vidigya

    Sub Button1_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sCustomarId, sFirstName, sLastName As String

    With Sheets(“Sheet1”)

    ‘Open a connection to SQL Server
    conn.Open “Provider=SQLOLEDB;Data Source=W1042W89R2;Initial Catalog=ExcelDemo;User Id=sa;Password=Bell@12345”
    cn.ConnectionString = Connstring
    cn.Open

    ‘Skip the header row
    iRowNo = 2

    ‘Loop until empty cell in CustomarId
    Do Until .Cells(iRowNo, 1) = “”
    sCustomerId = .Cells(iRowNo, 1)
    sFirstName = .Cells(iRowNo, 2)
    sLastName = .Cells(iRowNo, 3)

    ‘Generate and execute sql statement to import the excel rows to SQL Server table
    conn.Execute “insert into dbo.Customers (CustomarId, FirstName, LastName) values (‘” & sCustomarId & “‘, ‘” & sFirstName & “‘, ‘” & sLastName & “‘)”

    iRowNo = iRowNo + 1
    Loop

    MsgBox “Customers imported.”

    conn.Close
    Set conn = Nothing

    End With

    End Sub

    I am trying to make a file for multiple users….however facing an error with runtime error424 object:required. Please help

  50. Vishva Ratna

    This Post is old but still it is guiding people. Thanks man, This helped me a lot. Wanted to appreciate your efforts.

  51. Mike

    Hi,

    I have some data that has a ‘ character which is causing a run-time error. Is there a work around?

    Thanks.

  52. Venkat Naradasu

    Hi Tomas, Great article!

    I understood that you made a code to insert ‘N’ number of rows with fixed number (‘3’) of columns, but Could you please help with the code to insert ‘N’ number of rows with ‘N’ Number of columns?

    Many Thanks,
    Venkat

  53. Meghana

    Hi..i tried the above and when i check my sql table its coming up with black rows although there is data in the excel spreadsheet and funnily its coming with the exact no of rows.

  54. Brian Chung

    Thanks nice posted, Tomas. For some reason, my program didn’t import datas columns in Excel to SQL Table. I need your expertise help, would you be so kind help to tell me what I did wrong? Pls advise

    Sub Button1_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sprrowid, sMaterialType, sMaterialGroup As String

    Dim SQLUser As String
    Dim SQLPassword As String
    Dim SQLServer As String
    Dim DBName As String
    Dim DbConn As String

    SQLUser = “S1Datar”
    SQLPassword = “x!la”
    SQLServer = “voa-db-001”
    DBName = “S1data”

    With Sheets(“Sheet1”)

    ‘Open a connection to SQL Server

    conn.Open “Provider=SQLOLEDB; User ID=” & SQLUser & “;Password=” & SQLPassword & “;Initial Catalog=” & DBName & “;” & _
    “Data Source=” & SQLServer & “;Use Procedure for Prepare=1;”

    ‘Skip the header row
    iRowNo = 2

    ‘Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = “”
    sprrowid = .Cells(iRowNo, 0)
    sMaterialType = .Cells(iRowNo, 1)
    sMaterialGroup = .Cells(iRowNo, 2)

    ‘Generate and execute sql statement to import the excel rows to SQL Server table

    conn.Execute “UPDATE ” & [S4_DataMigration].[dbo].[pt_mstr_Group_Type] & ” ” & _
    “SET ” & _
    “MaterialType = ” & sMaterialType & “, ” & _
    “MaterialGroup = ” & sMaterialGroup & _
    “WHERE prrowid = ” & sprrowid & ” ;”

    iRowNo = iRowNo + 1
    Loop

    MsgBox “Customers imported.”

    conn.Close
    Set conn = Nothing

    End With

    End Sub

  55. Prachi R Zirape

    Really easy and nice

  56. Kunal Diyali

    Hello,

    Below is my VBA coding, however, the data isn’t importing.

    Sub Button4_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer

    Dim sEmpId, sEmployeeName, sManagerName, sUserId, sDateofJoining, sTeamName, sEmployeeStatus, sManagerChangedDate, sTeamChangedDate, sTeamChangedTo, sComments, sLastDatewithAllscripts, sStatus, sUpdatedBy, sUpdatedDateTime As String

    With Sheets(“database”)

    ‘Open a connection with SQL Server
    conn.Open “Provider=SQLOLEDB;Data Source=K783620-W10N;Initial Catalog=Staffing_List;Integrated Security=SSPI;”

    ‘Skip the header row

    iRowNo = 1

    ‘Loop Until empty cell in Emp Id

    Do Until .Cells(iRowNo, 1) = “”

    EmpId = .Cells(iRowNo, 2)
    EmployeeName = .Cells(iRowNo, 3)
    ManagerName = .Cells(iRowNo, 4)
    UserId = .Cells(iRowNo, 5)
    DateofJoining = .Cells(iRowNo, 6)
    TeamName = .Cells(iRowNo, 7)
    EmployeeStatus = .Cells(iRowNo, 9)
    ManagerChangedDate = .Cells(iRowNo, 10)
    TeamChangedDate = .Cells(iRowNo, 11)
    TeamChangedTo = .Cells(iRowNo, 12)
    Comments = .Cells(iRowNo, 13)
    LastDatewithAllscripts = .Cells(iRowNo, 14)
    EmployeeStatuse = .Cells(iRowNo, 15)
    UpdatedBy = .Cells(iRowNo, 16)
    UpdatedDateTime = .Cells(iRowNo, 17)

    ‘Generate and execute sql statement to import the excel rows to SQL Server table

    conn.Execute “Insert into dbo.StaffingList (EmpId, EmployeeName, ManagerName, UserId, DateofJoining, TeamName, EmployeeStatus, ManagerChangedDate, TeamChangedDate, TeamChangedTo, Comments, LastDatewithAllscripts, Status, UpdatedBy, UpdatedDateTime) Values (‘” & sEmpId & “‘,'” & sEmployeeName & “‘,'” & sManagerName & “‘,'” & sUserId & “‘,'” & sDateofJoining & “‘,'” & sTeamName & “‘,'” & sEmployeeStatus & “‘,'” & sManagerChangedDate & “‘,'” & sTeamChangedDate & “‘,'” & sTeamChangedTo & “‘,'” & sComments & “‘,'” & sLastDatewithAllscripts & “‘,'” & sStatus & “‘,'” & sUpdatedBy & “‘,'” & sUpdatedDateTime & “‘)”

    iRowNo = iRowNo + 1

    Loop

    MsgBox “Data Transferred to SQL”

    conn.Close

    Set conn = Nothing

    End With

    End Sub

  57. John

    If you try to add a string with an apostrophe to an SQL table, Excel will give you an error near the letter that follows the apostrophe. To prevent any such errors, you can replace

    sCustomerId

    with

    Replace(sCustomerId, “‘”, “””)

    (and the same for the other named strings) in your conn.Execute statement.

    (I can’t read any of the comments, for some reason, so sorry if someone has already said this.)

  58. Lee Zhi Qian

    Hi I followed all the steps, and I have the “user-defined type not defined” error. the Microsoft ActiveX data object recordset 2.8 library has been added

  59. Cindi Baard

    Thanx so much. One question….I am have a lot of columns in my database and having issues trying to split a long line of code in multiple lines before where the words are separated by a comma I.e. before value ( ‘” &..&.”‘,

Leave a Reply