Excel post to SQL

Posted on 2006-05-02
Last Modified: 2010-04-17
I would like to know if there's a way for Excel to post something to an ASP web page that in turn makes modifications to a MSSQL2000 db.

Here's the story: A client wants to have data pulled from the db through asp to Excel. I would like to in turn, send some data back to the SQL db stating that something has been completed on the Excel sheet (i.e. change status to "Shipped" or "Pending").

URLs or samples would be excellent!
Question by:victornegri
    LVL 10

    Author Comment

    An addendum: I also need advice on how to secure this transaction. I can probably create a link with a querystring in Excel and have it post to an ASP page on the srever but I don't want anyone writing to the SQL db via that file. Any advice?
    LVL 7

    Accepted Solution

    You could trip a vb macro, at that point you can do pretty much what you want.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Address = "$A$1" Then
        Application.EnableEvents = False
        Dim xmlhttp as Object
        Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")

        ' Indicate that page that will receive the request and the
        ' type of request being submitted
        xmlhttp.Open "POST", "http://localhost/test.asp", False

        ' Indicate that the body of the request contains form data
        xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

        ' Send the data as name/value pairs
        xmlhttp.send "Id=1&password=xxxx"

        Set xmlhttp = Nothing
        If Target.Value >= 10 Then
             Range("A2").Value = "posted"
           Range("A2").Value = "not posted"
        End If
        Application.EnableEvents = True
    End If

    End Sub
    LVL 7

    Expert Comment

    Oh, as for the security portion, use https and send your username and password or a session id as one of the name/valuepairs

    Yes, I know it looks easy to hack, welcome to the web.  (PS Thats why I suggested https)


    LVL 10

    Author Comment

    Cool... thanks. I'll try it out.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    This is an explanation of a simple data model to help parse a JSON feed
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now