Private Sub cmdout_Click()
Dim rst As New ADODB.Recordset
rst.Open "select" & Left(Trim(Text1.Text), Len(Trim(Text1.Text)) - 1) & "from" & combo1.Text & "", cn1, adOpenDynamic, adLockOptimistic
Dim xlsApp As Excel.Application '定义Excel程序
Dim xlsBook As Excel.Workbook '定义工作薄
Dim xlsSheet As Excel.Worksheet '定义工作表
Dim i, j As Long
Set xlsApp = CreateObject("Excel.Application")
'创建Excel应用程序
Set xlsBook = xlsApp.Workbooks.Add '创建工作薄
Set xlsSheet = xlsBook.Worksheets(1) '创建工作表
On Error Resume Next
j = 1
Do Until rst.EOF
For i = 1 To rst.Fields.Count
xlsSheet.Cells(j, i) = rst.Fields(i - 1)
'写入记录集(不包括表头)
Next i
rst.MoveNext
j = j + 1
Loop
xlsApp.Visible = True '显示电子表格
xlsBook.SaveAs App.Path & "\导出数据.xlsx"
Set xlsApp = Nothing '交换控制权给Excel
rst.Close
cn1.Close
Set rst = Nothing
Set cn1 = Nothing
Unload Me
Unload fm
End Sub
Private Sub combo1_Click() '向列表框添加表的字段名称
Dim i As Integer
Dim srs As New ADODB.Recordset
list1.Clear
srs.Open combo1.Text, cn1, adOpenKeyset, adLockOptimistic
i = srs.Fields.Count
For i = 0 To srs.Fields.Count - 1
list1.AddItem srs.Fields(i).Name
Next i
srs.Close
Set srs = Nothing
End Sub
Private Sub img1_Click() '选择文件向组合框添加记录
Dim rs1 As New ADODB.Recordset
cmd00.Filter = "Access文件(*.accdb)|*.accdb|所有文件(*.*)|*.*"
cmd00.CancelError = True
cmd00.DialogTitle = "打开Access文件"
cmd00.ShowOpen
fn = cmd00.FileName
Text1 = cmd00.FileName
If fn = "" Then
MsgBox "请重新选择Access文件!", vbInformation + vbOKOnly
End If
If cn1.State = adStateOpen Then
cn1.Close
combo1.Clear
End If
Call accdbcon
Set rs1 = cn1.OpenSchema(adSchemaTables)
Do Until rs1.EOF
If Left(rs1!table_name, 4) <> "MSys" Then '过滤系统文件名
combo1.AddItem rs1!table_name
End If
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
End Sub
Private Sub list1_ItemCheck(Item As Integer)
Text1.Text = Text1.Text & list1.List(Item) & ","
'把list1所选的字段赋给text1文本框
cmdout.Enabled = True
End Sub
问题
1、想导出的是整个表 而不是表中的一个字段
2、报错怎么解决 调试语句是这句
rst.Open "select" & Left(Trim(Text1.Text), Len(Trim(Text1.Text)) - 1) & "from" & combo1.Text & "", cn1, adOpenDynamic, adLockOptimistic