VBA 打印報表

来源：互联网发布：大数据交换系统编辑：程序博客网时间：2024/05/18 02:15

因公司有大量的數據需要打印報表,老大讓我思考下可否通過VBA來進行打印,因在大學期間學習過vb課程,對于vba接觸起來還是比較快的,只是其中的一些細節和宏定義瞭解不深.現在把學習的心得總結一下.

vba的全文名稱是visual basic for application . 它是visual basic(vb) 的應用程序版本.但是它和vb用很大的不同,vb是運行在獨立的平臺上,而vba是借助在Excel的運行環境中,通過它可以讓Excel的操作變得越來越簡單.要全面掌握vba是一件很讓人糾結的事情,它擁用龐大的宏,但是實際上我們用到的比較少.如果你對于宏瞭解不深最好的方法是錄製宏. 它在Excel工具欄中---工具----宏---錄製宏.說了這麼多貼代碼了!

'
'windowxp excel2007;
' SQL Server 2008
'@Autor MichaelYang
'
'
Public Function WorkBook_Open()
    Dim Conn As New ADODB.Connection '定義ADODB 連接對象
    Dim ConnStr As String ' 定義連接字符串
    '注意如果是通過IP連接SQL server2008的話需要開啟 SQL Server Browser服務
    ConnStr = "Provider=SQLOLEDB.1;DATA SOURCE=10.1.xxx.xxxx;Password=test;User ID=test;Initial Catalog =vbaTest;"


    Dim Records As New ADODB.Recordset
    Dim Sheet As Worksheet

    Set Sheet = ThisWorkbook.Worksheets(2)

    If Sheet.Name = "" Then
       Sheet.Name = "use_table"
    Else
       MsgBox "已經命名好了"
    End If

    Sheet.Cells.Clear '刪除原有的所有信息

    If Conn.State = 1 Then
        MsgBox "已經連接"
    Else
        Conn.Open ConnStr
     End If

     Dim SQLStr As String
     SQLStr = "select * from use_table"

     Records.Open SQLStr, Conn, adOpenStatic, adLockBatchOptimistic

     Dim i, j, TotalRows, TotalColumns As Integer

     j = 0

     TotalRows = Records.RecordCount
     TotalColunms = Records.Fields.Count

' Sheet.ClearArrows

' 用於插入列名
'     For i = 0 To TotalColunms - 1
'        Sheet.Cells(1, i + 1) = Records.Fields(i).Name
'     Next

     Do While Not Records.EOF '記錄集的
     For i = 0 To TotalColunms - 1
        Sheet.Cells(j + 3, i + 1) = Records.Fields(i).Value '從第三行開始輸入數據
     Next

     Records.MoveNext
     j = j + 1

     Loop

     Records.Close
     Conn.Close

     Set Records = Nothing
     Set Conn = Nothing

End Function

錄製宏test

Sub test()
'
' test Macro
' michaelyang  2011/3/28 魁籹エ栋
'

'
    Range("A1:C1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = "用戶密碼管理"
    With ActiveCell.Characters(Start:=1, Length:=6).Font
        .Name = "新細明體"
        .FontStyle = "粗體"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Columns("C:C").Select
    Range("C2").Activate
    Selection.ColumnWidth = 8.75
    Range("A1:C1").Select
    Columns("C:C").ColumnWidth = 27
    Columns("C:C").ColumnWidth = 8.38
    Columns("A:C").Select
    Range("A2").Activate
    Selection.ColumnWidth = 26.5
    Range("A1:C1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "主鍵"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "用戶名"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "密碼"
    Range("A2:C2").Select
    Range("C2").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .ColorIndex = 47
        .Pattern = xlSolid
    End With
    Range("A3").Select

    WorkBook_Open

End Sub