调用方法举例:
ExportDataFromDB "Select objectid,emsname from ems","C:\Users\trainbo\Desktop\1\3.xls"
具体函数:
'sql语句查询并导出到excel
Function ExportDataFromDB(sql,xlsUrl)
Set Excelobj=CreateObject ("Excel.Application")
xlsUrltemp="C:\Users\trainbo\Desktop\1\1.xls" '临时存放的excel,自行修改,预先创建
excelObj.workbooks.open(xlsUrltemp)
Set sheetNew = excelObj.sheets.item(1)
Set Con=CreateObject("ADODB.Connection")
Con.open "DSN=22inoformix" '设置连接字符串,根据本地修改
Set Record=CreateObject("ADODB.Recordset")
Record.open sql,con
Record.MoveFirst
If Record.EOF and Record.BOF Then
Record.Close
Reporter.ReportEvent micFail,"test","query fail"
Else
i = 1
Do While Not Record.EOF
For j = 0 to Record.Fields.Count -1
If i = 1 Then
sheetNew.cells(i,j+1).value = Record.Fields(j).name
Else
sheetNew.cells(i,j+1).value = Record.Fields(j).Value
End If
Next
Record.MoveNext
i = i + 1
Loop
End If
ExcelObj.activeworkbook.saveas xlsUrl
Record.close
Set Record=nothing
Con.close
Set Con=nothing
End Function