printdanzi.bas

一个简单进销存系统

Attribute VB_Name = "printDanZi"
Public Function printIncome(lPZnum As Long) As Boolean
    Dim mobjExcel As Excel.Application
    Dim mobjworkbook As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    Dim rsSale As New ADODB.Recordset
    Dim strDestination, strSource As String
    Dim iLine As Integer
    Dim iCol As Integer
    Dim curTemp As Currency, strSQl As String, curSum As Currency
    strSource = App.Path & "\income.xls"
    strDestination = strSource
    Set mobjExcel = New Excel.Application
    curSum = 0
    Set mobjworkbook = mobjExcel.Workbooks.Open(strDestination)
    Set xlsheet = mobjworkbook.Worksheets(1)
    strSQl = "select * from sale  where 编号 = " & CStr(lPZnum) & " order by 序号"
    rsSale.CursorLocation = adUseClient
    rsSale.Open strSQl, pubConn, adOpenDynamic, adLockOptimistic
    If rsSale.RecordCount = 0 Then
        MsgBox "该编号没有记录"
        Exit Function
    End If
    rsSale.MoveFirst
    '填充凭证数据
    mobjExcel.ActiveSheet.Cells(2, 6).Value = rsSale!编号
    mobjExcel.ActiveSheet.Cells(4, 6).Value = rsSale!日期

    Dim strSendMan As String
'    If IsNull(rsSale!客户名称) Then
'        strSendMan = ""
'    Else
'        strSendMan = rsSale!客户名称
'    End If
'    mobjExcel.ActiveSheet.Cells(4, 1).Value = "客户名称：" & strSendMan
'
'    If Not IsNull(rsSale!明细户) Then
'        mobjExcel.ActiveSheet.Cells(4, 4).Value = "明细户：" & rsSale!明细户
'    End If
    iLine = 6
    While Not rsSale.EOF
        xlsheet.Rows(iLine).Insert
  '      mobjExcel.ActiveSheet.Cells(iLine, 1).Value = iLine - 6 '不再打印序号
        mobjExcel.ActiveSheet.Cells(iLine, 1).Value = rsSale!批号
        mobjExcel.ActiveSheet.Cells(iLine, 2).Value = rsSale!颜色
        mobjExcel.ActiveSheet.Cells(iLine, 3).Value = rsSale!支数
        mobjExcel.ActiveSheet.Cells(iLine, 4).Value = rsSale!重量
        mobjExcel.ActiveSheet.Cells(iLine, 5).Value = rsSale!单价
        curTemp = rsSale!单价 * rsSale!重量
        mobjExcel.ActiveSheet.Cells(iLine, 6).Value = curTemp
        curSum = curSum + curTemp
        iLine = iLine + 1
        rsSale.MoveNext
    Wend
    mobjExcel.ActiveSheet.Cells(iLine, 6).Value = curSum
     mobjExcel.ActiveSheet.Cells(iLine + 1, 2).Value = Up(CStr(curSum))
     '打印预览
     mobjExcel.Visible = True
     xlsheet.PrintPreview
    mobjExcel.DisplayAlerts = False
    mobjExcel.Quit
    Set mobjExcel = Nothing
End Function