用Excel、Word对象来写---- 部分窗体及源程序代码如下: ---- 1.程序主模块 ---- 定义Excel、Access对象变量,显示系统启动画面,进入系统主程序界面。强调一下,在编写程序之前须加入对Excel及Access库函数的引用,具体操作是:选择菜单栏'工程'\'引用…',将'可使用的引用'列表框内'Microsoft Access 8.0 Object Library'和 'Microsoft Excel 8.0 Object Library'两项前的复选框标为选中,按"确定"返回。 mdsMain.bas
'定义数据库记录集及Excel对象变量
Public ex As New Excel.Application
Public exwbook As Excel.Workbook
Public exsheet As Excel.Worksheet
Public mydatabase As Database
Public myrecordset1 As Recordset
[定义记录集]
……
……
Public Opt As Integer '报表选项
[Opt为frmSelreport.frm返回值]
Public isYN As Boolean
Sub Main()
Load frmSplash
frmSplash.Show
frmSplash.Label2.Caption =
" 系统正在加载Access数据库..."
Set mydatabase = OpenDatabase("c:\sbda\sbda.mdb")
Set myrecordset1 = mydatabase.OpenRecordset
("报表打印(一)")
[此处对记录集赋值]
……
……
frmSplash.Label2.Caption =
" 系统正在加载Excel电子表格..."
Set ex = CreateObject("excel.application")
Set exwbook = ex.Workbooks.Open("c:\sbda\sbda.xls")
Load FrmInput '将数据输入窗体加载到内存中
Unload frmSplash
Load FrmMain '将主程序界面加载到内存中
End Sub---- 2.报表打印模块
---- 其中ExcelDoForVB1()是一子程序,由prnProess()调用,作用是从Access中提取所需数据资料,填入Excel对应工作表(Worksheet)的相应单元格(Cells)中,然后打印已填入数据的表格;prnProess()则负责实现对VB通用对话框(Commom Dialog)中打印功能的控制。 mdlPrint.bas
Option Explicit
'定义循环计数变量
Public nRow As Integer, nCol As
Integer, nBtoE As Integer
'定义变量接收打印对话框返回值
Public BeginPage, EndPage, NumCopies
'程序运行时需进行判断的各种标志
Public nflag, Flag, ifNum
'数据记录集中指针移动数
Public PageN As Integer, n As Integer
'bar1为进度条
Public bar1 As ObjectSub prnProess()
'控制通用对话框打印功能
Set bar1 = FrmPrint.PgsBar1 '进度条
On Error GoTo errhandle:
If Flag = 0 Then '当打印对话框中选"全部"时
Select Case Opt '选择需要打印的表格
Case 1
nflag = 1
myrecordset1.MoveFirst
myrecordset1.MovePrevious
PageN = 1
Do While nflag = 1
Call ExcelDoForVB1
'数据填入Excel单元格打印
PageN = PageN + 1
Loop
Case 2
……
……
End Select
Else
If Flag = 2 Then '
当打印对话框中选"页"时
If EndPage - BeginPage = 0 Then
ifNum = 0
Else
If EndPage - BeginPage > 0 Then
ifNum = 1
Else
ifNum = 2
End If
End If
Select Case ifNum
Case 2
Exit Sub
Case 0
Select Case Opt '
选择需要打印的表格
Case 1
myrecordset1.MoveFirst
n = (BeginPage - 1) *
(49 - 4 + 1) - 1
myrecordset1.Move n
PageN = BeginPage
Call ExcelDoForVB1
'数据填入Excel单元格并打印
Case 2
……
……
End Select
Case 1
Select Case Opt
'选择需要打印的表格
Case 1
myrecordset1.MoveFirst
n = (BeginPage - 1) *
(49 - 4 + 1) - 1
myrecordset1.Move n
PageN = BeginPage
For nBtoE = BeginPage To EndPage
Call ExcelDoForVB1
'填入Excel单元格并打印
PageN = PageN + 1
Next nBtoE
Case 2
……
……
End Select
End Select
End If
End If
FrmMain.Visible = True
Exit Sub
errhandle:
FrmPrint.Visible = False
FrmMain.Visible = True
End Sub---- 注意,下段仅通过ExcelDoForVB1()对"报表(一)"的处理,来说明数据填入Excel并打印的整个过程。 Sub ExcelDoForVB1() '打印报表(一)
FrmPrint.Visible = True
Set exsheet = exwbook.Worksheets("sheet1")
ex.Sheets("Sheet1").Select
ex.Range("A4:U49").Select
ex.Selection.ClearContents
ex.Range("A4").Select
bar1.Min = 0
bar1.Max = 45
For nRow = 4 To 49
bar1.Value = nRow - 4 '进度显示栏进程
myrecordset1.MoveNext
If myrecordset1.EOF Then
nflag = 0
Exit For
End If
For nCol = 1 To 21
exsheet.Cells(nRow, nCol) =
myrecordset1.Fields(nCol - 1)
Next nCol
Next nRow
exsheet.Cells(52, 21) = "第 " + CStr(PageN) + " 页"
FrmPrint.Visible = False
bar1.Value = 0
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies
End Sub
'定义数据库记录集及Excel对象变量
Public ex As New Excel.Application
Public exwbook As Excel.Workbook
Public exsheet As Excel.Worksheet
Public mydatabase As Database
Public myrecordset1 As Recordset
[定义记录集]
……
……
Public Opt As Integer '报表选项
[Opt为frmSelreport.frm返回值]
Public isYN As Boolean
Sub Main()
Load frmSplash
frmSplash.Show
frmSplash.Label2.Caption =
" 系统正在加载Access数据库..."
Set mydatabase = OpenDatabase("c:\sbda\sbda.mdb")
Set myrecordset1 = mydatabase.OpenRecordset
("报表打印(一)")
[此处对记录集赋值]
……
……
frmSplash.Label2.Caption =
" 系统正在加载Excel电子表格..."
Set ex = CreateObject("excel.application")
Set exwbook = ex.Workbooks.Open("c:\sbda\sbda.xls")
Load FrmInput '将数据输入窗体加载到内存中
Unload frmSplash
Load FrmMain '将主程序界面加载到内存中
End Sub---- 2.报表打印模块
---- 其中ExcelDoForVB1()是一子程序,由prnProess()调用,作用是从Access中提取所需数据资料,填入Excel对应工作表(Worksheet)的相应单元格(Cells)中,然后打印已填入数据的表格;prnProess()则负责实现对VB通用对话框(Commom Dialog)中打印功能的控制。 mdlPrint.bas
Option Explicit
'定义循环计数变量
Public nRow As Integer, nCol As
Integer, nBtoE As Integer
'定义变量接收打印对话框返回值
Public BeginPage, EndPage, NumCopies
'程序运行时需进行判断的各种标志
Public nflag, Flag, ifNum
'数据记录集中指针移动数
Public PageN As Integer, n As Integer
'bar1为进度条
Public bar1 As ObjectSub prnProess()
'控制通用对话框打印功能
Set bar1 = FrmPrint.PgsBar1 '进度条
On Error GoTo errhandle:
If Flag = 0 Then '当打印对话框中选"全部"时
Select Case Opt '选择需要打印的表格
Case 1
nflag = 1
myrecordset1.MoveFirst
myrecordset1.MovePrevious
PageN = 1
Do While nflag = 1
Call ExcelDoForVB1
'数据填入Excel单元格打印
PageN = PageN + 1
Loop
Case 2
……
……
End Select
Else
If Flag = 2 Then '
当打印对话框中选"页"时
If EndPage - BeginPage = 0 Then
ifNum = 0
Else
If EndPage - BeginPage > 0 Then
ifNum = 1
Else
ifNum = 2
End If
End If
Select Case ifNum
Case 2
Exit Sub
Case 0
Select Case Opt '
选择需要打印的表格
Case 1
myrecordset1.MoveFirst
n = (BeginPage - 1) *
(49 - 4 + 1) - 1
myrecordset1.Move n
PageN = BeginPage
Call ExcelDoForVB1
'数据填入Excel单元格并打印
Case 2
……
……
End Select
Case 1
Select Case Opt
'选择需要打印的表格
Case 1
myrecordset1.MoveFirst
n = (BeginPage - 1) *
(49 - 4 + 1) - 1
myrecordset1.Move n
PageN = BeginPage
For nBtoE = BeginPage To EndPage
Call ExcelDoForVB1
'填入Excel单元格并打印
PageN = PageN + 1
Next nBtoE
Case 2
……
……
End Select
End Select
End If
End If
FrmMain.Visible = True
Exit Sub
errhandle:
FrmPrint.Visible = False
FrmMain.Visible = True
End Sub---- 注意,下段仅通过ExcelDoForVB1()对"报表(一)"的处理,来说明数据填入Excel并打印的整个过程。 Sub ExcelDoForVB1() '打印报表(一)
FrmPrint.Visible = True
Set exsheet = exwbook.Worksheets("sheet1")
ex.Sheets("Sheet1").Select
ex.Range("A4:U49").Select
ex.Selection.ClearContents
ex.Range("A4").Select
bar1.Min = 0
bar1.Max = 45
For nRow = 4 To 49
bar1.Value = nRow - 4 '进度显示栏进程
myrecordset1.MoveNext
If myrecordset1.EOF Then
nflag = 0
Exit For
End If
For nCol = 1 To 21
exsheet.Cells(nRow, nCol) =
myrecordset1.Fields(nCol - 1)
Next nCol
Next nRow
exsheet.Cells(52, 21) = "第 " + CStr(PageN) + " 页"
FrmPrint.Visible = False
bar1.Value = 0
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies
End Sub
解决方案 »
- [求助]VB6下,InStr函数的用法求教。
- 3001,参数错误,这是什么错误啊?
- 谁可以帮我写这个程序
- 请教三个问题
- 求助,在打开一个数据库文件时出错
- 急救!!各位高手怎么我找不到spread sheet控件来添加到工具栏里!
- Declare Function WinExec Lib "kernel32" (ByVal lpCmdLine As String, ByVal nCmdShow As Long) As Long为什么提示编译错误,是不是要
- 组合查询问题
- 300分三个问题:如何将图标保存到数据库一个字段内?
- VBScript是个什么东东??
- 有没人用过webbrowser控件?想请教一下
- 怎样使记录集按序排列
的作用是什么?
程序主模块可不可以写的更详细一点
我只需要把表goumai中的内容输出成.xls和.doc形式的就可以了,不需要打印出来。
Dim adoRS As ADODB.Recordset
Dim strSQL As StringSet cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cn.ConnectionString = "C:\yonghu.mdb"
cn.OpenSet adoRS = New ADODB.Recordset
strSQL = "SELECT * INTO [Excel 8.0;DATABASE=" & App.Path & "\MyExcel.xls].[WorkSheet1] FROM [goumai];"adoRS.CursorType = adOpenStatic
adoRS.ActiveConnection = cn
adoRS.Source = strSQL
adoRS.Open用cells方法也可以把adors表中的数据一个个填入Excel单元格,但速度太慢,不推荐。转换到word不常见,上面的方法也不适用,可以考虑用宏操作。
'定义循环计数变量
Public nRow As Integer, nCol As
Integer, nBtoE As Integer
'定义变量接收打印对话框返回值
Public BeginPage, EndPage, NumCopies
'程序运行时需进行判断的各种标志
Public nflag, Flag, ifNum
'数据记录集中指针移动数
Public PageN As Integer, n As Integer
'bar1为进度条