我們開發(fā)軟件過程中,經(jīng)常需要將窗體或子窗體的數(shù)據(jù)導出到Excel,以便進行數(shù)據(jù)分析、統(tǒng)計或生成圖表,所以使用代碼自動完成這些操作,就可以得大大提高效率,下面就這個問題,歸納總結一下,希望對大家有所幫助:
一、在Access中使用代碼打開Excel:
ublic MyXL As Object
'打開Excel
Sub GetExcel()
Const ERR_APP_NOTRUNNING As Long = 429
On Error Resume Next
Set MyXL = GetObject(, "Excel.Application")
If Err = ERR_APP_NOTRUNNING Then
Set MyXL = New Excel.Application
End If
MyXL.Application.Visible = True
End Sub
使用這段代碼,可以打開一個Excel實例或者引用已經(jīng)打開的Excel實例
二、新建一個工作簿
Sub CreateNewBook
MyXL.Application.Workbooks.Add
End Sub
三、使用代碼將窗體上的數(shù)據(jù)復制到Windows粘貼板
Sub CopyToClip
Forms(FormName).Controls(SubFormName).SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
End Sub
四、使用代碼將Windows粘貼板的內容粘貼到Excel
Sub CopyToExcel()
GetExcel
MyXL.Application.Workbooks.Add
MyXL.Application.ActiveSheet.Paste
End Sub
五、對導出到Excel中的數(shù)據(jù)進行格式化,比如,加上報表標題、設置表格線等。
Sub FormatTAB()
SetLine '設置表格線的子程序,在Access中實現(xiàn)對Excel文檔格式化
'插入兩行作為標題行
MyXL.Application.ActiveSheet.Rows("1:1").Select
For j = 1 To 2
MyXL.Application.Selection.Insert Shift:=xlDown
Next j
MyXL.Application.ActiveSheet.Range("A1") = “標題文字”
'設置表標題字體
MyXL.Worksheets(1).Range("A1").Select
With MyXL.Application.Selection.Font
.Name = "宋體"
.Size = 16
End With
End Sub
'設置表格線
Sub SetLine()
On Error Resume Next
MyXL.Application.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
MyXL.Application.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
MyXL.Application.Selection.WrapText = False
With MyXL.Application.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With MyXL.Application.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With MyXL.Application.Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With MyXL.Application.Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With MyXL.Application.Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With MyXL.Application.Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
End Sub
六、關閉打開的工作簿
'關閉Excel
Sub CloseExcel()
On Error Resume Next
MyXL.Application.DisplayAlerts = False
MyXL.Application.Save
MyXL.Application.Quit
Set MyXL = Nothing '釋放對該應用程序
End Sub
經(jīng)過上面的講述,一個完整的導出操作就完成了。
如果大家想看看實例程序實現(xiàn)的效果,可以在我的網(wǎng)站上下載客戶管理軟件,試用一下
下載地址:[url=http://ctxi.cn/vvb/www.szyyt.com]www.szyyt.com[/url]
作者QQ:747767 253675026
MSN:guotx@hotmail.com
作者:guotianxin郭天新
在Access中用代碼執(zhí)行Excel文件中宏,實現(xiàn)對文件的格式化操作實例:
[此貼子已經(jīng)被作者于2004-11-12 23:35:01編輯過] |