是否有一个本地的Excel类,允许范围被复制和排序/过滤器应用?
我有一个范围我想任意排序和过滤使用vba。但是,我不想让它影响工作表。我想从本质上将范围复制到某些支持过滤和排序的本地类(因此我不必重新发明*)并使用该类将结果返回给调用代码。是否有一个本地的Excel类,允许范围被复制和排序/过滤器应用?
有没有我可以用来达到这个目的的任何课程? ListObject看起来最有希望,但似乎需要绑定到工作表范围才能正常工作。
原来我可以创建一个记录集来做到这一点。与Remou的回答不同,尽管我们不需要在我们的工作表上调用重量级的odbc进程。
以下函数(改编自Mark Nold的答案)将根据提供的范围创建记录集。它假定列标题位于提供的范围的第一行。这可以做得更强大,但它是一个很好的起点
Function CreateRecordSet(rSource As range) As Recordset
' Constants
Const MAX_CHARS = 1200
' Declarations
Dim rs As Recordset
Dim c As Long
Dim r As Long
Dim colCount As Long
Dim rowCount As Long
Dim fldName As String
colCount = rSource.Columns.Count
rowCount = rSource.rows.Count
' Setup record set
Set rs = New Recordset
r = 1 ' assume first row contains headers
For c = 1 To colCount
fldName = rSource.Cells(r, c).Value
rs.Fields.Append fldName, adVarChar, MAX_CHARS
Next c
' Fill record set
rs.Open
r = 2 ' skip header row
For r = 2 To rowCount
rs.AddNew
Debug.Print "row "; r & " of " & rowCount & " created"
For c = 1 To colCount
rs.Fields(c - 1) = CStr(rSource.Cells(r, c).Value)
Debug.Print "-- row(" & r; "): added col " & c & " of " & colCount
Next c
Next r
Set CreateRecordSet = rs
End Function
Sub TestCreateRecordSet()
Dim r As range
Dim rs As Recordset
Set r = range("A1:B4")
Set rs = CreateRecordSet(r)
End Sub
为什么不将数据复制到新的隐藏工作表,在那里执行排序/筛选,然后在完成时将数据复制回来?
你想使用Range类(就像CasperOne所说的)。下面是一些例子VBA代码
Function SortAndFilter(rSource As Range) As Range
Dim rResult As Range
Dim vaTemp As Variant
Dim wsTemp As Worksheet
Dim wbTemp As Workbook
vaTemp = rSource.Value
Set wbTemp = Workbooks.Add
Set wsTemp = wbTemp.Sheets(1)
Set rResult = wsTemp.Range("A1").Resize(UBound(vaTemp, 1), UBound(vaTemp, 2))
rResult.Value = vaTemp
rResult.Sort rResult.Cells(1), xlDescending
Set SortAndFilter = rResult
End Function
Sub Testit()
Dim rTest As Range
Set rTest = SortAndFilter(Selection)
'Do stuff with your range object
rTest.Parent.Parent.Close False 'close temp workbook
End Sub
我遇到了rResult.Value = vaTemp行的问题。 VBA似乎没有让我在函数内设置rResult的值。我读过其他地方VBA不会让你设置一个范围内的功能,只有潜艇。你确定这段代码有效吗?再次感谢您的帮助。 – 2009-01-19 21:39:11
可以使用的记录。这里有一些注意事项:
'Reference: Microsost ActiveX n.n Object Library '
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
'From: http://support.microsoft.com/kb/246335 '
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
'Must have client-side cursor for sorting '
rs.CursorLocation = adUseClient
'Substitute a name range for [Sheet1$] '
'or include a range of cells : [Sheet1&A1:C7] '
strSQL = "SELECT * FROM [Sheet1$] " _
& "WHERE TransID>2 ORDER BY MyTime"
rs.Open strSQL, cn, 3, 3
rs.Filter = "TransID=3"
rs.Sort = "Mytime"
'Write out to another sheet '
Worksheets(2).Cells(2, 1).CopyFromRecordset rs
你会发现这个线程有趣:syncing two lists with VBA
如果您想阅读和分析复杂的数据集可以使用Microsoft ActiveX Data Objects Recordset 2.8 Library
。有了这个,你可以将你的数据读入记录集,然后过滤,排序,追加,删除并将其传递给其他函数。
我经常使用这个,因为我经常需要操作和显示大型数据集。如果它在记录集中,我可以一次又一次地使用相同的操作和演示例程。
有关将数据引入记录集的示例,请参阅Merge Excel Sheets....。在记录集中有数据后,使用r.filter =“ColumnA = 1”或r.sort =“ColumnC,ColumnA”。
这可能就足够了。我会看看。 Ty – 2009-01-13 18:15:38
你可以举一个你用来做这些事的代码的例子吗? – 2009-01-13 20:43:26