VBA 将数据库值存储在变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20949148/
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
VBA storing database values in variables
提问by darkjoe31
I have to write some VBA code in excel to go with my VB.NET program and I am struggling with the basics of VBA as I have not used it before. I found some code which allowed me to get the values from my temporary table and put them directly in appropiate fields in the excel spreadsheet. I am populating a purchase order document, so information passed over are things like subtotal, vat, shipping, total, etc.
我必须在 excel 中编写一些 VBA 代码才能与我的 VB.NET 程序一起使用，而且我正在努力学习 VBA 的基础知识，因为我以前没有使用过它。我找到了一些代码，它允许我从临时表中获取值，并将它们直接放在 Excel 电子表格中的适当字段中。我正在填充采购订单文档，因此传递的信息是小计、增值税、运输、总计等。
This is the code I have used to populate a single cell:
这是我用来填充单个单元格的代码：
'get quantity
strQry = "SELECT quantity from [temp];"
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Open strQry
End With
'append data to document
Worksheets("PurchaseOrder").Range("D22").CopyFromRecordset rs
quantity = rs.Fields("quantity") 'setting the quatity in a variable
The last line of code is my attempt of storing the value of quantity in a variable, which I need to use to calculate the sub total as sub total was not passed over to excel. The code to populate the cells works fine, it is just putting the data in a variable to manipulate which I am struggling with. Populating the cells directly from the database works fine, but I am getting an error on that last line.
最后一行代码是我尝试将数量的值存储在一个变量中，我需要用它来计算小计，因为小计没有传递给 excel。填充单元格的代码工作正常，它只是将数据放入一个变量中以操纵我正在努力处理的变量。直接从数据库填充单元格工作正常，但在最后一行出现错误。
After storing quantity in a variable, I would also like to store the cost per unit in a variable, remove the ￡ sign at the start using a substring equivalent, convert it to a decimal then times the cost per unit by the quantity to get the sub total.
在变量中存储数量后，我还想将单位成本存储在变量中，使用等效的子字符串在开始时删除 ￡ 符号，将其转换为小数，然后将单位成本乘以数量得到小计。
I tried using this following code:
我尝试使用以下代码：
'get price
strQry = "SELECT costPerUnit from [temp];"
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Open strQry
End With
'append data to document
Worksheets("PurchaseOrder").Range("N22").CopyFromRecordset rs
costPerUnit = Right(rs(0), Len(costPerUnit) - 1) 'setting the cost per unit in a variable
subtotal = costPerUnit * quantity
Worksheets("PurchaseOrder").Cells("Q47").Value = "￡ " & subtotal
Any help is appreciated. Thankyou.
任何帮助表示赞赏。谢谢你。
回答by Graffl
Ok. So until filling the of the Recordset you have done it correct and then you have to see that the Recordset is more like a table and can have none or multiple rows with multiple columns.
First you have to check if the query returned any result or if the Recordset is empty (rs.BOFand rs.EOFare true). Then you would loop through the rows (rs.MoveNext).
To access a single value you can either give the index of the column or the column name.
好的。因此，在填充 Recordset 之前，您已经正确完成了它，然后您必须看到 Recordset 更像是一个表，可以没有行或具有多列的多行。首先，您必须检查查询是否返回任何结果，或者 Recordset 是否为空（rs.BOF并且rs.EOF为真）。然后您将遍历行 ( rs.MoveNext)。要访问单个值，您可以提供列的索引或列名。
The following example loops through rows and through columns but also extracts again the column "quantity" at the end:
以下示例循环遍历行和列，但还会在末尾再次提取列“数量”：
If (rs.EOF) And (rs.BOF) Then
Exit Function
Else
rs.MoveFirst
Do Until rs.EOF
For j = 1 To rs.Fields.Count
valueOfColumnJ = rs.Fields(j - 1).Value
Next j
quantity = rs.Fields("quantity")
rs.MoveNext
Loop
End If
回答by Dick Kusleika
Recordsets have a cursor and any reference to the recordset is going to return properties (like the value of a field) based on where that cursor is.
记录集有一个游标，对记录集的任何引用都将根据该游标的位置返回属性（如字段的值）。
When you called CopyFromRecorset, you moved the cursor to the end (EOF = True). Then when you tried to get the Fields("quantity"), there was no active record of the recordset, so you got an error.
当您调用 CopyFromRecorset 时，您将光标移动到末尾（EOF = True）。然后，当您尝试获取 Fields("quantity") 时，记录集没有活动记录，因此出现错误。
You could have first done rs.MoveFirstif you have the right type of recordset. Then quantity would have equaled the quantity field from the first record. Probably not what you want.
rs.MoveFirst如果您拥有正确类型的记录集，您可以先完成。那么数量将等于第一条记录中的数量字段。可能不是你想要的。
There is not a one-liner (as far as I know) that will get you the total of all the fields in your recordset. You have to loop like Graffl shows.
没有一个单行（据我所知）可以让您获得记录集中所有字段的总数。你必须像 Graffl 表演那样循环播放。
What might be the better path is to use Excel. You already have the data in there, so insert a formula to get the subtotal you want.
使用 Excel 可能是更好的方法。您已经在那里有了数据，所以插入一个公式来获得您想要的小计。
Worksheets("PurchaseOrder").Cells("Q47").Formula = _
"=SUMPRODUCT(P23:P46*Q23:A46)"
or something like that.
或类似的东西。
