none
Need to update SQL Server table on click of Excel button

    Question

  • I am reading some data from SQL Server and showing into Excel. User has the flexibility to update values and click on update button. On this click I want to save this updated data in to SQL Server table.
    Wednesday, February 19, 2014 11:21 AM

Answers

  • Sure..!

    Go to Developer tab

    create new button on excel sheet by using insert option.

    When you create the button it'll ask to create a macro like "Button1_click".

    Click that it'll go to code behind..

    Then,

     Dim cn As New ADODB.Connection
      cn.ConnectionString = "Provider = SQLOLEDB.1; Persist Security Info = False;Data Source = servername; User ID = username;Password = password; Initial Catalog = dbname"
      cn.CursorLocation = adUseClient
      cn.Open

     dim i as integer, QryTxt as string

     i = startingrow(number)
     Do Until i = Sheet1.Rows.Count
      If Sheet1.Cells(i, 1) = "" Then
       nRows = i - 1
       Exit Do
      End If
      i = i + 1
     Loop

     For i = startingrow(number) To nRows
            QryTxt = QryTxt & "update table name set column1 = '" & Replace(Sheet1.Cells(i, 1), "'", "''") & "'," & vbCrLf
                               QryTxt = QryTxt & "    column2 =  '" & Replace(Sheet1.Cells(i, 3), "'", "''") & "'," & vbCrLf
                               QryTxt = QryTxt & "    column3 =  '" & Sheet1.Cells(i, 4) & "'," & vbCrLf
            QryTxt = QryTxt & "where conditioncolumn =" & "'" & Sheet1.Cells(i, 2) & "'" & "" & vbCrLf
     Next

     conn.execute(QryTxt)

     

    Wednesday, February 19, 2014 12:28 PM

All replies

  • Hi Sunil,

         Correct me if I'm wrong, user created a new column in excel sheet and entered some data on that column. Finally when he click the update button the new column and data will go to SQL table.

    Is it right?

    Wednesday, February 19, 2014 11:29 AM
  • No new column will be added by user. He is allowed to update the values and then click on UPDATE button.
    Wednesday, February 19, 2014 11:58 AM
  • Sunil,

     By using Excel macros (VBA) project we can do

    Establish the connection and take the count of rows and do like as below,

     i = startingrow(number)
     Do Until i = Sheet1.Rows.Count
      If Sheet1.Cells(i, 1) = "" Then
       nRows = i - 1
       Exit Do
      End If
      i = i + 1
     Loop

     For i = startingrow(number) To nRows
            QryTxt = QryTxt & "update table name set column1 = '" & Replace(Sheet1.Cells(i, 1), "'", "''") & "'," & vbCrLf
                               QryTxt = QryTxt & "    column2 =  '" & Replace(Sheet1.Cells(i, 3), "'", "''") & "'," & vbCrLf
                               QryTxt = QryTxt & "    column3 =  '" & Sheet1.Cells(i, 4) & "'," & vbCrLf
            QryTxt = QryTxt & "where conditioncolumn =" & "'" & Sheet1.Cells(i, 2) & "'" & "" & vbCrLf
     Next

     conn.execute(QryTxt)

    Wednesday, February 19, 2014 12:09 PM
  • Hi bpbhaskar,

    I have never written VB code. Can you guide me step by step to integrate your code. 

    Wednesday, February 19, 2014 12:13 PM
  • Sure..!

    Go to Developer tab

    create new button on excel sheet by using insert option.

    When you create the button it'll ask to create a macro like "Button1_click".

    Click that it'll go to code behind..

    Then,

     Dim cn As New ADODB.Connection
      cn.ConnectionString = "Provider = SQLOLEDB.1; Persist Security Info = False;Data Source = servername; User ID = username;Password = password; Initial Catalog = dbname"
      cn.CursorLocation = adUseClient
      cn.Open

     dim i as integer, QryTxt as string

     i = startingrow(number)
     Do Until i = Sheet1.Rows.Count
      If Sheet1.Cells(i, 1) = "" Then
       nRows = i - 1
       Exit Do
      End If
      i = i + 1
     Loop

     For i = startingrow(number) To nRows
            QryTxt = QryTxt & "update table name set column1 = '" & Replace(Sheet1.Cells(i, 1), "'", "''") & "'," & vbCrLf
                               QryTxt = QryTxt & "    column2 =  '" & Replace(Sheet1.Cells(i, 3), "'", "''") & "'," & vbCrLf
                               QryTxt = QryTxt & "    column3 =  '" & Sheet1.Cells(i, 4) & "'," & vbCrLf
            QryTxt = QryTxt & "where conditioncolumn =" & "'" & Sheet1.Cells(i, 2) & "'" & "" & vbCrLf
     Next

     conn.execute(QryTxt)

     

    Wednesday, February 19, 2014 12:28 PM
  • What is startingrow(number)?  conn.execute(QryTxt) will always throw error as conn is never declared.
    Wednesday, February 19, 2014 1:02 PM
  • Sunil,

     Sorry for the wrong update.

    change cn.execute(QryTxt) instead of conn.execute(QryTxt)

    and

    Startingrow in the sense starting row of data(row number) left side in excel sheet.

    Wednesday, February 19, 2014 1:07 PM
  • can we get starting row dynamically? is there any function?
    Wednesday, February 19, 2014 1:34 PM
  • Try the same,

    i = 1 

    Do Until i = Sheet1.Rows.Count
      If Sheet1.Cells(i, 1) <> "" Then
       nRows = i   
       Exit Do
      End If
      i = i + 1
     Loop

    Wednesday, February 19, 2014 1:45 PM
  • Hi,

    I need starting row dynamically? Will this code work for the same?

    Wednesday, February 19, 2014 1:54 PM
  • Yes. You can get the starting row dynamically using above code.

    Wednesday, February 19, 2014 2:07 PM
  • dim startrow as integer

    startrow = 1

    Do Until startrow = Sheet1.Rows.Count
      If Sheet1.Cells(startrow, 1) <> "" Then
       nRows = startrow   
       Exit Do
      End If
      startrow = startrow + 1
     Loop

    Wednesday, February 19, 2014 2:09 PM
  • For now I have used 2 as a starting row (since 1 row is header only). Thanks for your help.

    Is there any way to identify what all rows were updated in Excel. This I need to update only those rows in SQL Server also (not all rows).

    Thursday, February 20, 2014 6:12 AM