1.难点
1.1 vba 对zebra打印机调用方法open
1.2 zebra打印机默认支持UTF8 编码方式,对应编码命令为CI28; 支持GB2312 GB18030 ASCII码编码方式,对应编码命令为CI26
1.3 VBA对中文只支持GB2312, 而excel 是默认支持UTF8的, excel 与VBA编码环境并不一样。
1.4 斑马打印机(新机默认环境)是UTF8的,需要指定字体(SIMSUN.TFF)和编码方式(CI26 支持GB18030,兼容GB2312)
2.代码样例
Sub 打印()
Dim file As String
'Dim i, NumLabels As Integer
file = ""
' MyPrinter = "\\10.165.98.33\IP_Print_ZDesigner ZT410-203dpi ZPL(test)"
'NumLabels = 1
Call optxt(file)
'printcmd = "copy " & file & ""
'printcmd = printcmd & " \\10.165.98.33\IP_Print_ZDesigner ZT410-203dpi ZPL(test)"
'Open MyPrinter For Output As #2
' Print #2, "^PQ" & NumLabels
' Print #2, file
' Close #2
'Shell ("copy " & file & """ \\10.165.98.33\IP_Print_ZDesigner ZT410-203dpi ZPL(test)""")
'Debug.Print printcmd
' Shell printcmd
' Shell"C:\windows\system32NOTEPAD.EXE /p " & file
End Sub
Function optxt(file)
Dim FSN, sheetname, nowDate, SN, PN, SEQ, ItemDesc1, ItemDesc2, PIK, CDT, MyPrinter As String
Dim i, NumLabels As Integer
sheetname = ActiveWindow.ActiveSheet.name '当前光标sheetname
FSN = Sheets(sheetname).Range("B16") & Format(Now, "YYYY") & Format(Now, "MM") & Format(Now, "DD") & Format(Now, "hhmmss") '输出历史文件用的文件名变量
SN = Sheets(sheetname).Range("B16") & Format(Now, "YYYY") & Format(Now, "MM") & Format(Now, "DD") & Format(Now, "hhmmss") '输出产品流水号(唯一),给二维码生产使用
PN = Sheets(sheetname).Range("B16") '模板上面产品型号,因为标签纸装不下,所以SN拆分成PN与SEQ
SEQ = Format(Now, "YYYY") & Format(Now, "MM") & Format(Now, "DD") & Format(Now, "hhmmss") '同上
ItemDesc1 = Sheets(sheetname).Range("B18") '获取中文描述
ItemDesc2 = Sheets(sheetname).Range("B19") '获取中文描述
CDT = Format(Now, "yyyy/mm/dd") & " " & Format(Now, "hh:mm:ss") '获取格式化后时间格式
PIK = Sheets(sheetname).Range("B20") '获取看板值,以上值在每个sheet都是固定的
'定义文本文件的名称
Path = ThisWorkbook.Path & "\history\"
If Dir(Path) <> "" Then
file = ThisWorkbook.Path & "\history\" & FSN & ".txt" '如果为真则把新建的txt文件放在该路径的路径
Else
MkDir (ThisWorkbook.Path & "\history") '如果为假则把先创建该文件夹,让后把新建的txt文件放在该路径的路径
file = ThisWorkbook.Path & "\history\" & FSN & ".txt"
End If
Open file For Output As #1 '打开a.txt,指定文件编号为#1
Print #1, "^XA~TA000~JSN"
Print #1, "^LT0"
Print #1, "^MD3"
Print #1, "^PON"
Print #1, "^PMN"
Print #1, "^LH0,0"
Print #1, "^PR3,6"
Print #1, "^SD20"
Print #1, "^JUS"
Print #1, "^LRN"
Print #1, "^CI0"
Print #1, "^XZ"
Print #1, "^XA"
Print #1, "^CWS,E:SIMSUN.TTF"
Print #1, "^MMT"
Print #1, "^PW416"
Print #1, "^LL0320"
Print #1, "^LS0 "
Print #1, "^FT33,166^BQN,2,4"
Print #1, "^FH\^FDHA," & SN & "^FS "
Print #1, "^FPH,2^FT153,61^A0N,23,25^FH\^CI28^FD" & PN & "^FS^CI27"
Print #1, "^FPH,2^FT153,90^A0N,23,25^FH\^CI28^FD" & SEQ & "^FS^CI27"
Print #1, "^FPH,2^FT153,145^A0N,11,10^FH\^CI28^FD*****^FS^CI27"
Print #1, "^FPH,2^FT253,145^A0N,11,10^FH\^CI28^FD" & CDT & "^FS^CI27"
Print #1, "^FPH,2^FT33,170^ASN,15,10^FH\^CI26^FD" & ItemDesc1 & "^FS^CI27"
Print #1, "^FPH,2^FT33,190^ASN,15,10^FH\^CI26^FD" & ItemDesc2 & "^FS^CI27"
Print #1, "^FO23,190^GB370,0,4^FS"
Print #1, "^FPH,2^FT90,220^ASN,23,25^FH\^CI26^FD包覆件报产条码^FS^CI27"
Print #1, "^BY2,3,40^FT35,270^BCN,,Y,N^FH\^FD" & PIK & "^FS^CI27"
Print #1, "^PQ1,0,1,Y"
Print #1, "^XZ"
Close #1
optxt = file
'MyPrinter = "\\10.165.98.33\IP_Print_ZDesigner ZT410-203dpi ZPL(test)"
MyPrinter = "\\10.165.98.33\IP-print-ZDesigner ZT411-203dpi ZPL(station20)" '打印机位置,这种方式默认对共享打印机有效。
NumLabels = 1 '打印数量
Open MyPrinter For Output As #2 '打开打印机通道,指定文件编号为#2
Print #2, "^XA~TA000~JSN"
Print #2, "^LT0"
Print #2, "^MD3"
Print #2, "^PON"
Print #2, "^PMN"
Print #2, "^LH0,0"
Print #2, "^PR3,6"
Print #2, "^SD20"
Print #2, "^JUS"
Print #2, "^LRN"
Print #2, "^CI0"
Print #2, "^XZ"
Print #2, "^XA"
Print #2, "^MMT"
Print #2, "^PW416"
Print #2, "^LL0320"
Print #2, "^LS0 "
Print #2, "^CWS,E:SIMSUN.TTF" '使用simun宋体,且别名为S
Print #2, "^SEE:GB18030.DAT^CI26" '新打印机必须用该命令为SIMSUN指定GB18030.DAT编码
Print #2, "^FT33,166^BQN,2,4"
Print #2, "^FH\^FDHA," & SN & "^FS "
Print #2, "^FPH,2^FT153,61^A0N,23,25^FH\^CI28^FD" & PN & "^FS^CI27"
Print #2, "^FPH,2^FT153,90^A0N,23,25^FH\^CI28^FD" & SEQ & "^FS^CI27"
Print #2, "^FPH,2^FT153,145^A0N,11,10^FH\^CI28^FD**********^FS^CI27"
Print #2, "^FPH,2^FT253,145^A0N,11,10^FH\^CI28^FD" & CDT & "^FS^CI27"
Print #2, "^FPH,2^FT33,170^ASN,15,10^FH\^CI26^FD" & ItemDesc1 & "^FS^CI27"
Print #2, "^FPH,2^FT33,185^ASN,15,10^FH\^CI26^FD" & ItemDesc2 & "^FS^CI27"
Print #2, "^FO23,190^GB370,0,4^FS"
Print #2, "^FPH,2^FT90,220^ASN,23,25^FH\^CI26^FD****包覆件报产条码^FS^CI27"
Print #2, "^BY2,3,40^FT35,270^BCN,,Y,N^FH\^FD" & PIK & "^FS^CI27"
Print #2, "^PQ" & NumLabels & ",0,1,Y"
Print #2, "^XZ"
Close #2
End Function
打印机代码
ZEBRA 官网中文调用
通过 VBA(Visual Basic for Applications)发送 ZPL (zebra.cn)https://supportcommunity.zebra.cn/s/article/Sending-ZPL-via-VBA-Visual-Basic-for-ApplicationsArticle Detail (zebra.cn)
https://supportcommunity.zebra.cn/s/article/Zebra-Printer-Chinese-SimSun-font