建一存储过程,把用于数据查询部分SQL作为入口参数。create procedure datatoexcel @sql varchar(500) as declare @cmd varchar(500) set @cmd = 'bcp "' +rtrim(@sql)+'" queryout d:\temp.xls -c -S you_servername -U sa -P you_password' exec master..xp_cmdshell @cmd 调用: dim sql as string dim cnnstr as string dim cnn as adodb.connection dim rst as adodb.recordset sql = "select top 10 * from you_databasename.dbo.you_tablename" cnnstr = "..." cnn.open cnnstr st rst = cnn.execute("exec datatoexcel '"+sql+"'") 注: bcp(批量copy)功能一览表usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] [-C code page specifier] [-t field terminator] [-r row terminator] [-i inputfile] [-o outfile] [-a packetsize] [-S server name] [-U username] [-P password] [-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values] [-h "load hints"]
下面已调试通过,可以参靠 create procedure datatoexcel @sql varchar(500) as declare @cmd varchar(500) set @cmd = 'bcp "' +rtrim(@sql)+'" queryout d:\temp.xls -c -S ALong_Yue -U sa -P 1234567' exec master..xp_cmdshell @cmd Private Sub Command1_Click() Dim sql As String Dim cnnstr As String Dim cnn As New ADODB.connection Dim rst As New ADODB.Recordset On Error GoTo errlable1 sql = "select top 20 * from tfmsdb.dbo.leave_card" cnnstr = " Provider = SQLOLEDB.1;Password=1234567;Persist Security Info=True;User ID=sa;Initial Catalog=zydb;Data Source=ALong_Yue " cnn.open cnnstr Set rst = cnn.execute("exec datatoexcel '" + sql + "'") Exit Sub errlable1: MsgBox Err.Description Err.Clear End Sub
谢谢前面三位了。
出现这样的错误。如果是DLL,该DLL是否是给ASP调用?
try it
你让excel的visible=true还是false???
false时有时会有多一些的问题出现。
(2) 我的email: [email protected]
你的方法对内存的要求很高啊。因为我的数据量比较大,有没有更好的方法。谢谢各位的帮助!
建一存储过程,把用于数据查询部分SQL作为入口参数。create procedure datatoexcel
@sql varchar(500)
as
declare @cmd varchar(500)
set @cmd = 'bcp "' +rtrim(@sql)+'" queryout d:\temp.xls -c -S you_servername -U sa -P you_password'
exec master..xp_cmdshell @cmd
调用:
dim sql as string
dim cnnstr as string
dim cnn as adodb.connection
dim rst as adodb.recordset
sql = "select top 10 * from you_databasename.dbo.you_tablename"
cnnstr = "..."
cnn.open cnnstr
st rst = cnn.execute("exec datatoexcel '"+sql+"'")
注:
bcp(批量copy)功能一览表usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
create procedure datatoexcel
@sql varchar(500)
as
declare @cmd varchar(500)
set @cmd = 'bcp "' +rtrim(@sql)+'" queryout d:\temp.xls -c -S ALong_Yue -U sa -P 1234567'
exec master..xp_cmdshell @cmd
Private Sub Command1_Click()
Dim sql As String
Dim cnnstr As String
Dim cnn As New ADODB.connection
Dim rst As New ADODB.Recordset
On Error GoTo errlable1
sql = "select top 20 * from tfmsdb.dbo.leave_card"
cnnstr = " Provider = SQLOLEDB.1;Password=1234567;Persist Security Info=True;User ID=sa;Initial Catalog=zydb;Data Source=ALong_Yue "
cnn.open cnnstr
Set rst = cnn.execute("exec datatoexcel '" + sql + "'")
Exit Sub
errlable1:
MsgBox Err.Description
Err.Clear
End Sub