ekkehard
Fri Feb 01 08:06:25 CST 2008
jcnone@none.com schrieb:
> Can anyone help me to transfer data results from a SQL query to
> input forms on a web page using vba, scripting, or whatever will work. I am
> manually inputting a series of data to web page forms. The web
> page is not mine and I have no control of the web page. The data
> comes from an SQL query and I want to code the transfer of the
> SQL data set into the web page form fields. I have little
> experience with VBA, or scripting, so please comment any code if possible.
> Thanks for your help.
[...]
This db2wp.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="
http://www.w3.org/1999/xhtml">
<head>
<title>
Db2Wp
</title>
<meta http-equiv = "content-type" content="text/html; charset=iso-8859-1"/>
</head>
<body>
<form name = "frmDb2Wp">
<p>
<input type = "text" name = "sleCatID"/>
</p>
<p>
<input type = "text" name = "sleCatName" id = "sleCatName"/>
</p>
<input type = "submit" onclick = "alert('submit');"/>
</form>
</body>
</html>
fakes a page containg a form with some HTML elements and a submit button.
Mark the different methods those elements are identifiable (name, id,
position in a list of elements of the same type/tag).
This script:
Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
Dim sURL : sURL = "file://" & oFS.GetAbsolutePathName( ".\db2wp.html" )
Dim sMFSpec : sMFSpec = oFS.GetAbsolutePathName( ".\impadoexcel\Nwind.mdb" )
Dim oIE : Set oIE = CreateObject( "InternetExplorer.Application" )
Dim oADOCN : Set oADOCN = CreateObject( "ADODB.Connection" )
Dim sCS : sCS = Join( Array( _
"Provider=Microsoft.Jet.OLEDB.4.0" _
, "Data Source=" & sMFSpec _
), ";" )
Dim sSQL : sSQL = Join( Array( _
"SELECT * FROM Categories" _
), " " )
oIE.navigate sURL
Do Until oIE.readystate = 4 : WScript.Sleep 100 : Loop
oIE.visible = 1
Dim oDOC : Set oDOC = oIE.document
oADOCN.Open sCS
Dim oRS : Set oRS = oADOCN.Execute( sSQL )
oDOC.frmDb2Wp.sleCatID.Value = oRS.Fields( 0 ).Value
oDOC.getElementById( "sleCatName" ).Value = oRS.Fields( "CategoryName" ).Value
oDOC.getElementsByTagName( "input" )( 2 ).click
oRS.Close
oADOCN.Close
WScript.Echo "press enter to terminate"
WScript.Stdin.ReadLine
oIE.Quit
uses IE to get a page (db2wp.html) and ADO to do some SQL (NWind/Northwind).
The lines:
oDOC.frmDb2Wp.sleCatID.Value = oRS.Fields( 0 ).Value
oDOC.getElementById( "sleCatName" ).Value = oRS.Fields( "CategoryName" ).Value
oDOC.getElementsByTagName( "input" )( 2 ).click
demonstrate different ways (name, id, enumeration) to access the HTML
elements.
I hope this will get you started. Use Docs (script??.chm, ado??.chm, msdn)
and/or Google/... to learn about objects like "connection", "recordset", "IE",
"document", and their methods "getElementsByTagName", ...
Feel free to ask for details.