|
请高手帮忙优化一下代码:
Sub Rept3() '定义过程名称
Dim i, j, k, l, m, n, o, p As Integer, sht As Worksheet 'i,j为整数变量;sht 为excel工作表对象变量,指向某一工作表
Dim cn As New ADODB.Connection '定义数据链接对象 ,保存连接数据库信息;请先添加ADO引用
Dim rs As New ADODB.Recordset '定义记录集对象,保存数据表
Dim strCn As String, strSQL As String '字符串变量
strCn = "Provider=sqloledb;Server=;Database=jmsys;Uid=sa;Pwd=;" '定义数据库链接字符串
strSQL = "select child.儿童姓名,child.儿童性别,child.出生日期,child.户籍属性,child.手机号码,child.在册情况,child.父亲姓名,child.母亲姓名,child.家庭电话,child.通讯地址 from child where 出生日期>='2008-01-01' and 出生日期<='2008-02-28' AND (child.在册情况 Not Like '%J%' And child.在册情况 Not Like '%K%' And child.在册情况 Not Like '%L%' And child.在册情况 Not Like '%M%' And child.在册情况 Not Like '%N%' And child.在册情况 Not Like '%W%' And child.在册情况 Not Like '%S%' And child.在册情况 Not Like '%8%' And child.在册情况 Not Like '%9%' )"
'定义SQL查询命令字符串
cn.Open strCn '与数据库建立连接,如果成功,返回连接对象cn
rs.Open strSQL, cn '执行strSQL所含的SQL命令,结果保存在rs记录集对象中
i = 3
ii = 1
j = 4
k = 4
l = 5
m = 7
mm = 9
n = 6
nn = 10
o = 8
oo = 11
p = 8
Set sht = ThisWorkbook.Worksheets("Tzd") '把sht指向当前工作簿的sheet1工作表
Do While Not rs.EOF '当数据指针未移到记录集末尾时,循环下列操作
sht.Cells(ii, 1) = "***卫生院预防接种通知单"
sht.Cells(j, 4) = rs("儿童姓名") '把当前记录的字段的值保存到sheet1工作表的第i行第1列
sht.Cells(k, 5) = rs("出生日期")
sht.Cells(i, 1) = rs("父亲姓名")
sht.Cells(l, 5) = rs("家庭电话")
sht.Cells(l, 6) = rs("手机号码")
sht.Cells(n, 4) = rs("通讯地址")
sht.Cells(i, 2) = "家长:"
sht.Cells(j, 2) = "请携带你家小孩"
sht.Cells(j, 6) = "(出生)"
sht.Cells(l, 1) = "按下面时间到横泾卫生院"
sht.Cells(l, 4) = "接种疫苗。"
sht.Cells(m, 1) = "----------------------------------------------------"
sht.Cells(mm, 1) = "----------------------------------------------------"
sht.Cells(n, 2) = "家庭地址:"
sht.Cells(o, 1) = "2009年4月5日"
sht.Cells(p, 3) = "接种麻疹疫苗"
sht.Cells(nn, 1) = "各位家长请注意,来接种请带接种证. 应缴费0.00元"
sht.Cells(oo, 1) = "附注:请家长把手机号码写上,以便今后联系!"
rs.MoveNext '把指针移向下一条记录
i = i + 12 'i加1,准备把下一记录相关字段的值保存到工作表的下一行
ii = ii + 12
j = j + 12
k = k + 12
l = l + 12
m = m + 12
mm = mm + 12
n = n + 12
nn = nn + 12
o = o + 12
oo = oo + 12
p = p + 12
Loop '循环
rs.Close '关闭记录集,至此,程序将把某数据表的字段保存在excel工作表sheet1中,行数等于数据表的记录数
cn.Close '关闭数据库链接,释放资源
End Sub
另外:如何把这个查询结果返回到单元格中:"select count(*) from child where 出生日期>='2008-01-01' and 出生日期<='2008-02-28' AND (child.在册情况 Not Like '%J%' And child.在册情况 Not Like '%K%' And child.在册情况 Not Like '%L%' And child.在册情况 Not Like '%M%' And child.在册情况 Not Like '%N%' And child.在册情况 Not Like '%W%' And child.在册情况 Not Like '%S%' And child.在册情况 Not Like '%8%' And child.在册情况 Not Like '%9%' )"
[ 本帖最后由 samica 于 2009-3-25 16:25 编辑 ] |
|