VBA 打印報表

最新推荐文章于 2024-03-12 13:49:40 发布

最新推荐文章于 2024-03-12 13:49:40 发布 · 142 阅读

文章标签：

#VBA #VB #Excel #SQL Server #SQL

本文介绍如何使用VBA连接SQL Server数据库，并通过Excel进行数据查询与报表打印。主要内容包括：VBA简介、宏录制技巧、ADODB连接对象使用、SQL查询执行等。

因公司有大量的數據需要打印報表,老大讓我思考下可否通過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