1180
社区成员
- 主页
请问有关用ADO导出到EXCEL的问题!
我想把某个表中的数据导出到EXCEL表格中,但是要直接用单条SQL语句实现,那这条语句该怎么写呢！
...全文
请发表友善的回复…
发表回复
jtkkjtkk 2004-10-12
SELECT * INTO [Excel 8.0;Database=c:\book1.xls].[Sheet1] FROM project
online 2004-10-12
http://www.microsoft.com/china/community/Column/32.mspx
hsanzhang 2004-10-12
Private Sub form_load()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim appxls As New Excel.Application
Dim bokxls As New Excel.Workbook
Dim shetxls As New Excel.Worksheet
Dim strsql As String
With cn
.Provider = "sqloledb"
.ConnectionString = "data source=hsansql;initial catalog=stock;user id=;password="
.Open
End With
strsql = "select * from tab_spl_detial"
With rs
.CursorLocation = adUseClient
.ActiveConnection = cn
.CursorType = adOpenDynamic
.Open strsql
End With
Set bokxls = appxls.Workbooks.Add
Set shetxls = appxls.Worksheets.Add
shetxls.Cells.CopyFromRecordset rs
appxls.Visible = True
End Sub
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim appxls As New Excel.Application
Dim bokxls As New Excel.Workbook
Dim shetxls As New Excel.Worksheet
Dim strsql As String
With cn
.Provider = "sqloledb"
.ConnectionString = "data source=hsansql;initial catalog=stock;user id=;password="
.Open
End With
strsql = "select * from tab_spl_detial"
With rs
.CursorLocation = adUseClient
.ActiveConnection = cn
.CursorType = adOpenDynamic
.Open strsql
End With
Set bokxls = appxls.Workbooks.Add
Set shetxls = appxls.Worksheets.Add
shetxls.Cells.CopyFromRecordset rs
appxls.Visible = True
End Sub
tztz520 2004-10-11
Private Sub Command1_Click()
Dim Conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim ExcelApp As New Excel.Application
Dim WorkBookObj As Workbook
Dim SheetObj As Worksheet
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\BakDatabase.mdb"
Conn.Open
Rs.Open "Select * From TrapLog", Conn, adOpenKeyset, adLockOptimistic, adCmdText
'==========================================================================
Set WorkBookObj = ExcelApp.Workbooks.Open(App.Path & "\abc.xls")
Set SheetObj = WorkBookObj.Worksheets(1)
'========================================
SheetObj.Range("A1").CopyFromRecordset Rs
'========================================
With SheetObj.PageSetup '页眉页脚设置
.LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称：" ' & Gsmc
.CenterHeader = "&""楷体_GB2312,常规""我的公司名称&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期："
.RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位："
.LeftFooter = "&""楷体_GB2312,常规""&10制表人："
.CenterFooter = "&""楷体_GB2312,常规""&10制表日期："
.RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"
End With
'========================================
Set SheetObj = Nothing
WorkBookObj.Save
WorkBookObj.Close
Set WorkBookObj = Nothing
ExcelApp.Quit
Set ExcelApp = Nothing
Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
MsgBox "OK!请您打开abc.xls文件察看!"
End Sub
Dim Conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim ExcelApp As New Excel.Application
Dim WorkBookObj As Workbook
Dim SheetObj As Worksheet
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\BakDatabase.mdb"
Conn.Open
Rs.Open "Select * From TrapLog", Conn, adOpenKeyset, adLockOptimistic, adCmdText
'==========================================================================
Set WorkBookObj = ExcelApp.Workbooks.Open(App.Path & "\abc.xls")
Set SheetObj = WorkBookObj.Worksheets(1)
'========================================
SheetObj.Range("A1").CopyFromRecordset Rs
'========================================
With SheetObj.PageSetup '页眉页脚设置
.LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称：" ' & Gsmc
.CenterHeader = "&""楷体_GB2312,常规""我的公司名称&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期："
.RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位："
.LeftFooter = "&""楷体_GB2312,常规""&10制表人："
.CenterFooter = "&""楷体_GB2312,常规""&10制表日期："
.RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"
End With
'========================================
Set SheetObj = Nothing
WorkBookObj.Save
WorkBookObj.Close
Set WorkBookObj = Nothing
ExcelApp.Quit
Set ExcelApp = Nothing
Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
MsgBox "OK!请您打开abc.xls文件察看!"
End Sub