Print from SAP using access ?? Without having to open SAP

Question

I want to control SAP from access to print a barcode label just by pressing a button in access.

Currently I have a script that only works if SAP is open

session.findById("wnd[0]/tbar[0]/okcd").Text ="pickLabel" 
session.findById("wnd[0]").sendVKey 0 
session.findById("wnd[0]/usr/ctxtY_P_MAT").Text = "testbox" 
session.findById("wnd[0]/usr/txtY_P_QUA").Text = "1" 
session.findById("wnd[0]/usr/ctxtY_P_LGN").Text = "test" 
session.findById("wnd[0]/usr/ctxtY_P_PRI").Text = "testPrinter" 
session.findById("wnd[0]/usr/txtY_P_KSC").Text = "test" 
session.findById("wnd[0]/usr/txtY_P_KSC").SetFocus 
session.findById("wnd[0]/usr/txtY_P_KSC").caretPosition = 4 
session.findById("wnd[0]").sendVKey 0 
session.findById("wnd[0]/tbar[1]/btn[8]").press

I would like to have a way to do it without having SAP open. Also I need to record the barcode number, but in the screen this number is not shown.

Any idea?

Thanks!!!

This script obviously accesses parts of the SAP GUI, so it can only work with SAP open. — Andre
If you're using the VBscript part of SAP then you must realize you are accessing the data strictly from the GUI's perspective. If the GUI takes the number and renders a barcode you may never find the number. You're fighting an uphill battle that may be better suited by talking with your database admin on best practices to access the data you desire in a reliable fashion. (Your DB admin may be able to export the data on a daily/hourly basis for your consumption, create a link to the table in Access through SAP, etc.) — Cody Ｇ
The above comment is quite reasonable. Daily exports or even direct connection to underlying DB is more appropriate in your use-case. — Suncatcher

Shodan Shodan · Accepted Answer · 2016-07-21T00:03:13

I might have a solution for you. I too needed SAP to print stickers on my Zebra Z4Mplus sticker printer.

What I did was use excel to generate my sticker and send them directly to the printer

First step was installing the printer, it is a TCP/IP printer that you have to connect directly to. The printer had a function to print all settings, I did that then used windows advanced printer setup, create a local TCP/IP port for the printer and added it. Google made this step easy.

Then in excel I inserted a chart on my worksheet.

then I created a shape and created the following code.

This code is an example, my code is much bigger, you need to generate all elements.

I used the GetPrinterFullNames function from pixcels.nl/set-activeprinter-excel/

For barcodes I used the following class stackoverflow.com/questions/13909248/generating-code-128-barcodes-using-excel-vba

You need to find the code128.fft file, I don't remember where I got it.

I also have a module for adding font to system without being administrator (using AddFontResource), storing font in excel worksheet I got this module from this thread

http://www.mrexcel.com/forum/excel-questions/328457-how-do-you-embed-font-into-excel-file.html

So here is my code for printer my stickers.

Sub PrintExportControl()

Dim myprinter As String Dim PrintersList() As String Dim printer_name As String Dim x As Long

GenerateExportControl

myprinter = Application.ActivePrinter
PrintersList() = GetPrinterFullNames

For x = 1 To UBound(PrintersList)
    If InStr(1, PrintersList(x), "ExportControl", vbTextCompare) > 0 Then _
        printer_name = PrintersList(x)
Next x

Application.ActivePrinter = printer_name

DoEvents

Sheet1.Shapes("chtExportControl").Chart.PrintOut , , , , "ExportControl"
Sheet1.Shapes("chtExportControl").Chart.PrintOut , , , , "ExportControl"

'Sheet1.Shapes("chtZSMI").Chart.PrintOut , , , , "ExportControl"
'Sheet1.Shapes("chtZSMI").Chart.PrintOut , , , , "ExportControl"

Application.ActivePrinter = myprinter

End Sub

And here is my code for generating the chart which has the sticker on it

Sub GenerateExportControl()

    Dim mychart As Chart

    Dim picLogo As Shape

    Dim shpExportControl As Shape
    Dim shpDate As Shape
    Dim shpBadge As Shape

    Dim shpPN As Shape
    Dim shpSN As Shape
    Dim shpESN As Shape
    Dim shpPartDescription As Shape
    Dim shpCSOrder As Shape
    Dim shpSO As Shape

    Dim shpCSOrderBarcode As Shape
    Dim shpSOBarcode As Shape

    Dim shp1stMil As Shape
    Dim shpPUSML As Shape
    Dim shpUSML As Shape
    Dim shpITARCID As Shape
    Dim shpPECCN As Shape
    Dim shpECCN As Shape
    Dim shpECL As Shape

    ' Get the TEMP path
    Dim FSO As Object, TmpFolder As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    Set TmpFolder = FSO.GetSpecialFolder(2)

    ' Focus a cell
    ActiveSheet.Range("A92").Select

    ' Make sure the "Code 128" font is available
    If FontIsInstalled("Code 128") = False Then
        CreateFontFile
        AddFont
    End If

    Dim BarcodeGenerator As clsBarcode
    Set BarcodeGenerator = New clsBarcode

    Set mychart = Sheet1.Shapes("chtExportControl").Chart

    mychart.Parent.Width = 288
    mychart.Parent.Height = 144
    mychart.ChartArea.Border.LineStyle = xlNone

    For Each MyShape In mychart.Shapes
        MyShape.Delete
    Next

    Set picLogo = mychart.Shapes.AddPicture("C:\Users\pw46487\Desktop\archive\programs\pwc-faded.jpg", msoTrue, msoTrue, 0, 0, 100, 79.39)
    picLogo.Left = mychart.Parent.Width / 2 - picLogo.Width / 2
    picLogo.Top = mychart.Parent.Height / 2 - picLogo.Height / 2

    Set shpExportControl = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 181, 17)
    shpExportControl.TextFrame.Characters.Text = "EXPORT CLASSIFICATION"
    shpExportControl.TextFrame.Characters.Font.Size = 18
    shpExportControl.TextFrame.MarginLeft = 0
    shpExportControl.TextFrame.MarginTop = 0
    shpExportControl.TextFrame.MarginRight = 0
    shpExportControl.TextFrame.MarginBottom = 0
    shpExportControl.TextFrame.AutoSize = True

    Set shpDate = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, 185, 0, 61, 11)
    shpDate.TextFrame.Characters.Text = Day(Date) & "-" & MonthName(Month(Date), True) & "-" & Year(Date)
    If IsDate(Worksheets("Sheet1").Range("B68")) Then _
        shpDate.TextFrame.Characters.Text = Day(Worksheets("Sheet1").Range("B68")) & "-" & _
        MonthName(Month(Worksheets("Sheet1").Range("B68")), True) & "-" & _
        Year(Worksheets("Sheet1").Range("B68"))

    shpDate.TextFrame.Characters.Font.Size = 12
    shpDate.TextFrame.MarginLeft = 0
    shpDate.TextFrame.MarginTop = 0
    shpDate.TextFrame.MarginRight = 0
    shpDate.TextFrame.MarginBottom = 0
    shpDate.TextFrame.AutoSize = True
    shpDate.Left = ((mychart.Parent.Width - shpDate.Width - shpExportControl.Width) / 2) + shpExportControl.Left + shpExportControl.Width

    Set shpBadge = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, 185, 0, 100, 11)
    shpBadge.TextFrame.Characters.Text = "Badge: " & Split(Worksheets("Sheet1").Range("B11"), " ")(0)
    shpBadge.TextFrame.Characters.Font.Size = 12
    shpBadge.TextFrame.MarginLeft = 0
    shpBadge.TextFrame.MarginTop = 0
    shpBadge.TextFrame.MarginRight = 0
    shpBadge.TextFrame.MarginBottom = 0
    shpBadge.TextFrame.AutoSize = True
    shpBadge.Top = shpDate.Top + shpDate.Height + 2
    shpBadge.Left = ((mychart.Parent.Width - shpBadge.Width - shpExportControl.Width) / 2) + shpExportControl.Left + shpExportControl.Width

    Set shpPN = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, 185, 4.5, 100, 11)
    shpPN.TextFrame.Characters.Text = "PN: " & Worksheets("Sheet1").Range("B1")
    shpPN.TextFrame.Characters.Font.Size = 11
    shpPN.TextFrame.MarginLeft = 0
    shpPN.TextFrame.MarginTop = 0
    shpPN.TextFrame.MarginRight = 0
    shpPN.TextFrame.MarginBottom = 0
    shpPN.TextFrame.AutoSize = True
    shpPN.Top = shpBadge.Top + shpBadge.Height - 2
    shpPN.Left = mychart.Parent.Width - shpPN.Width
    If Worksheets("Sheet1").Range("B1") = "" Then shpPN.Visible = False

    Set shpSN = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, 185, 4.5, 100, 11)
    shpSN.TextFrame.Characters.Text = "SN: " & Worksheets("Sheet1").Range("B4")
    shpSN.TextFrame.Characters.Font.Size = 11
    shpSN.TextFrame.MarginLeft = 0
    shpSN.TextFrame.MarginTop = 0
    shpSN.TextFrame.MarginRight = 0
    shpSN.TextFrame.MarginBottom = 0
    shpSN.TextFrame.AutoSize = True
    shpSN.Top = shpPN.Top + shpPN.Height - 2
    shpSN.Left = mychart.Parent.Width - shpSN.Width
    If Worksheets("Sheet1").Range("B4") = "" Then shpSN.Visible = False

    Set shpESN = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, 185, 4.5, 100, 11)
    shpESN.TextFrame.Characters.Text = "ESN: " & Worksheets("Sheet1").Range("B5")
    shpESN.TextFrame.Characters.Font.Size = 11
    shpESN.TextFrame.MarginLeft = 0
    shpESN.TextFrame.MarginTop = 0
    shpESN.TextFrame.MarginRight = 0
    shpESN.TextFrame.MarginBottom = 0
    shpESN.TextFrame.AutoSize = True
    shpESN.Top = shpSN.Top + shpSN.Height - 2
    shpESN.Left = mychart.Parent.Width - shpESN.Width
    If Worksheets("Sheet1").Range("B5") = "" Then shpESN.Visible = msoFalse

    Set shpPartDescription = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, 185, 4.5, 100, 11)
    shpPartDescription.TextFrame.Characters.Text = Worksheets("Sheet1").Range("B50")
    shpPartDescription.TextFrame.Characters.Font.Size = 11
    shpPartDescription.TextFrame.MarginLeft = 0
    shpPartDescription.TextFrame.MarginTop = 0
    shpPartDescription.TextFrame.MarginRight = 0
    shpPartDescription.TextFrame.MarginBottom = 0
    shpPartDescription.TextFrame.AutoSize = True
    shpPartDescription.Top = mychart.Parent.Height - shpPartDescription.Height
    shpPartDescription.Left = mychart.Parent.Width - shpPartDescription.Width

    Set shpCSOrder = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, 185, 4.5, 100, 11)
    shpCSOrder.TextFrame.Characters.Text = "CS:" & Worksheets("Sheet1").Range("B48")
    shpCSOrder.TextFrame.Characters.Font.Size = 11
    shpCSOrder.TextFrame.MarginLeft = 0
    shpCSOrder.TextFrame.MarginTop = 0
    shpCSOrder.TextFrame.MarginRight = 0
    shpCSOrder.TextFrame.MarginBottom = 0
    shpCSOrder.TextFrame.AutoSize = True
    shpCSOrder.Top = shpPartDescription.Top - shpCSOrder.Height
    shpCSOrder.Left = mychart.Parent.Width - shpCSOrder.Width
    If Worksheets("Sheet1").Range("B48") = "" Then shpCSOrder.Visible = msoFalse


    Sheet1.Shapes("chtBarcode2Pic").Chart.Shapes("TextBox 1").TextFrame.Characters.Text = BarcodeGenerator.Code128_Str(Worksheets("Sheet1").Range("B48"))
    ' Erase temporary file if exists
    If FileExists(TmpFolder & "bufferbarcode.jpg") Then Kill TmpFolder & "bufferbarcode.jpg"
    ' Output picture of barcode to file
    Sheet1.Shapes("chtBarcode2Pic").Chart.Export TmpFolder & "bufferbarcode.jpg", "jpg"
    Set shpCSOrderBarcode = mychart.Shapes.AddPicture(TmpFolder & "bufferbarcode.jpg", msoTrue, msoTrue, 0, 0, 100, 22)
    shpCSOrderBarcode.Top = shpCSOrder.Top - shpCSOrderBarcode.Height + 3
    shpCSOrderBarcode.Left = mychart.Parent.Width - shpCSOrderBarcode.Width + 3
    If Worksheets("Sheet1").Range("B48") = "" Then shpCSOrderBarcode.Visible = msoFalse


    Set shpSO = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, 185, 4.5, 100, 11)
    shpSO.TextFrame.Characters.Text = "SO:" & Worksheets("Sheet1").Range("B29")
    shpSO.TextFrame.Characters.Font.Size = 11
    shpSO.TextFrame.MarginLeft = 0
    shpSO.TextFrame.MarginTop = 0
    shpSO.TextFrame.MarginRight = 0
    shpSO.TextFrame.MarginBottom = 0
    shpSO.TextFrame.AutoSize = True
    shpSO.Top = shpCSOrderBarcode.Top - shpSO.Height + 3
    shpSO.Left = mychart.Parent.Width - shpSO.Width
    If Worksheets("Sheet1").Range("B29") = "" Then shpSO.Visible = msoFalse

    Sheet1.Shapes("chtBarcode2Pic").Chart.Shapes("TextBox 1").TextFrame.Characters.Text = BarcodeGenerator.Code128_Str(Worksheets("Sheet1").Range("B29"))
    ' Erase temporary file if exists
    If FileExists(TmpFolder & "bufferbarcode.jpg") Then Kill TmpFolder & "bufferbarcode.jpg"
    ' Output picture of barcode to file
    Sheet1.Shapes("chtBarcode2Pic").Chart.Export TmpFolder & "bufferbarcode.jpg", "jpg"

    Set shpSOBarcode = mychart.Shapes.AddPicture(TmpFolder & "bufferbarcode.jpg", msoTrue, msoTrue, 0, 0, 100, 22)
    shpSOBarcode.Top = shpSO.Top - shpSOBarcode.Height + 3
    shpSOBarcode.Left = mychart.Parent.Width - shpSOBarcode.Width + 3
    If Worksheets("Sheet1").Range("B29") = "" Then shpSOBarcode.Visible = msoFalse


    Set shp1stMil = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, shpExportControl.Left, shpExportControl.Top + shpExportControl.Height, 100, 11)
    shp1stMil.TextFrame.Characters.Text = "1st MILITARY:(" & Worksheets("Sheet1").Range("B21") & ")"
    shp1stMil.TextFrame.Characters.Font.Size = 13
    shp1stMil.TextFrame.MarginLeft = 0
    shp1stMil.TextFrame.MarginTop = 0
    shp1stMil.TextFrame.MarginRight = 0
    shp1stMil.TextFrame.MarginBottom = 0
    shp1stMil.TextFrame.AutoSize = True
    shp1stMil.Fill.BackColor.RGB = RGB(255, 255, 255)
    If Worksheets("Sheet1").Range("B21") = "" Then shp1stMil.Visible = msoFalse

    Set shpPUSML = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, shpExportControl.Left, shp1stMil.Top + shp1stMil.Height, 100, 11)
    shpPUSML.TextFrame.Characters.Text = "P-USML:(" & Worksheets("Sheet1").Range("B22") & ")"
    shpPUSML.TextFrame.Characters.Font.Size = 13
    shpPUSML.TextFrame.MarginLeft = 0
    shpPUSML.TextFrame.MarginTop = 0
    shpPUSML.TextFrame.MarginRight = 0
    shpPUSML.TextFrame.MarginBottom = 0
    shpPUSML.TextFrame.AutoSize = True
    shpPUSML.Fill.BackColor.RGB = RGB(255, 255, 255)
    If Worksheets("Sheet1").Range("B22") = "" Then shpPUSML.Visible = msoFalse

    Set shpUSML = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, shpExportControl.Left, shpPUSML.Top + shpPUSML.Height, 100, 11)
    shpUSML.TextFrame.Characters.Text = "USML:(" & Worksheets("Sheet1").Range("B23") & ")"
    shpUSML.TextFrame.Characters.Font.Size = 13
    shpUSML.TextFrame.MarginLeft = 0
    shpUSML.TextFrame.MarginTop = 0
    shpUSML.TextFrame.MarginRight = 0
    shpUSML.TextFrame.MarginBottom = 0
    shpUSML.TextFrame.AutoSize = True
    shpUSML.Fill.BackColor.RGB = RGB(255, 255, 255)
    If Worksheets("Sheet1").Range("B23") = "" Then shpUSML.Visible = msoFalse

    Set shpITARCID = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, shpExportControl.Left, shpUSML.Top + shpUSML.Height, 100, 11)
    shpITARCID.TextFrame.Characters.Text = "ITAR CID:(" & Worksheets("Sheet1").Range("B24") & ")"
    shpITARCID.TextFrame.Characters.Font.Size = 13
    shpITARCID.TextFrame.MarginLeft = 0
    shpITARCID.TextFrame.MarginTop = 0
    shpITARCID.TextFrame.MarginRight = 0
    shpITARCID.TextFrame.MarginBottom = 0
    shpITARCID.TextFrame.AutoSize = True
    shpITARCID.Fill.BackColor.RGB = RGB(255, 255, 255)
    If Worksheets("Sheet1").Range("B24") = "" Then shpITARCID.Visible = msoFalse

    Set shpPECCN = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, shpExportControl.Left, shpITARCID.Top + shpITARCID.Height, 100, 11)
    shpPECCN.TextFrame.Characters.Text = "P-ECCN:(" & Worksheets("Sheet1").Range("B25") & ")"
    shpPECCN.TextFrame.Characters.Font.Size = 13
    shpPECCN.TextFrame.MarginLeft = 0
    shpPECCN.TextFrame.MarginTop = 0
    shpPECCN.TextFrame.MarginRight = 0
    shpPECCN.TextFrame.MarginBottom = 0
    shpPECCN.TextFrame.AutoSize = True
    shpPECCN.Fill.BackColor.RGB = RGB(255, 255, 255)
    If Worksheets("Sheet1").Range("B25") = "" Then shpPECCN.Visible = msoFalse

    Set shpECCN = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, shpExportControl.Left, shpPECCN.Top + shpPECCN.Height, 100, 11)
    shpECCN.TextFrame.Characters.Text = "ECCN:(" & Worksheets("Sheet1").Range("B26") & ")"
    shpECCN.TextFrame.Characters.Font.Size = 13
    shpECCN.TextFrame.MarginLeft = 0
    shpECCN.TextFrame.MarginTop = 0
    shpECCN.TextFrame.MarginRight = 0
    shpECCN.TextFrame.MarginBottom = 0
    shpECCN.TextFrame.AutoSize = True
    shpECCN.Fill.BackColor.RGB = RGB(255, 255, 255)
    If Worksheets("Sheet1").Range("B26") = "" Then shpECCN.Visible = msoFalse

    Set shpECL = mychart.Shapes.AddTextbox(msoTextOrientationHorizontal, shpExportControl.Left, shpECCN.Top + shpECCN.Height, 100, 11)
    shpECL.TextFrame.Characters.Text = "ECL:(" & Worksheets("Sheet1").Range("B27") & ")"
    shpECL.TextFrame.Characters.Font.Size = 13
    shpECL.TextFrame.MarginLeft = 0
    shpECL.TextFrame.MarginTop = 0
    shpECL.TextFrame.MarginRight = 0
    shpECL.TextFrame.MarginBottom = 0
    shpECL.TextFrame.AutoSize = True
    shpECL.Fill.BackColor.RGB = RGB(255, 255, 255)
    If Worksheets("Sheet1").Range("B27") = "" Then shpECL.Visible = msoFalse

End Sub

Good luck

Print from SAP using access ?? Without having to open SAP

1 Answers