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
.HorizontalAlignment = 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
.HorizontalAlignment = 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屋!