VBA改写VB 排序
Set xl = New Excel.Applicationxl.Visible = True '设置EXCEL可见
xl.Workbooks.Add
xl.Workbooks.Open ("C:\Documents and Settings\Administrator\桌面\exportExcel.xls") '打开网页保存的EXCLE文档
Rows("1:1").Select '选择并删除第一列
Selection.Delete Shift:=xlUp
Dim rng As Range '赋予rng变量Range(单元格)属性
Dim i As Integer, j As Integer '计算exportExcel文件中的行、列数
Set rng = xl.ActiveSheet.UsedRange
i = rng.Rows.Count
j = rng.Columns.Count
Debug.Print i
Debug.Print j
EXCELAPP.ActiveSheet.Range("A1:P99").Sort Key1:=Range("H1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
执行后能完成排序,但是弹出窗口提示“实时错误424,要求对象”,请问这个怎么改 --------------------编程问答-------------------- EXCELAPP没有定义。
VBA里没有这个关键字。 --------------------编程问答--------------------
Private Sub Cdload_Click()
Set xl = New excel.Application
xl.Visible = True '设置EXCEL可见
xl.Workbooks.Add
xl.Workbooks.Open ("C:\Documents and Settings\Administrator\桌面\exportExcel.xls") '打开网页保存的EXCLE文档
Rows("1:1").Select '选择并删除第一列
Selection.Delete Shift:=xlUp
Dim rng As Range
Dim i As Integer, j As Integer '计算exportExcel文件中的行、列数
Set rng = xl.ActiveSheet.UsedRange
i = rng.Rows.Count
j = rng.Columns.Count
Debug.Print i
Debug.Print j
xl.Range("A1:P999").Sort Key1:=Range("H1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
End Sub
我想改进一下不用A1:P999,因为之前已经计算出有多少行,并将行数赋予变量i,我想改成排序时候的区域为“A1到P的第i行”(A1:Pi),请问这个怎么实现,应该添加什么,没法直接改成("A1:Pi") --------------------编程问答-------------------- 要这样
dim strRange as string
strRange ="A1:P" & i
xl.Range(strRange).
--------------------编程问答-------------------- xl.Range(.Cells(1, 1), .Cells(16, i)).…… --------------------编程问答-------------------- up............ --------------------编程问答--------------------
Private Sub Cdload_Click()--------------------编程问答-------------------- 楼上的码代可用。。。 --------------------编程问答--------------------
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
xlApp.Visible = True
'xlApp.Workbooks.Add '<-多余'
Set xlBook = xlApp.Workbooks.Open("C:\Documents and Settings\Administrator\桌面\exportExcel.xls")
Set xlSheet = xlBook.Sheets(1)
xlSheet.Rows("1:1").Delete Shift:=xlUp
Dim rng As Range
Dim i As Integer, j As Integer
Set rng = xlSheet.UsedRange
i = rng.Rows.Count
j = rng.Columns.Count
Debug.Print i
Debug.Print j
xlSheet.Range("A1:P" & i).Sort Key1:=xlSheet.Range("H1"), Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin
Set xlSheet = Nothing
xlBook.Save
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
补充:VB , VBA