如何在vb6中对现有记录集运行查询 [英] How to run query on existing recordset in vb6

问题描述

您好，



我有一个表TableA，列名为Name，Y / N，Value1，Value2。这些数据在excel文件中。

并且每个名称都有多个条目。

1.想要在此表中添加一列并根据Y /计算值N列并填写新列。意思是如果Y / N列有Y然后将value1放在value3列中，如果它是N，则将value2放在值3列中

2.现在想要组名并想要计算value3的总和

3.关于Sum值想要做一些计算。



能否请你如何在VBA中解决这个问题。

我的代码如下

 私人  Sub  CalcFinalLoanAmount（）
  On  错误  GoTo  ErrorHandler 
 
  Dim  xlFile  As  Excel.Application 
  Dim  xlsWB1  As  对象 
  Dim  xlsWS1 作为 对象 
  Dim  rs  As  ADODB.Recordset 
 
 设置 rs = 新 ADODB.Recordset 
 
 设置 xlFile = 新 Excel.Application 
 xlFile.Workbooks.Open gstrXlName 
 xlFile.Visible =  False  
 
 设置 xlsWB1 = xlFile.Workbooks.Open（gstrXlName）
 设置 xlsWS1 = xlsWB1.Worksheets（ 员工）
 
 ' 获取最大行数 
  Dim  LastRow 作为 长 
 LastRow& =单元格（Rows.Count， 3 ）。结束（xlUp）.Row 
 LastRow& =范围（  C& Rows.Count）。结束（xlUp）.Row 
 
 MaxRow = LastRow& 
 MaxCol =  9  
 
 
 
 使用 rs.Fields 
 
。附近 名称，adVariant 
 .Append   Y / N，adVariant 
 .Append   Value1，adVariant 
 .Append  < span class =code-string> Value2，adVariant 
 .Append   Value3 ，adVariant ' 新列 
 结束 使用 
 rs.Open 
 
  Dim  r 作为 整数 
 对于 r =  2    MaxRow 
 
  Dim  dblFLA  As   Double  
  If  Cells （r， 7 ）。值=   Y 然后 
 dblFLA = Cells（r， 8 ）。值
 否则 
 如果单元格（r， 7 ).Value =   N 然后 
 dblFLA = Cells（r， 9 ）。值
 结束 如果 
 结束 如果 
 
 使用 rs 
 .AddNew 
！[Name] = Cells（r， 1 ）。值
！[Value1] = Cells（r， 2 ）。值
！[Value2] = Cells（r， 3 ）。值
！ [value3] = dblFLA ' 根据Y / ​​N列添加新列值 
 
。更新
 
 结束 使用 
 下一步 

'问题：如何对Name进行分组并执行Value3的总和？如何对其进行另一次计算

 退出  Sub  
 ErrorHandler：
 MsgBox  错误编号：& Err.Number&  描述：& Err.Description 
 结束  Sub  

先谢谢！

解决方案

我建​​议你阅读这篇文章：如何在Visual Basic或VBA中使用ADO与Excel数据 [ ^ ]



你会找到一种方法，使用T-SQL对Excel数据执行许多计算命令。

Hello,

I have a table "TableA" with column name Name,"Y/N","Value1","Value2". This data is in excel file.
And have multiple entries for each Name.
1.Want to add one column to this table and calculate value on the basis of Y/N column and fill the new column. Meaning if Y/N column has Y then place value1 in value3 column if it is N then put value2 in value 3 column
2.Now want to group name and want to calculate sum of value3
3. on that Sum value want to do some calculation.

Can you please How can we solve this in VBA.
my code is as below

Private Sub CalcFinalLoanAmount()
On Error GoTo ErrorHandler
    
    Dim xlFile As Excel.Application
    Dim xlsWB1 As Object
    Dim xlsWS1 As Object
    Dim rs As ADODB.Recordset
    
    Set rs = New ADODB.Recordset
    
    Set xlFile = New Excel.Application
    xlFile.Workbooks.Open gstrXlName
    xlFile.Visible = False
    
    Set xlsWB1 = xlFile.Workbooks.Open(gstrXlName)
    Set xlsWS1 = xlsWB1.Worksheets("Employees")
    
    'get max row count
    Dim LastRow As Long
    LastRow& = Cells(Rows.Count, 3).End(xlUp).Row
    LastRow& = Range("C" & Rows.Count).End(xlUp).Row
    
    MaxRow = LastRow&
    MaxCol = 9



With rs.Fields

  .Append "Name", adVariant
  .Append "Y/N", adVariant
  .Append "Value1", adVariant
  .Append "Value2", adVariant
  .Append "Value3", adVariant   'new column
End With
rs.Open

Dim r As Integer
For r = 2 To MaxRow

Dim dblFLA As Double
If Cells(r, 7).Value = "Y" Then
    dblFLA = Cells(r, 8).Value
Else
If Cells(r, 7).Value = "N" Then
    dblFLA = Cells(r, 9).Value
End If
End If

With rs
  .AddNew
  ![Name] = Cells(r, 1).Value
  ![Value1] = Cells(r, 2).Value
  ![Value2] = Cells(r, 3).Value
  ![value3] = dblFLA 'add new column value on basis of Y/N column

  .Update

End With
Next

'Question: How to group Name and do Sum of Value3? How to perform another calculation on it

Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & "Description: " & Err.Description
End Sub

Thanks in Advance!

解决方案

I would suggest you to read this article: How To Use ADO with Excel Data from Visual Basic or VBA[^]

There you'll find a way to perform many "calculations" on Excel data using T-SQL commands.

这篇关于如何在vb6中对现有记录集运行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！