|
有如下代码,麻烦帮忙补充下,有注释的地方是不知道怎么写的,谢谢各位了!- Public Sub ceshi2(ByVal data_h, ByVal scene_type, ByVal scene_name)
- Worksheets("测试数据").Range("A3:J10000").ClearContents
- Set conn = CreateObject("ADODB.Connection")
- Set rsOra = CreateObject("ADODB.Recordset")
- conn.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
- Sql = "select 场景类型,'" & scene_name & "'," & data_h & ",指标ID, 指标名称,指标类型,'','',指标来源表1,指标来源表2,指标来源表3,指标来源表4,指标算法 from [字典数据$a:m] where 场景类型='" & scene_type & "' "
- Sheets("测试数据").[A3].CopyFromRecordset conn.Execute(Sql)
- conn.Close: Set conn = Nothing
- j=3
- source_table1 = Worksheets("测试数据").Cells(j, 9)
- source_table2 = Worksheets("测试数据").Cells(j, 10)
- source_table3 = Worksheets("测试数据").Cells(j, 11)
- source_table4 = Worksheets("测试数据").Cells(j, 12)
- algorithm = Worksheets("测试数据").Cells(j, 13)
-
- If source_table1 = """" Then
-
- '在Worksheets("测试数据").Cells(j, 7) 填入 此指标无算法
-
- ElseIf source_table2 = """" Then
- sqlcou = "…………"
-
- ElseIf source_table3 = """" Then
- sqlcou = "……………… "
-
- ElseIf source_table4 = """" Then
- sqlcou = "……………… "
- Else
- sqlcou = "……………… "
- 'rsOra2.Open sqlcou 执行上面的sql
- 'value_s = rsOra的结果 ,此结果是多个字段,多行数据,写到cells(j,7)这一个单元格里
- 'Worksheets("测试数据").Cells(j, 7) = value_s
- End If
- 'j从第三行开始,一直到最后一行
-
- End Sub
复制代码
你这样代码没法想你的目的,只有猜 - Public Sub ceshi2(ByVal data_h, ByVal scene_type, ByVal scene_name)
- Worksheets("测试数据").Range("A3:J10000").ClearContents
- Set conn = CreateObject("ADODB.Connection")
- Set rsOra = CreateObject("ADODB.Recordset")
- conn.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
- Sql = "select 场景类型,'" & scene_name & "'," & data_h & ",指标ID, 指标名称,指标类型,'','',指标来源表1,指标来源表2,指标来源表3,指标来源表4,指标算法 from [字典数据$a:m] where 场景类型='" & scene_type & "' "
- Sheets("测试数据").[A3].CopyFromRecordset conn.Execute(Sql)
- conn.Close: Set conn = Nothing
- '上面关闭了连接,下面是否有地方重新打开连接了?
- j = 3
- 'j从第三行开始,一直到最后一行
- 'A列最后的数据所在行
- Do While j <= Cells(Rows.Count, 1).End(xlUp).Row
- source_table1 = Worksheets("测试数据").Cells(j, 9)
- source_table2 = Worksheets("测试数据").Cells(j, 10)
- source_table3 = Worksheets("测试数据").Cells(j, 11)
- source_table4 = Worksheets("测试数据").Cells(j, 12)
- algorithm = Worksheets("测试数据").Cells(j, 13)
- If source_table1 = """" Then
- '在Worksheets("测试数据").Cells(j, 7) 填入 此指标无算法
- ElseIf source_table2 = """" Then
- sqlcou = "…………"
- ElseIf source_table3 = """" Then
- sqlcou = "……………… "
- ElseIf source_table4 = """" Then
- sqlcou = "……………… "
- Else
- sqlcou = "……………… "
- rsora2.Open sqlcou, conn '执行上面的sql
- Worksheets("测试数据").Cells(j, 7).CopyFromRecordset rsora2
- 'value_s = rsOra的结果 ,此结果是多个字段,多行数据,写到cells(j,7)这一个单元格里
- 'Worksheets("测试数据").Cells(j, 7) = value_s
- End If
- j = j + 1
- Loop
- End Sub
复制代码
|
|