转置 CopyFromRecordset Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13141639/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transposing CopyFromRecordset Excel VBA
提问by Kristina
I have the following code in my Excel VBA that copies data from a table in SQL into Excel. This data is being inserted horizontally starting on cell C2 but I want it to be inserted vertically on column C.
我的 Excel VBA 中有以下代码，可将 SQL 中的表中的数据复制到 Excel 中。此数据从单元格 C2 开始水平插入，但我希望它垂直插入列 C。
Sheets("Control").Range("C2").CopyFromRecorset rsPubs
Where rsPubsis my ADO connection.
rsPubs我的 ADO 连接在哪里。
Basically, I just want this data transposed. What's an efficient way of doing this? Any help will be appreciated!
基本上，我只想转置这些数据。这样做的有效方法是什么？任何帮助将不胜感激！
Edit:This is how rsPubsis created (the connection works fine as I'm actually getting the data):
编辑：这是如何rsPubs创建的（连接工作正常，因为我实际上正在获取数据）：
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "SELECT * FROM Analytics.dbo.XBodoffFinalAllocation"
' Copy the records into cell B3 on Sheet1.
Sheets("Control").Range("C2").CopyFromRecordset rsPubs
' Tidy up
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
采纳答案by Jook
I cannot test this currently, but you could:
我目前无法对此进行测试，但您可以：
Sheets("Control").Range("C2").CopyFromRecorset rsPubs 'copy your data
Sheets("Control").Range("C2").Copy 'copy the data into clipboard
Sheets("Control").Range("C2").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, True, True
Also you could use the Transpose Worksheetfunction - however, I don't quite see a way right now to do this directly, expect your input data is transposed already.
您也可以使用转置工作表功能 - 但是，我现在还没有看到直接执行此操作的方法，希望您的输入数据已经转置。
Edit:
编辑：
here is a nice official example and further informations on this topic: http://support.microsoft.com/kb/246335/en-us
这是一个很好的官方示例和有关此主题的更多信息：http: //support.microsoft.com/kb/246335/en-us
especially the "using GetRows" section.
尤其是“使用 GetRows”部分。
Edit2:
编辑2：
this should do
这应该做
Dim resultset As Variant
Dim result As Variant
resultset = rsPubs.GetRows
result = Application.WorksheetFunction.Transpose(resultset)
Sheets("Control").Range("C2").Resize(UBound(result, 1), UBound(result, 2)) = result
http://www.teachexcel.com/excel-help/excel-how-to.php?i=147811
http://www.teachexcel.com/excel-help/excel-how-to.php?i=147811
回答by Tim Williams
Untested:
未经测试：
Sub CopyTransposed(rng As Range, rs As ADODB.Recordset)
Dim x As Long, y As Long
x = 0
Do While Not rs.EOF
For y = 0 To rs.Fields.Count - 1
rng.Offset(y, x).Value = rs.Fields(y).Value
Next y
x = x + 1
rs.MoveNext
Loop
End Sub
回答by John Denniston
Edit 2 in the accepted answer doesn't work for me, but the following does (see http://www.mrexcel.com/forum/excel-questions/513845-copyfromrecordset-transpose.htmlfor my source):
已接受答案中的编辑 2 对我不起作用，但以下内容适用（请参阅http://www.mrexcel.com/forum/excel-questions/513845-copyfromrecordset-transpose.html以获取我的来源）：
Public Sub PlaceTransposedResults(oResults As ADODB.Recordset, rTarget As Range)
Dim vTransposed As Variant
If Not oResults.EOF Then
vTransposed = oResults.GetRows
rTarget.Resize(UBound(vTransposed, 1) + 1, UBound(vTransposed, 2) + 1) = vTransposed
End If
End Sub
(this assummes that you haven't changed the array base with the OPTION BASEand that your version of Excel has Range.Resizeand that oResults is never nothing)
（这假设您没有使用OPTION BASE和您的 Excel 版本更改数组基数，Range.Resize并且 oResults 永远不会什么都没有）
One tweak on this is to make this a function and return the correctly sized range - useful if you want to resize a named range to cover the result set.
对此的一个调整是使其成为一个函数并返回正确大小的范围 - 如果您想调整命名范围的大小以覆盖结果集，则非常有用。
Another likely tweak is that you may want to optionally allow the user to ask for the field names to be added as the in the first column. I have found nothing better than:
另一个可能的调整是您可能希望有选择地允许用户要求将字段名称添加为第一列。我发现没有比以下更好的了：
Dim ix As Integer
For ix = 0 To oResults.Fields.Count - 1
rTarget.Offset(ix, 0) = oResults.Fields(ix).Name
Next ix
(of course you then have to offset your main results by 1 column in this case).
（当然，在这种情况下，您必须将主要结果抵消 1 列）。
回答by Robert Co
My suggestion is not to use VBA, at all. Microsoft already provided you the facility to import data from a database.
我的建议是根本不要使用 VBA。Microsoft 已经为您提供了从数据库导入数据的工具。
Data -> Import External Data
It will, then, create a QueryTable inside the sheet where you can just right-click and refresh in a regular basis. Another bonus is that you don't get the nasty macro warning. The QueryTable can be a table, query or stored procedure.
然后，它会在工作表内创建一个 QueryTable，您只需右键单击并定期刷新即可。另一个好处是您不会收到讨厌的宏警告。QueryTable 可以是表、查询或存储过程。
Try it!
尝试一下！
