Excel VBA报告大厦 [英] Excel VBA report building

问题描述

我一直在MS Excel中创建一个动态报告。我正在处理一个传统的VB6应用程序，我遇到了一些问题，希望能帮助我解决问题。我在下面做的是将数据抓到我的记录集g_RS3中 - 通常这里有3到20个项目，我使用g_RS3在每个标题下的值（标题和2列值）中输入我的excel电子表格。我正在努力进行编辑，但我一直在努力。这是我的代码....

  Do While not g_RS3.EOF 
 With xlSheet.Cells（xlRow，xlCol ）
 .Value = g_RS3（Label）
 .Offset（1，0）.Value =客户端
 .Offset（1，1）.Value =Buyers
 With .Offset（1，0）
 .Font.Bold = True 
 .Borders.Weight = xlThin 
 End with 
 With .Offset（1，1）
 .Font.Bold = True 
 .Borders.Weight = xlThin 
结束
带.Resize（1，2）
 .Font.Bold = True 
 .WrapText = True 
 .VerticalAlignment = xlCenter 
 .Merge 
 .Horizo​​ntalAlignment = xlCenter 
 .Borders.Weight = xlThin 
结束
结束
 xlCol = xlCol + 2 
 g_RS3.MoveNext 
循环
  

我是附上一个imag这将显示它的外观。在录音结束时，我试图添加另一个标题，只是说TOTAL，下面有2列。但是我很难做到这一点。

解决方案

这是一个从主代码中提取独立功能是有意义的情况：头块格式可以进入一个单独的Sub，所以你可以从记录集循环或一个单一的一组标题

主代码然后变成

 '从记录集
尽管没有g_RS3.EOF 
 DoBlock xlsheet.Cells（xlRow，xlCol），g_RS3（Label），客户，买家
 g_RS3.MoveNext 
 xlCol = xlCol + 2 
循环
'额外的头
 DoBlock xlsheet.Cells（xlRow，xlCol），总计，客户，买家
  

提取代码：
编辑 - 整理

 code> Sub DoBlock（rng As Range，h1，h2，h3）
用rng 
 .Value = h1 
 .WrapText = True 
 .VerticalAlignment = xlCenter 
 .Horizo​​ntalAlignment = xlCenter 
 
 .Offset（1，0）.Value = h2 
 .Offset（1，1）.Value = h3 
 
 With .Resize（2，2）
 .Font.Bold = True 
 .Borders.Weight = xlThin 
结束
 .Resize（1，2）.Merge 
结束
 End Sub 
  

I've been working on creating a dynamic report in MS Excel. I'm working on a legacy VB6 application and I've come across a few issue that I hope ya'll can help me resolve. What I'm doing below, is grabbing data into my recordset g_RS3 - typically this has anywhere from 3 to 20 items, and I use g_RS3 to enter values (headings, and 2 column values under each heading: clients, buyers) into my excel spreadsheet. I'm trying to make an edit to it but I've been struggling with it. This is my code....

Do While Not g_RS3.EOF
    With xlSheet.Cells(xlRow, xlCol)
        .Value = g_RS3("Label")
            .Offset(1, 0).Value = "Clients"
            .Offset(1, 1).Value = "Buyers"
                With .Offset(1, 0)
                    .Font.Bold = True
                .Borders.Weight = xlThin
            End With
            With .Offset(1, 1)
                .Font.Bold = True
                .Borders.Weight = xlThin
            End With
            With .Resize(1, 2)
                .Font.Bold = True
                .WrapText = True
                .VerticalAlignment = xlCenter
                .Merge
                .HorizontalAlignment = xlCenter
                .Borders.Weight = xlThin
            End With
    End With
    xlCol = xlCol + 2
    g_RS3.MoveNext
Loop

I am attaching an image that will show what it looks like. At the end of the recordset I'm trying to add another heading that just says TOTAL and has the 2 columns below it. But I'm having a difficult time doing that.

解决方案

This is a case where it makes sense to extract a stand-alone piece of functionality from your main code: the header block formatting can go into a separate Sub, so you can call it either from within the recordset loop or for a single set of headings

Main code then becomes

'headers from recordset
Do While Not g_RS3.EOF
    DoBlock xlsheet.Cells(xlRow, xlCol), g_RS3("Label"), "Clients", "Buyers"
    g_RS3.MoveNext
    xlCol = xlCol + 2
Loop
'Extra header
DoBlock xlsheet.Cells(xlRow, xlCol), "Total", "Clients", "Buyers"

Extracted code: EDIT - tidied up

Sub DoBlock(rng As Range, h1, h2, h3)
    With rng
        .Value = h1
        .WrapText = True
        .VerticalAlignment = xlCenter
        .HorizontalAlignment = xlCenter

        .Offset(1, 0).Value = h2
        .Offset(1, 1).Value = h3

        With .Resize(2, 2)
            .Font.Bold = True
            .Borders.Weight = xlThin
        End With
        .Resize(1, 2).Merge
    End With
End Sub

这篇关于Excel VBA报告大厦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！