在VBA Excel表单上向标签添加更多变量/文本

7xzttuei  于 2022-12-01  发布在  其他
关注(0)|答案(1)|浏览(198)

我试图用VBA在Excel上制作一个标签条形码生成器,但遇到了一些语法问题。我设法打印了一个条形码,但我想在B2下分配更多的变量,如B3、B4、B5、B6等,并在条形码下面或旁边显示文本。这将是分配给每个单元格的简单字符串变量,然后在按下按钮后打印在标签上。
我试着查找VBA语法一整天,但遇到错误。有人知道如何将额外的文本添加到此条形码吗?下面是我附加到按钮的代码。其余的只是Excel工作表上的信息。

Sub Button2_Click()
    Const BarcodeWidth As Integer = 156
    Dim ws As Worksheet, WdApp
    
    
    Set ws = ActiveSheet
    
    With ws
        Range("B2").Select
    End With
    
    Set WdApp = CreateObject("Word.Application")
    With WdApp.Documents.Add
        .PageSetup.RightMargin = .PageSetup.PageWidth - .PageSetup.LeftMargin - BarcodeWidth
       
        .Fields.Add(Range:=.Range, Type:=-1, Text:="DISPLAYBARCODE " & CStr(Selection.Value) & " CODE39 \d \t", PreserveFormatting:=False).Copy
        
        
    End With
    ws.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
    WdApp.Quit SaveChanges:=False
    Set WdApp = Nothing
End Sub
jogvjijk

jogvjijk1#

不清楚附加文本是应该包含在条形码中,还是只是显示在条形码下面。对于后者,请尝试以下内容:

Sub Button2_Click()
Dim WdApp As Object, WdDoc As Object, StrCd As String, StrTxt As String
With ActiveSheet
  StrCd = Chr(34) & .Range("B2").Value & Chr(34)
  StrTxt = Chr(11) & .Range("B3").Value & " " & .Range("B4").Value & Chr(11) & .Range("B5").Value & " " & .Range("B6").Value
End With
Set WdApp = CreateObject("Word.Application"): Set WdDoc = WdApp.Documents.Add
With WdDoc
  .PageSetup.PageWidth = 180: .PageSetup.RightMargin = 36: .PageSetup.LeftMargin = 36
  .Fields.Add .Range, -1, "DISPLAYBARCODE " & StrCd & " CODE39 \d \t", False
  With .Range
    With .ParagraphFormat
      .Alignment = 1 'wdAlignParagraphCenter
      .LineSpacingRule = 0 'wdLineSpaceSingle
      .SpaceBefore = 0
      .SpaceAfter = 0
    End With
    .Characters.last.Text = StrTxt
    .Copy
  End With
  ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
  .Close False
End With
Set WdDoc = Nothing: WdApp.Quit: Set WdApp = Nothing
End Sub

相关问题