Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Excel post to SQL

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!
0
victornegri
Asked:
victornegri
  • 2
  • 2
1 Solution
 
victornegriAuthor Commented:
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?
0
 
wnrossCommented:
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"
    Else
       Range("A2").Value = "not posted"
    End If
    Application.EnableEvents = True
End If

End Sub
0
 
wnrossCommented:
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)

Cheers,
-Bill

0
 
victornegriAuthor Commented:
Cool... thanks. I'll try it out.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now