Update multiple records in SQL using Excel VBA

Question

Hello I doing an update from Excel to SQL but the code only works for 1 row. I want to update multiple rows or you could say all rows which are on Excel I have loaded

see the below code

VBA Code:
Sub UpdateTable()
Dim cnn As ADODB.Connection
Dim uSQL As String

Dim LR, LC As Long
Dim Group, Category As String
Dim itcode As Integer
' this is only for active cell i want for all cell in sheet which is on A column
Dim rngname As Range
Set rngname = ActiveCell
Set cnn = New Connection
        itcode = ShCustomers.Cells(rngname.row, 1)
        Group = "'" & ShCustomers.Cells(rngname.row, 2) & "'"
         Category = "'" & ShCustomers.Cells(rngname.row, 3) & "'"
cnnstr = "Provider=SQLOLEDB; Data Source=MAK-SYS;Initial Catalog=db_bckupserver_test_sys;User ID=sa;Password=Rehman@123;Trusted_Connection=No"
cnn.Open cnnstr
uSQL = "UPDATE mak_items_chart SET [Group] = " & Group & " WHERE itcode =  " & itcode
cnn.Execute uSQL
cnn.Close
Set cnn = Nothing

End Sub

I want to loop it but I don't understand how I can add a loop which will update all the records into SQL

Here is the image with modified code

Please see the below image

No Active sheet is shcustomers sheet – Makhdoom Liaqat Sep 23 '22 at 08:52 — Makhdoom Liaqat, Sep 23 '22 at 08:52

Storax · Accepted Answer · 2022-09-23T09:33:58.280

0

My assumption is that we only need the sheet with the codename ShCustomers. Then my suggestion would be

Sub UpdateTable()
    Dim cnn As ADODB.Connection
    Dim uSQL As String

    Dim LR, LC As Long
    Dim Group As String, Category As String  ' I fixed the declaration
    Dim itcode As Integer
    Dim rngname As Range
    
    Set cnn = New Connection
    cnnstr = "Provider=SQLOLEDB; Data Source=MAK-SYS;Initial Catalog=db_bckupserver_test_sys;User ID=sa;Password=Rehman@123;Trusted_Connection=No"
    cnn.Open cnnstr
    
    Set rngname = getColA(ShCustomers)
    
    Dim sngCell As Range
    For Each sngCell In rngname
        
        ' you have to check if the values make sense
        ' What about empty cells
        itcode = sngCell.Value
        Group = "'" & sngCell.Offset(, 1).Value & "'"
        Category = "'" & sngCell.Offset(, 2).Value & "'"
        
        uSQL = "UPDATE mak_items_chart SET [Group] = " & Group & " WHERE itcode =  " & itcode
        cnn.Execute uSQL
    Next sngCell
    
    cnn.Close
    Set cnn = Nothing

End Sub

You need to add the following functions

Function getColA(ws As Worksheet) As Range
    Dim rng As Range
        
    Dim lastRow As Long
    lastRow = FindLastRow(ws.Columns(1))
    
    With ws
        Set rng = Range(.Cells(1, 1), .Cells(lastRow, 1))
    End With
    Set getColA = rng
End Function
Function FindLastRow(rg As Range) As Long
    
    On Error GoTo EH
    
    FindLastRow = rg.Find("*", , Lookat:=xlPart, LookIn:=xlFormulas _
        , searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Exit Function

EH:
    FindLastRow = rg.Cells(1, 1).Row

End Function

edited Sep 23 '22 at 09:33

answered Sep 23 '22 at 08:40

Storax

11,158
3
16
33

I don't Have any Empty Cells But it is giving me error on itcode = sngcell.value Run time error 13 Type Mismatch – Makhdoom Liaqat Sep 23 '22 at 09:09
`itcode` is declared as `integer`! Do you have integers in column A? That's what I wrote in the comments of the modified code _you have to check if the values make sense_ Empty cells are just one case. – Storax Sep 23 '22 at 09:11
yes itcode is integer and i have integer value let me show you the image https://www.mediafire.com/file/g89h6xydnt3j3gf/Screenshot+2022-09-23+141843.png/file here is the image of my excel sheet – Makhdoom Liaqat Sep 23 '22 at 09:19
Please add the screenshot to the post. And also copy the value there. The error message says that the content of the cell is not an integer. – Storax Sep 23 '22 at 09:26
Error Image https://www.mediafire.com/file/uwhbzbv1coz4p7i/Error.jpg/file Debug Image https://www.mediafire.com/file/n7425ccdncwrhqj/debug+highlighted.jpg/file Itcode Value itcode 13845 15801 8324 15802 – Makhdoom Liaqat Sep 23 '22 at 09:36
So, the first value of `itcode` is _itcode_ because you have a header in your range, right? This is not an integer. You wrote _' i want for all cell in sheet which is on A column_ – Storax Sep 23 '22 at 09:37
Oh My God Dear thank you i got That You are right My Problem is solved – Makhdoom Liaqat Sep 23 '22 at 09:42

Update multiple records in SQL using Excel VBA

1 Answers1

Linked