在机房收费系统中,用到了vb与office的交互,将数据从vb中导出到excel。
我采用的是在模块中定义,再在窗体中调用。这样减少了代码量,同时使得系统比较稳定。
首先,引用office对象库:在工程引用中,使用Microsoft Excel 14.0 Object Library 和 Microsoft ActiveX Data Objects 2.6 Libray
然后,创建,使用Application对象
最后,关闭 Application对象
'MSHFlexGrid控件导出到Excel
Public Sub OutDataToExcel(Flex As MSFlexGrid) '导出至Excel
Dim s As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
On Error GoTo Ert
Dim Excelapp As Excel.Application '声明,使用Excel
Set Excelapp = New Excel.Application
On Error Resume Next
DoEvents
Excelapp.SheetsInNewWorkbook = 1
Excelapp.Workbooks.Add ‘增加Excel工作表
Excelapp.ActiveSheet.Cells(1, 3) = s
Excelapp.Range("C1").Select
Excelapp.Selection.Font.FontStyle = "Bold"
Excelapp.Selection.Font.Size = 16
With Flex ‘将数据导出到excel表格
k = .Rows
For i = 0 To k - 1
For j = 0 To .Cols - 1
DoEvents
Excelapp.ActiveSheet.Cells(3 + i, j + 1) = "'" & .TextMatrix(i, j)
Next j
Next i
End With
Excelapp.Visible = True ’设置可见
'Excelapp.Sheets.PrintPreview ‘设置打印预览
Ert:
If Not (Excelapp Is Nothing) Then
'Excelapp.Quit ’释放控制权
End If
End Sub