Dim MyExcel As New Excel.ApplicationMyExcel.Columns("E:E").Select
MyExcel.Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal我想对myexcel中的E列进行排序,可以用以上的语句不能执行排序操作,请问怎么办?
MyExcel.Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal我想对myexcel中的E列进行排序,可以用以上的语句不能执行排序操作,请问怎么办?
MyExcel.Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal
Set MyExcel = CreateObject("Excel.Application")******************** MyExcel.Range("E2:E20").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal不执行排序也不报错,快疯了?
MyExcel.Range("E2:E20").Select
MyExcel.Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal 会报以下错误:
实时错误"1004"
排序引用无效.请确保它在所要排序的数据内,并且第一个"排序依据"框不相同且不为空.
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim i As Integer Set xlapp = CreateObject("Excel.Application")
Set xlbook = xlapp.Workbooks.Open("e:\test2.xls")
Set xlsheet = xlbook.Worksheets(1)
xlsheet.Range("E2:E20").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal
xlapp.Workbooks.Close
Set xlapp = NothingEnd Sub
myexcel是我程序刚生成的一个excel文件,还没有保存
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal 这两个根本对不上,看我给你的代码比较一下
Set xlapp = CreateObject("Excel.Application")
Set MyExcel = CreateObject("Excel.Application")
MyExcel.Workbooks.Add '新建excel文档
MyExcel.Visible = TrueWith MyExcel.ActiveSheet.PageSetup '打印表头
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
'.Orientation = xlLandscape
.CenterFooter = "第 &P 页"
End With
Set xlbook = xlapp.Workbooks.Open("d:\user.xls")
Set xlsheet = xlbook.Worksheets("user") '设置活动工作表
xlsheet.PageSetup.Orientation = xlLandscape '横向打印
xlsheet.Select
Set rng = xlapp.ActiveSheet.UsedRange
i = rng.Rows.Count '得到当月购电总记录数
j = rng.Columns.Count '得到购电表列数
lsStr = "I" + CStr(i - 2)
MyExcel.Range("A1", lsStr).Borders.LineStyle = 1 '设置新表格样式
MyExcel.Cells(1, 1) = "用户表号"
MyExcel.Cells(1, 2) = "用户名称"
MyExcel.Cells(1, 3) = "户号"
MyExcel.Cells(1, 4) = "用户地址"
MyExcel.Cells(1, 5) = "电价"
MyExcel.Cells(1, 6) = "购电量"
MyExcel.Cells(1, 7) = "购电金额"
MyExcel.Cells(1, 8) = "购电日期"
MyExcel.Cells(1, 9) = "优惠电价"
For A = 4 To i
MyExcel.Rows(A - 2).RowHeight = 22
Next
MyExcel.Rows(1).RowHeight = 40
MyExcel.Rows(i).RowHeight = 26
MyExcel.Rows(i - 1).RowHeight = 30
MyExcel.Cells.Select
MyExcel.Selection.Font.Size = 10
MyExcel.Selection.Columns.AutoFit
MyExcel.ActiveSheet.Cells(1, 4).ColumnWidth = 10 '用户地址栏列宽
MyExcel.ActiveSheet.Cells(1, 3).ColumnWidth = 3 '用户号
MyExcel.ActiveSheet.Cells(1, 6).ColumnWidth = 9 '购电量列宽
MyExcel.ActiveSheet.Cells(1, 5).ColumnWidth = 6 '电价列宽
MyExcel.ActiveSheet.Cells(1, 7).ColumnWidth = 9 '购电金额列宽
MyExcel.ActiveSheet.Cells(1, 8).ColumnWidth = 8 '购电日期列宽
MyExcel.ActiveSheet.Cells(1, 9).ColumnWidth = 7 '优惠电价列宽
MyExcel.ActiveSheet.Cells(1, 2).ColumnWidth = 14 '用户名称列宽
MyExcel.ActiveSheet.Cells(1, 1).ColumnWidth = 8 '表号列宽
MyExcel.Columns(1).HorizontalAlignment = xlCenter
MyExcel.Columns(3).HorizontalAlignment = xlCenter
MyExcel.Columns(5).HorizontalAlignment = xlCenter
MyExcel.Rows(i - 1).HorizontalAlignment = xlCenter
MyExcel.Rows(i).HorizontalAlignment = xlCenter
MyExcel.Rows(1).HorizontalAlignment = xlCenter
MyExcel.Rows(1).Select
MyExcel.Selection.Font.Bold = True '加粗
'MyExcel.Selection.Font.Color = RGB(255, 0, 0) '颜色
'**************************写数据进excel*************************************
T1 = 0 '清零购电量
T2 = 0 '清零购电金额
For A = 4 To i
Sql (xlsheet.Cells(A, 1).Formula) '取得数据库中数据
If IsNumeric(xlsheet.Cells(A, 8).Formula) Then '判断购电金额是否为空
MyExcel.Cells(A - 2, 1) = Rs.Fields("meternum").Value '表号
MyExcel.Cells(A - 2, 2) = Rs.Fields("username").Value '用户名称
MyExcel.Cells(A - 2, 3) = Rs.Fields("usernumber").Value '用户号
MyExcel.Cells(A - 2, 4) = Replace(Rs.Fields("addr").Value, "_", " ") '用户地址
MyExcel.Cells(A - 2, 5) = xlsheet.Cells(A, 8).Formula / xlsheet.Cells(A, 6).Formula '电价
MyExcel.Cells(A - 2, 6) = xlsheet.Cells(A, 6).Formula '购电量
T1 = T1 + CDbl(xlsheet.Cells(A, 6).Formula)
MyExcel.Cells(A - 2, 7) = xlsheet.Cells(A, 8).Formula '购电金额
If IsNumeric(xlsheet.Cells(A, 8).Formula) Then
T2 = T2 + CDbl(CStr(xlsheet.Cells(A, 8).Formula))
End If
MyExcel.Cells(A - 2, 8) = xlsheet.Cells(A, 5).Formula '购电日期
Else
MyExcel.Cells(A - 2, 1) = Rs.Fields("meternum").Value '表号
MyExcel.Cells(A - 2, 2) = Rs.Fields("username").Value '用户名称
MyExcel.Cells(A - 2, 3) = Rs.Fields("usernumber").Value '用户号
MyExcel.Cells(A - 2, 4) = Replace(Rs.Fields("addr").Value, "_", " ") '用户地址
MyExcel.Cells(A - 2, 5) = xlsheet.Cells(A, 7).Formula / xlsheet.Cells(A, 5).Formula '电价
MyExcel.Cells(A - 2, 6) = xlsheet.Cells(A, 5).Formula '购电量
T1 = T1 + CDbl(xlsheet.Cells(A, 5).Formula)
MyExcel.Cells(A - 2, 7) = xlsheet.Cells(A, 7).Formula '购电金额
If IsNumeric(xlsheet.Cells(A, 7).Formula) Then
T2 = T2 + CDbl(CStr(xlsheet.Cells(A, 7).Formula))
End If
MyExcel.Cells(A - 2, 8) = xlsheet.Cells(A, 4).Formula '购电日期
End If Next
MyExcel.Range("E2:E20").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal xlapp.DisplayAlerts = False '不提示保存对话框
MyExcel.DisplayAlerts = True
Set xlsheet = Nothing
Set xlbook = Nothing
xlapp.Quit
Set xlapp = Nothing
MyExcel.Quit
Set MyExcel = Nothing
添加
Set xlbook2 = MyExcel.Workbooks.add
Set xlsheet2 = xlbook2.Worksheets(1) '设置活动工作表然后用xlsheet2
xlsheet.Application.Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal
实时错误"1004"
排序引用无效.请确保它在所要排序的数据内,并且第一个"排序依据"框不相同且不为空. Set xlapp = CreateObject("Excel.Application")
Set MyExcel = CreateObject("Excel.Application")
MyExcel.Workbooks.Add '新建excel文档
MyExcel.Visible = TrueWith MyExcel.ActiveSheet.PageSetup '打印表头
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
'.Orientation = xlLandscape
.CenterFooter = "第 &P 页"
End With
Set xlbook = xlapp.Workbooks.Open("d:\user.xls")
Set xlsheet = xlbook.Worksheets("user") '设置活动工作表
xlsheet.PageSetup.Orientation = xlLandscape '横向打印
xlsheet.Select
Set rng = xlapp.ActiveSheet.UsedRange
i = rng.Rows.Count '得到当月购电总记录数
j = rng.Columns.Count '得到购电表列数
lsStr = "I" + CStr(i - 2)
MyExcel.Range("A1", lsStr).Borders.LineStyle = 1 '设置新表格样式
MyExcel.Cells(1, 1) = "用户表号"
MyExcel.Cells(1, 2) = "用户名称"
MyExcel.Cells(1, 3) = "户号"
MyExcel.Cells(1, 4) = "用户地址"
MyExcel.Cells(1, 5) = "电价"
MyExcel.Cells(1, 6) = "购电量"
MyExcel.Cells(1, 7) = "购电金额"
MyExcel.Cells(1, 8) = "购电日期"
MyExcel.Cells(1, 9) = "优惠电价"
For A = 4 To i
MyExcel.Rows(A - 2).RowHeight = 22
Next
MyExcel.Rows(1).RowHeight = 40
MyExcel.Rows(i).RowHeight = 26
MyExcel.Rows(i - 1).RowHeight = 30
MyExcel.Cells.Select
MyExcel.Selection.Font.Size = 10
MyExcel.Selection.Columns.AutoFit
MyExcel.ActiveSheet.Cells(1, 4).ColumnWidth = 10 '用户地址栏列宽
MyExcel.ActiveSheet.Cells(1, 3).ColumnWidth = 3 '用户号
MyExcel.ActiveSheet.Cells(1, 6).ColumnWidth = 9 '购电量列宽
MyExcel.ActiveSheet.Cells(1, 5).ColumnWidth = 6 '电价列宽
MyExcel.ActiveSheet.Cells(1, 7).ColumnWidth = 9 '购电金额列宽
MyExcel.ActiveSheet.Cells(1, 8).ColumnWidth = 8 '购电日期列宽
MyExcel.ActiveSheet.Cells(1, 9).ColumnWidth = 7 '优惠电价列宽
MyExcel.ActiveSheet.Cells(1, 2).ColumnWidth = 14 '用户名称列宽
MyExcel.ActiveSheet.Cells(1, 1).ColumnWidth = 8 '表号列宽
MyExcel.Columns(1).HorizontalAlignment = xlCenter
MyExcel.Columns(3).HorizontalAlignment = xlCenter
MyExcel.Columns(5).HorizontalAlignment = xlCenter
MyExcel.Rows(i - 1).HorizontalAlignment = xlCenter
MyExcel.Rows(i).HorizontalAlignment = xlCenter
MyExcel.Rows(1).HorizontalAlignment = xlCenter
MyExcel.Rows(1).Select
MyExcel.Selection.Font.Bold = True '加粗
'MyExcel.Selection.Font.Color = RGB(255, 0, 0) '颜色
'**************************写数据进excel*************************************
T1 = 0 '清零购电量
T2 = 0 '清零购电金额
For A = 4 To i
Sql (xlsheet.Cells(A, 1).Formula) '取得数据库中数据
If IsNumeric(xlsheet.Cells(A, 8).Formula) Then '判断购电金额是否为空
MyExcel.Cells(A - 2, 1) = Rs.Fields("meternum").Value '表号
MyExcel.Cells(A - 2, 2) = Rs.Fields("username").Value '用户名称
MyExcel.Cells(A - 2, 3) = Rs.Fields("usernumber").Value '用户号
MyExcel.Cells(A - 2, 4) = Replace(Rs.Fields("addr").Value, "_", " ") '用户地址
MyExcel.Cells(A - 2, 5) = xlsheet.Cells(A, 8).Formula / xlsheet.Cells(A, 6).Formula '电价
MyExcel.Cells(A - 2, 6) = xlsheet.Cells(A, 6).Formula '购电量
T1 = T1 + CDbl(xlsheet.Cells(A, 6).Formula)
MyExcel.Cells(A - 2, 7) = xlsheet.Cells(A, 8).Formula '购电金额
If IsNumeric(xlsheet.Cells(A, 8).Formula) Then
T2 = T2 + CDbl(CStr(xlsheet.Cells(A, 8).Formula))
End If
MyExcel.Cells(A - 2, 8) = xlsheet.Cells(A, 5).Formula '购电日期
Else
MyExcel.Cells(A - 2, 1) = Rs.Fields("meternum").Value '表号
MyExcel.Cells(A - 2, 2) = Rs.Fields("username").Value '用户名称
MyExcel.Cells(A - 2, 3) = Rs.Fields("usernumber").Value '用户号
MyExcel.Cells(A - 2, 4) = Replace(Rs.Fields("addr").Value, "_", " ") '用户地址
MyExcel.Cells(A - 2, 5) = xlsheet.Cells(A, 7).Formula / xlsheet.Cells(A, 5).Formula '电价
MyExcel.Cells(A - 2, 6) = xlsheet.Cells(A, 5).Formula '购电量
T1 = T1 + CDbl(xlsheet.Cells(A, 5).Formula)
MyExcel.Cells(A - 2, 7) = xlsheet.Cells(A, 7).Formula '购电金额
If IsNumeric(xlsheet.Cells(A, 7).Formula) Then
T2 = T2 + CDbl(CStr(xlsheet.Cells(A, 7).Formula))
End If
MyExcel.Cells(A - 2, 8) = xlsheet.Cells(A, 4).Formula '购电日期
End If Next
Set xlbook1 = MyExcel.Workbooks.Add
Set xlsheet1 = xlbook1.Worksheets(1) '设置活动工作表
xlsheet1.Range("E2:E20").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal
xlapp.DisplayAlerts = False '不提示保存对话框
MyExcel.DisplayAlerts = True
Set xlsheet = Nothing
Set xlbook = Nothing
xlapp.Quit
Set xlapp = Nothing
MyExcel.Quit
Set MyExcel = Nothing
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
' Dim xlsheet As Excel.Worksheet
' Dim i As Integer Set xlapp = CreateObject("Excel.Application")
Set xlbook = xlapp.Workbooks.Add
' Set xlsheet = xlbook.Worksheets(1)
xlapp.Cells(2, 5) = "3"
xlapp.Cells(3, 5) = "63"
xlapp.Cells(4, 5) = "2"
xlapp.Cells(5, 5) = "77"
xlapp.Cells(6, 5) = "8"
xlapp.Cells(7, 5) = "35"
xlapp.Cells(8, 5) = "22"
xlapp.Range("E2:E20").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal
' xlapp.Workbooks.Close
xlapp.DisplayAlerts = True
xlapp.Quit
Set xlapp = Nothing
End Sub但去掉Set xlbook = xlapp.Workbooks.Add不行
排序引用无效.请确保它在所要排序的数据内,并且第一个"排序依据"框不相同且不为空.
到底是什么问题呢?
14楼的代码不对lz确认数据问题
实时错误"1004"
排序引用无效.请确保它在所要排序的数据内,并且第一个"排序依据"框不相同且不为空.
快疯了
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal