insert into OpenRowSet('microsoft.jet.oledb.4.0' ,'Excel 8.0;hdr=yes;database=d:\temp.XLS;' ,'select * from [orders$]') select * from northwind.dbo.orders
下面进行代码的编写,首先定义窗体级的全局变量,代码如下: Dim conn As New ADODB.Connection Dim rsExport As New ADODB.Recordset 下面在窗体的Load事件中添加如下的代码,进行初始化的工作: Private Sub Form1_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load '联接数据库并打开记录集 conn.CursorLocation = ADODB.CursorLocationEnum.adUseServer conn.Open("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\NWind.mdb;") rsExport.Open("select *from Customers", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic) '初始化对话框 With Dialog1 .Filter = "FoxBase/FoxPro (*.DBF)|*.DBF|Access 8.0(*.MDB)|*.MDB|Excel 8.0(*.XLS)|*.XLS|Paradox 4.x(*.DB)|*.DB" .Title = "导出文件为" End With End Sub 上面的数据为Nwind.mdb数据库,是从SQL Server数据库中导出来的数据,下面在“转换”按钮的Click事件中添加如下的代码,以实现数据格式的转换,具体代码如下: Private Sub BtConvert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtConvert.Click Dim myPath As Object Dim myStr As String Dim myPos As Short Dim mdbTable As String Dim Export_Str As Object Try '******************处理选择的各种表的导出 With Dialog1 If Rbt1.Checked Then .FilterIndex = 1 .ShowDialog() myStr = StrReverse(.FileName) '串取反 myPos = InStr(myStr, "\") '在反字符串中,找从左开始第一个\的位置 myPath = StrReverse(Mid(myStr, myPos)) '取目录部分,并还原. myStr = StrReverse(Strings.Left(myStr, myPos - 1)) '取文件名 Export_Str = "select * into [dBase III;database=" & myPath & "]." & myStr & " from Customers" .DefaultExt = "*.DBF" ElseIf Rbt2.Checked Then mdbTable = InputBox("请给导出到MDB文件的表确定表名") .FilterIndex = 2 .ShowDialog() Export_Str = "select * into [;database=" & .FileName & "]." & mdbTable & " from Customers" .DefaultExt = "*.MDB" ElseIf Rbt3.Checked Then .FilterIndex = 3 .ShowDialog() Export_Str = "select * into [Excel 8.0;database=" & .FileName & "].Customers from Customers" .DefaultExt = "*.XLS" ElseIf Rbt4.Checked Then .FilterIndex = 4 .ShowDialog() myStr = StrReverse(.FileName) '串取反 myPos = InStr(myStr, "\") '在反字符串中,找从左开始第一个\的位置 myPath = StrReverse(Mid(myStr, myPos)) '取目录部分,并还原. myStr = StrReverse(Strings.Left(myStr, myPos - 1)) '取文件名 Export_Str = "select * into [Paradox 4.X;database=" & myPath & "]." & myStr & " from Customers" .DefaultExt = "*.DB" End If End With '*****生成文件 System.Diagnostics.Debug.WriteLine(Export_Str) If rsExport.State = 1 Then rsExport.Close() End If If Dir(Dialog1.FileName) <> "" Then '防用户没选文件 If Dialog1.FilterIndex <> 2 Then Kill((Dialog1.FileName)) End If rsExport.Open(Export_Str, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic) Else rsExport.Open(Export_Str, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic) End If Catch Exit Sub End Try End Sub 上面的代码分别对4种数据格式的情况进行了转换,主要使用了一些SQL导出语句,这些SQL语句可以表示如下: select * into [Excel 8.0;database=导出目录].导出表名 from 表 select * into [FoxPro 2.6;database=导出目录].导出表名 from 表 select * into [FoxPro 2.5;database=同上].导出表名 from 表 select * into [dBase III;database=同上].导出表名 from 表 select * into [Paradox 4.X;database=同上].导出表名 from 表 select * into [;database=C:\temp\xxx.mdb].导出表名 from 表这是一本书上的方法,我想导出为多种格式的,所以想用这种方法,可是正如第一帖所说的那样出现问题
,'Excel 8.0;hdr=yes;database=d:\temp.XLS;'
,'select * from [orders$]')
select * from northwind.dbo.orders
Dim conn As New ADODB.Connection
Dim rsExport As New ADODB.Recordset
下面在窗体的Load事件中添加如下的代码,进行初始化的工作:
Private Sub Form1_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
'联接数据库并打开记录集
conn.CursorLocation = ADODB.CursorLocationEnum.adUseServer
conn.Open("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\NWind.mdb;")
rsExport.Open("select *from Customers", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
'初始化对话框
With Dialog1
.Filter = "FoxBase/FoxPro (*.DBF)|*.DBF|Access 8.0(*.MDB)|*.MDB|Excel 8.0(*.XLS)|*.XLS|Paradox 4.x(*.DB)|*.DB"
.Title = "导出文件为"
End With
End Sub
上面的数据为Nwind.mdb数据库,是从SQL Server数据库中导出来的数据,下面在“转换”按钮的Click事件中添加如下的代码,以实现数据格式的转换,具体代码如下:
Private Sub BtConvert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtConvert.Click
Dim myPath As Object
Dim myStr As String
Dim myPos As Short
Dim mdbTable As String
Dim Export_Str As Object
Try
'******************处理选择的各种表的导出
With Dialog1
If Rbt1.Checked Then
.FilterIndex = 1
.ShowDialog()
myStr = StrReverse(.FileName) '串取反
myPos = InStr(myStr, "\") '在反字符串中,找从左开始第一个\的位置
myPath = StrReverse(Mid(myStr, myPos)) '取目录部分,并还原.
myStr = StrReverse(Strings.Left(myStr, myPos - 1)) '取文件名
Export_Str = "select * into [dBase III;database=" & myPath & "]." & myStr & " from Customers"
.DefaultExt = "*.DBF"
ElseIf Rbt2.Checked Then
mdbTable = InputBox("请给导出到MDB文件的表确定表名")
.FilterIndex = 2
.ShowDialog()
Export_Str = "select * into [;database=" & .FileName & "]." & mdbTable & " from Customers"
.DefaultExt = "*.MDB"
ElseIf Rbt3.Checked Then
.FilterIndex = 3
.ShowDialog()
Export_Str = "select * into [Excel 8.0;database=" & .FileName & "].Customers from Customers"
.DefaultExt = "*.XLS"
ElseIf Rbt4.Checked Then
.FilterIndex = 4
.ShowDialog()
myStr = StrReverse(.FileName) '串取反
myPos = InStr(myStr, "\") '在反字符串中,找从左开始第一个\的位置
myPath = StrReverse(Mid(myStr, myPos)) '取目录部分,并还原.
myStr = StrReverse(Strings.Left(myStr, myPos - 1)) '取文件名
Export_Str = "select * into [Paradox 4.X;database=" & myPath & "]." & myStr & " from Customers"
.DefaultExt = "*.DB"
End If
End With
'*****生成文件
System.Diagnostics.Debug.WriteLine(Export_Str)
If rsExport.State = 1 Then
rsExport.Close()
End If
If Dir(Dialog1.FileName) <> "" Then
'防用户没选文件
If Dialog1.FilterIndex <> 2 Then
Kill((Dialog1.FileName))
End If
rsExport.Open(Export_Str, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
Else
rsExport.Open(Export_Str, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
End If
Catch
Exit Sub
End Try
End Sub
上面的代码分别对4种数据格式的情况进行了转换,主要使用了一些SQL导出语句,这些SQL语句可以表示如下:
select * into [Excel 8.0;database=导出目录].导出表名 from 表
select * into [FoxPro 2.6;database=导出目录].导出表名 from 表
select * into [FoxPro 2.5;database=同上].导出表名 from 表
select * into [dBase III;database=同上].导出表名 from 表
select * into [Paradox 4.X;database=同上].导出表名 from 表
select * into [;database=C:\temp\xxx.mdb].导出表名 from 表这是一本书上的方法,我想导出为多种格式的,所以想用这种方法,可是正如第一帖所说的那样出现问题
使用起来是很不方便的