Results 1 to 8 of 8

Thread: SQL Select,Insert,Update queries from Excel vba?

  1. #1

    Post SQL Select,Insert,Update queries from Excel vba?

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am having an excel workbook with some sheets.From that i am able to read the current month and previous month sheets data and inserting those columns data into sql table.

    But to avoid insertion of same data all the time,

    I just want to check a condition like if particular columns of sql table data and excel sheets data may match then i want to update those records in the database otherwise i want to insert as new records in to the database.

    Some part i am successfully done(like insertion) but

    i am not succeeded at select statement in the code(to check the rows count based on conditions for columns data match) and
    at updating those records if condition met.

    Please look at the sample files along with database creation and please provide any solution to solve my problem

    Thanks for any solutions.

    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    Fits of all, thanks for the table creation script, that was a nice touch.

    Rewrote the code a bit

    Option Explicit
    Private Cn As Object 'ADODB.Connection
    Private RS As Object 'ADODB.Recordset
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Const connString As String = _
        "Driver={SQL Server};" & _
        "Server=<server>;" & _
        "Database=<database>;" & _
        "Uid=<user>;" & _
    Dim CurrMonth As String, PrevMonth As String
    Dim ServerName As String
    Dim DatabaseName As String
    Dim TableName As String
    Dim UserID As String
    Dim Password As String
    Dim Msg As String
        Msg = MsgBox("Do you really want to save the workbook?", vbYesNo)
        If Msg = vbYes Then
            ServerName = "."
            DatabaseName = "Upload"
            TableName = "Test"
            UserID = ""
            Password = ""
            Set Cn = CreateObject("ADODB.Connection")
            With Cn
                .CursorLocation = 3 'adUseClient
                .Open Replace(Replace(Replace(Replace(connString, _
                                                "<server>", ServerName), _
                                        "<database>", DatabaseName), _
                                "<user>", UserID), _
                        "Password>", Password)
                .CommandTimeout = 0
            End With
            CurrMonth = Format$(Date, "mmm 'yy")
            PrevMonth = Format$(Date - Day(Date), "mmm 'yy")
            Call upload(Worksheets(PrevMonth))
            Call upload(Worksheets(CurrMonth))
            Set Cn = Nothing
            MsgBox "Operation was Cancelled"
        End If
    End Sub
    Sub upload(ByRef sh As Worksheet)
    Const sSQLSelect As String = _
        "SELECT Count( ID ) " & vbNewLine & _
        "FROM Test " & vbNewLine & _
        "WHERE Country='<country>' AND " & vbNewLine & _
        "      Name='<name>'"
    Const sSQLInsert As String = _
        "INSERT INTO Test(Country" & vbNewLine & _
        "                ,Name" & vbNewLine & _
        "                ,Month" & vbNewLine & _
        "                ,Year) " & vbNewLine & _
        "VALUES ('<country>'" & vbNewLine & _
        "       ,'<name>'" & vbNewLine & _
        "       ,'<month>'" & vbNewLine & _
        "       ,'<year>')"
    Const sSQLUpdate As String = _
        "UPDATE Test " & vbNewLine & _
        "SET Month = '<month>'" & vbNewLine & _
        "   ,Year = '<year>'" & vbNewLine & _
        "   ,Name = '<name>'" & vbNewLine & _
        "WHERE Country = '<country>'"
    Dim shtSheetToWork As Worksheet
    Dim lRow As Long, lCol As Long
    Dim sSQL As String
    Dim SplitMonthYear As String
    Dim SplitMonth As String
    Dim SplitYear As String
    Dim SqlRowCount
    Dim LastRow As Long
    Dim mtxRecords As Variant
        With sh
            SplitMonthYear = .Name
            SplitMonth = Left(SplitMonthYear, 3)
            SplitYear = "20" & "" & Right(SplitMonthYear, 2)
            Set RS = CreateObject("ADODB.RecordSet")
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).row
            For lRow = 2 To LastRow
                sSQL = Replace(Replace(sSQLSelect, _
                                    "<country>", .Cells(lRow, "B").Value), _
                            "<name>", .Cells(lRow, "C").Value)
                RS.Open sSQL, Cn, 2, 3 'adOpenDynamic, adLockOptimistic
                mtxRecords = RS.GetRows
                If mtxRecords(0, 0) = 0 Then
                    sSQL = Replace(Replace(Replace(Replace(sSQLInsert, _
                                                        "<country>", .Cells(lRow, "B").Value), _
                                                "<name>", .Cells(lRow, "C").Value), _
                                        "<month>", SplitMonth), _
                                "<year>", SplitYear)
                    Cn.Execute sSQL
                    sSQL = Replace(Replace(Replace(Replace(sSQLUpdate, _
                                                        "<country>", .Cells(lRow, "B").Value), _
                                                "<name>", .Cells(lRow, "C").Value), _
                                        "<month>", SplitMonth), _
                                "<year>", SplitYear)
                    Cn.Execute sSQL
                End If
            Next lRow
        End With
    End Sub

  3. #3
    Hi Bob,

    Many thanks for your reply.

    Actually i'm new to excel vba coding.But some how i am doing this.

    I have tried your code and i am getting the following error.Can you please solve it.

    Click image for larger version. 

Name:	Error.PNG 
Views:	858 
Size:	31.1 KB 
ID:	708
    Sorry to say that i am not able to understand your code exactly. Can you please add the comments to the lines of your code.

    Sorry for giving the trouble.Because i can't go forward without understanding the code.

    Please consider my request.


  4. #4

    I have found the solution for the error.

    This is because of table creation(I didn't give the Identity constraint for the column [ID]).

    The new problem is i am not getting the 2 sheets (currmonth and prevmonth) data into the sqltable.It is showing either one sheet data only.
    I want 2 sheets data in to the sql table.

    Please help me in this.


  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version

    In your original code you were trying to insert the ID into the table on news, but you had specified ID as an identity column. SO I removed the ID on the insert, and thus the data for Jul will update the Jun data as the country and the name are identical. I cannot see how you got that Null error if you were defining the ID as an identity column.

    If you want both Jun and Jul data to go into the table, then you need to decide what the key is. Is it ID? I struggle to see the relevance of ID when the real key would be month & year, that is what I would use if both sheets of data are required. And if so, would there ever be updates, wouldn't it all just be inserts.

    You need to make these design decisions before the code can be finished.

  6. #6

    I want to catch the ID comming from the sqlTable like this but i am not succeeded
    ColA = shtSheetToWork.Cells(lRow, 1)
    ColB = shtSheetToWork.Cells(lRow, 2)
    SQLSelect = "select ID from Test where Country= ' " & ColA & " ' and Name=' " & ColB & " ' and Month=' " & SplitMonth& " ' and Year=' " & SplitYear & " ' "
    Cn.Execute SQLSelect
    Here how can i catch the result in a variable.

    Please help me to achieve this.


  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    You totally ignored every point I made, every question I asked. You are telling me what you are trying to do rather than what you want to do, which quite honestly is irrelevant to me as I know you don't really know how to do it.

  8. #8

    Sorry for giving the trouble to you.

    Hope this is the last post on this one from my side and expecting the reply from you.

    I am able to execute the query with the values hold in ColA,ColB,SplitMonth,SplitYear for each iteration and I'm getting the ID's too in sql.

    But what my question is why can't i get the ID for each iteration in excel from sql table and if the ID holds some value,i will update the record with that ID other wise i can insert the record.

    weather am i going or thinking in a not possible way?
    Please gave clarity on this one.

    Thanks for your patience and replies.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts