用vba 导出查询到excel文件,怎么跟在access里查询到的结果不一样?
建立查询“select * from 帐户统计之支行净增”和导出文件有一行记录不同,其他的都一样,会不会是 Rs_Data.Open strOpen, Adocn, adOpenStatic, adLockOptimistic造成的?
Public Sub 导出支行净增()
'*********************************************************
'* 名称:ExporToExcel
'* 功能:导出数据到EXCEL
'* 用法:ExporToExcel(sql查询字符串)
'*********************************************************
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer
Dim Adocn As New ADODB.Connection
Dim strOpen As String
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable
Dim strSQL As String
Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Dim i%
strSQL = "select 支行 from 支行"
Set Cnn = CurrentProject.Connection
Rst.Open strSQL, Cnn, adOpenKeyset, adLockOptimistic
If Not Rst.EOF Then
Rst.MoveFirst
Do While Not Rst.EOF
Set Adocn = CurrentProject.Connection
Adocn.CursorLocation = adUseClient
strOpen = "select * from 帐户统计之支行净增"
Rs_Data.Open strOpen, Adocn, adOpenStatic, adLockOptimistic
If Rs_Data.RecordCount >= 1 Then
Set Cnn = CurrentProject.Connection
Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlApp.Sheets.Add
' Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlApp.Sheets.Add
Set xlSheet = xlBook.Worksheets("sheet5") Irowcount = Rs_Data.RecordCount
Icolcount = Rs_Data.Fields.Count
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Size = 15 '.Name = "黑体"
.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Borders.LineStyle = xlContinuous
.PageSetup.CenterHeader = "&""宋体,加粗""&18支行帐户情况&""宋体,常规"""
.PageSetup.RightMargin = 1
.PageSetup.LeftMargin = 30
End With
xlQuery.FieldNames = True '显示字段名
xlQuery.Refresh xlSheet.Name = "支行帐户情况"
xlBook.SaveAs "D:\temp\帐户情况-" & Rst!支行 & ".xls" 'App.Path & "\" & filename & ".xls"
End If
Set xlSheet = Nothing
xlApp.Application.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Rs_Data.Close
Rst.MoveNext Loop
End IfEnd Sub
建立查询“select * from 帐户统计之支行净增”和导出文件有一行记录不同,其他的都一样,会不会是 Rs_Data.Open strOpen, Adocn, adOpenStatic, adLockOptimistic造成的?
Public Sub 导出支行净增()
'*********************************************************
'* 名称:ExporToExcel
'* 功能:导出数据到EXCEL
'* 用法:ExporToExcel(sql查询字符串)
'*********************************************************
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer
Dim Adocn As New ADODB.Connection
Dim strOpen As String
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable
Dim strSQL As String
Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Dim i%
strSQL = "select 支行 from 支行"
Set Cnn = CurrentProject.Connection
Rst.Open strSQL, Cnn, adOpenKeyset, adLockOptimistic
If Not Rst.EOF Then
Rst.MoveFirst
Do While Not Rst.EOF
Set Adocn = CurrentProject.Connection
Adocn.CursorLocation = adUseClient
strOpen = "select * from 帐户统计之支行净增"
Rs_Data.Open strOpen, Adocn, adOpenStatic, adLockOptimistic
If Rs_Data.RecordCount >= 1 Then
Set Cnn = CurrentProject.Connection
Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlApp.Sheets.Add
' Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlApp.Sheets.Add
Set xlSheet = xlBook.Worksheets("sheet5") Irowcount = Rs_Data.RecordCount
Icolcount = Rs_Data.Fields.Count
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Size = 15 '.Name = "黑体"
.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Borders.LineStyle = xlContinuous
.PageSetup.CenterHeader = "&""宋体,加粗""&18支行帐户情况&""宋体,常规"""
.PageSetup.RightMargin = 1
.PageSetup.LeftMargin = 30
End With
xlQuery.FieldNames = True '显示字段名
xlQuery.Refresh xlSheet.Name = "支行帐户情况"
xlBook.SaveAs "D:\temp\帐户情况-" & Rst!支行 & ".xls" 'App.Path & "\" & filename & ".xls"
End If
Set xlSheet = Nothing
xlApp.Application.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Rs_Data.Close
Rst.MoveNext Loop
End IfEnd Sub
*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码) http://feiyun0112.cnblogs.com/
xlSheet.Range("A2").CopyFromRecordset Rs_Data
替代
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
看看结果
这句话没有加支行条件。2,调试的话,这个追加 [=23] 的那条记录的条件,这样你调试起来就不是半小时了。看看数据库里面 23的字段的数据类型,再看看excel里面的数据类型是不是一致。