领料单打印

一指禅62

问题来源 https://club.excelhome.net/forum ... amp;_dsign=68639f0d




批量打印.zip (56.58 KB, 下载次数: 27)

2024-4-18 22:23 上传

点击文件名下载附件

一指禅62

1. Option Explicit
   
2. Dim Rst As New ADODB.Recordset
   
3. Dim i%, j%
   
4. 
   
5. Private Sub UserForm_Initialize()
   
6.     Set Rst = cnn.Execute("Select Distinct 单号 From [列表$A:N]")
   
7.     Do While Not Rst.EOF
   
8.         ListBox1.AddItem Rst.Fields("单号")
   
9.         Rst.MoveNext
   
10.     Loop
    
11.     Set Rst = Nothing
    
12.     Sheets("打印页").Select
    
13. End Sub
    
14. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
15.     Set Rst = Nothing
    
16. End Sub
    
17. 
    
18. Private Function cnn() As Object
    
19.     Set cnn = CreateObject("adodb.connection")
    
20.     If Application.Version < 12 Then
    
21.         cnn.Provider = "Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0"
    
22.     Else
    
23.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0"
    
24.     End If
    
25.     cnn.Open ThisWorkbook.FullName
    
26. End Function
    
27. 
    
28. Private Sub cmd打印_Click()
    
29.     For i = 1 To SpinButton1.Max
    
30.         SpinButton1.Value = i
    
31.         Call PrintOutView(i)
    
32.         'Range("A1:G17").PrintOut   '打印指定区域
    
33.     Next
    
34. End Sub
    
35. 
    
36. Private Sub cmd全部打印_Click()
    
37.     For j = 0 To ListBox1.ListCount - 1
    
38.         DoEvents
    
39.         ListBox1.Selected(j) = True
    
40.         Call cmd打印_Click
    
41.     Next
    
42. End Sub
    
43. 
    
44. Private Sub ListBox1_Click()
    
45.     If Rst.State <> 0 Then Rst.Close
    
46.     Set Rst = CreateObject("adodb.Recordset")
    
47.     Rst.Open "Select * FROM [列表$A:N] Where str(单号)='" & Str(ListBox1.Text) & "'", cnn, 1, 3
    
48.     Rst.PageSize = 9  '每页条数
    
49.     SpinButton1.Max = Rst.PageCount
    
50.     SpinButton1.Value = 1
    
51.     Call SpinButton1_Change
    
52. End Sub
    
53. 
    
54. Private Sub SpinButton1_Change()
    
55.     Rst.AbsolutePage = SpinButton1.Value
    
56.     Label1.Caption = "该单有" & Rst.RecordCount & "条记录，分" & Rst.PageCount & "页打印。第" & SpinButton1.Value & "页。"
    
57.     Call PrintOutView(SpinButton1.Value)
    
58. End Sub
    
59. 
    
60. 
    
61. Private Sub PrintOutView(Optional ByVal Intex As Integer = 1)
    
62.     Dim Temp(1 To 10, 1 To 7), n%
    
63.     Rst.MoveLast
    
64.     Rst.AbsolutePage = Intex
    
65.     n = 0
    
66.     Range("B3") = Rst.Fields("日期")
    
67.     Range("B4") = Rst.Fields("部门")
    
68.     Range("F3") = Rst.Fields("单号")
    
69.     Range("F4") = Rst.Fields("出库类别")
    
70.     Range("B17,D17,G17") = ""
    
71. 
    
72.     If Not IsNull(Rst.Fields("制单人")) Then Range("B17") = Rst.Fields("制单人")
    
73.     If Not IsNull(Rst.Fields("审核人")) Then Range("D17") = Rst.Fields("审核人")
    
74.     If Not IsNull(Rst.Fields("领料人")) Then Range("G17") = Rst.Fields("领料人")
    
75.    
    
76.     Do While Not Rst.EOF And n < 9
    
77.         n = n + 1
    
78.         Temp(n, 1) = Rst.Fields("存货编码")
    
79.         Temp(n, 2) = Rst.Fields("存货名称")
    
80.         Temp(n, 3) = Rst.Fields("规格型号")
    
81.         Temp(n, 4) = Rst.Fields("单位")
    
82.         Temp(n, 5) = Rst.Fields("数量")
    
83.         Temp(n, 6) = Rst.Fields("仓库")
    
84.         Temp(n, 7) = Rst.Fields("生产订单号")
    
85.         Rst.MoveNext
    
86.     Loop
    
87.     Range("A6:G15") = Temp
    
88. End Sub
    
89. 
    

复制代码