'引用 Microsoft ActiveX Data Objects 2.X Library Private Sub Command1_Click() Dim cn As New ADODB.Connection cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=jtaf;Password=登陆密码;Initial Catalog=sql里的数据库;Data Source=sql服务器别名或IP" cn.CursorLocation = adUseClient cn.Open 'sqq导入Excel: cn.Execute("insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\Test.xls;','select * from [Sheet1$]') select * from table1") cn.Close Set cn = Nothing End Sub
请参考: 在VB工程里增加引用:Microsoft Excel 9.0 Object library 引用的版本号因所装的Office版本不同,需引用相应版本. 以下进行说明: Dim ExlApp As Excel.Application Dim ExlBook As Excel.Workbook Dim ExlSheet As Excel.Worksheet '从数据库中查询数据 sql = "select fd1,fd1 from tbtable" set rs=conn.execute(sql) If Not rs.EOF Then Set ExlApp = CreateObject("Excel.Application") ExlApp.Visible = False '/********************打开Excel报表***************** '打开报表模版文件 Set ExlBook = ExlApp.Workbooks.Open(App.Path & "\Report_Mod\mod1.xls") Set ExlSheet = ExlBook.Worksheets(1) ExlSheet.Activate Row = 4 With ExlSheet .Cells(3, 1) = "统计日期:" & strBdate & " 至 " & strEdate Do While Not rs.EOF Row = Row + 1
Set ExlSheet = Nothing Set ExlBook = Nothing Set ExlApp = Nothing MsgBox "生成完毕,文件名:" & strExcelfile, vbOKOnly, "信息提示" Else ExlApp.Visible = True End If Else MsgBox "没有符合条件的返还记录。", vbOKOnly, strmsg End If
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=jtaf;Password=登陆密码;Initial Catalog=sql里的数据库;Data Source=sql服务器别名或IP"
cn.CursorLocation = adUseClient
cn.Open 'sqq导入Excel:
cn.Execute("insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\Test.xls;','select * from [Sheet1$]') select * from table1") cn.Close
Set cn = Nothing
End Sub
在VB工程里增加引用:Microsoft Excel 9.0 Object library
引用的版本号因所装的Office版本不同,需引用相应版本.
以下进行说明:
Dim ExlApp As Excel.Application
Dim ExlBook As Excel.Workbook
Dim ExlSheet As Excel.Worksheet
'从数据库中查询数据
sql = "select fd1,fd1 from tbtable"
set rs=conn.execute(sql)
If Not rs.EOF Then
Set ExlApp = CreateObject("Excel.Application")
ExlApp.Visible = False
'/********************打开Excel报表*****************
'打开报表模版文件
Set ExlBook = ExlApp.Workbooks.Open(App.Path & "\Report_Mod\mod1.xls")
Set ExlSheet = ExlBook.Worksheets(1)
ExlSheet.Activate
Row = 4
With ExlSheet
.Cells(3, 1) = "统计日期:" & strBdate & " 至 " & strEdate
Do While Not rs.EOF
Row = Row + 1
.Cells(Row, 1) = rs("fd1")
.Cells(Row, 2) = (rs("fd2)
Loop
'增加边框
.Range(.Cells(5, 1), .Cells(Row, 2)).Borders.LineStyle = xlContinuous
End With
strExcelfile = Year(Now) & Right("00" & Month(Now), 2) & Right("00" & Day(Now), 2) & Right("00" & Hour(Now), 2) & Right("00" & Minute(Now), 2) & Right("00" & Second(Now), 2)
strExcelfile = App.Path & "\Report\" & strExcelfile & ".xls"
ExlSheet.SaveAs strExcelfile
If MsgBox("是否查看?", 32 + 4 + 256, strmsg) = vbNo Then
ExlBook.Close
ExlApp.Quit
Set ExlSheet = Nothing
Set ExlBook = Nothing
Set ExlApp = Nothing
MsgBox "生成完毕,文件名:" & strExcelfile, vbOKOnly, "信息提示"
Else
ExlApp.Visible = True
End If
Else
MsgBox "没有符合条件的返还记录。", vbOKOnly, strmsg
End If