Solved

MS Access Dymo SDK Help Translating VB Net or Excel VBA Code to VBA Access

Posted on 2016-11-01
11
37 Views
Last Modified: 2016-11-02
I am trying to figure outhow to print labels directly from Access to my Dymo printer through SDK. Dymo has a small sample Access file to help show some features but this is all confusing to me. Anywasy I have found several codes for VB Net and was wondering if anyone could translate this to VBA for me so I can try to make it work. Thanks.

Sample database:

Dymo Sample Database Click Here

Private Label As DYMO.Label.Framework.ILabel
Label = DYMO.Label.Framework.Framework.Open("c:Documents and SettingsAll 
UsersDocumentsDYMO LabelLabel FilesTestLabel.label")
Label.SetObjectText("TEST_BARCODE", "9876543")
Label.Print("DYMO LabelWriter 450 Turbo")

Open in new window



Or can this VBA be converted from Excel to Access?
Sub TestLabel()
    
    Dim myDymo As DYMO_DLS_SDK.DymoHighLevelSDK
    Dim dyAddin As DYMO_DLS_SDK.ISDKDymoAddin
    Dim dyLabel As DYMO_DLS_SDK.ISDKDymoLabels
    
    Set myDymo = New DYMO_DLS_SDK.DymoHighLevelSDK
    
    Set dyAddin = myDymo.DymoAddin
    Set dyLabel = myDymo.DymoLabels
    
    dyAddin.SelectPrinter dyAddin.GetDymoPrinters
        
    dyAddin.Open Environ$("USERPROFILE") & "\My Documents\DYMO Label\Labels\BoardFile.label"
    dyLabel.SetField "Text", "My text goes here"
    dyAddin.Print2 1, True, 1
    
    Set myDymo = Nothing
    
End Sub

Open in new window

0
Comment
Question by:Dustin Stanley
  • 6
  • 4
11 Comments
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 41869649
Hi,

Since there is no Addin available for Access you could probably open the excel document from Access run a modified macro

in the Excel File
Sub TestLabel(strText as String)
    
    Dim myDymo As DYMO_DLS_SDK.DymoHighLevelSDK
    Dim dyAddin As DYMO_DLS_SDK.ISDKDymoAddin
    Dim dyLabel As DYMO_DLS_SDK.ISDKDymoLabels
    
    Set myDymo = New DYMO_DLS_SDK.DymoHighLevelSDK
    
    Set dyAddin = myDymo.DymoAddin
    Set dyLabel = myDymo.DymoLabels
    
    dyAddin.SelectPrinter dyAddin.GetDymoPrinters
        
    dyAddin.Open Environ$("USERPROFILE") & "\My Documents\DYMO Label\Labels\BoardFile.label"
    dyLabel.SetField "Text", strText
    dyAddin.Print2 1, True, 1
    
    Set myDymo = Nothing
    
End Sub

Open in new window


in Access

Sub MacroDymo()
Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("C:\YourFolder\YourFile.xlsm")
xl.Run "TestLabel", "My text goes here"
xl.ActiveWorkbook.Close (False)
xl.Quit
Set xl = Nothing
End Sub

Open in new window

Regards
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41869966
There's no reason to use Excel....you can do this in access.

You need to install the SDK, then set a reference to the object lib.   Once that's done, the sample DB will work.

Jim.
0
 

Author Comment

by:Dustin Stanley
ID: 41870185
you can do this in access.

You need to install the SDK, then set a reference to the object lib.   Once that's done, the sample DB will work.

**First off is there a place or something that I can look through which codes are supplied in these refrence libraries so I know what exists and what doesn't?

I installed the SDK from dymo and tried different refrences to the libraries and other things Dymo had to offer in the refrences and online. I tried the Sample Database and It works as in they intended with me filling out a string in the text box and printing just some text on a blank label. But in the actual SDK install there is a Sample DB called Attendees of printing to saved label templates. I tried yesterday for around 10-12 hrs straight to figure this out and no luck as in being able to get these to print what I need from my database forms.

I just want to be able to Print 2 Control Fields from my Access frmSkusEntry form to a saved Dymo (.Label Format) Label. There is a Barcode and a text box with an image on the Dymo Label. The image is always the same.
I just found these codes online and thought maybe if I could alter them I could go from there. Thanks for the help!
Here is that Attendee Sample
ATTENDEE.MDB
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41870247
<<**First off is there a place or something that I can look through which codes are supplied in these refrence libraries so I know what exists and what doesn't?>>

 That should be covered in the SDK (I haven't looked through it).   But once a reference is set in VBA, then the object browser should work for that lib and give you all the properties and methods for their object lib.

<<I tried the Sample Database and It works as in they intended with me filling out a string in the text box and printing just some text on a blank label. >>

 OK.

<<I tried yesterday for around 10-12 hrs straight to figure this out and no luck as in being able to get these to print what I need from my database forms. >>

Here the setup is a little different.   Rather than setting a reference and calling methods in a object lib or DLL, they are creating an OLE object:

Function CreateOLEObjects()
    '****************************************************************************
    'This fuction creates Dymo OLE objects
    '****************************************************************************
    On Error Resume Next
    
    Set DymoAddIn = CreateObject("Dymo.DymoAddIn")
    Set DymoLabels = CreateObject("Dymo.DymoLabels")
    'check if successful
    If (DymoAddIn Is Nothing) Or (DymoLabels Is Nothing) Then
        MsgBox "Unable to create OLE objects"
    End If
End Function

Open in new window


 and then controlling that. This is what you'd do if you were controlling Excel for example.

This is where they call that routine above:

 
   On Error Resume Next
    Call GetDesc(Desc)                            'gets description of current label
    Call GetObject(Obj)                           'defines object to paste text to
    Call CreateOLEObjects                          'create Dymo OLE objects
        'opens the template in DLS using the correct label type
    DymoAddIn.Open path + FileStr 'open label in DLS

Open in new window


and then later on in that routine (PrintLabel()):

        DymoLabels.SetField Obj, Str 'send Str to DLS
        'change the Title on the label
        DymoLabels.SetField "Title", BadgeTitle
            
        q = DymoAddIn.Print(1, True)               'print the label
        Call DestroyOLEObjects ' destroy objects

Open in new window


 Call methods (.SetField and .Print) to produce the labels, and finally cleaning up (DestroyOLEObjects()).

  Where did you get hung up?

Jim.
0
 

Author Comment

by:Dustin Stanley
ID: 41870318
Ok thanks. I will be back in my office soon and I will see about recreating the problem. I will start over from scratch. But I do know yesterday the strangest thing with it was happening. I got it to recognize my exact label and I could only refrence one field on my dymo label but later....No matter what I changed or what part of code I left out or even added it was printing a label that I printed two days ago. I cleared everything and even printed inside dymo a different label and then tried again printing in access and it still printed this same two day old label. it was weird. I will get back with you soon. Thanks Jim.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Dustin Stanley
ID: 41870902
Where did you get hung up?

Being an idiot! I copied the original file from the original SDK folder into another folder and was using that copied Sample DB to work on so I wouldn't lose the original. Well I didn't copy the Dymo Label Software Label Templates I created in DLS also. So when the Sample DB was looking for those templates it defaulted back onto that 2 day old label. Why that one I don't know! But anyways I have figured it out for the most part (Victory Dance after first properly printed label ;)  and I am able to print labels from my subforms. Thanks for your help and breaking it down also.  I will paste the code below of what I came up with.  

But I do have a side question or I can just post another one.
How would I make a number combo box that would be linked back to this print code and loop it. Say I need 10 labels?

Option Compare Database
Option Explicit

'*********************************************************************************
'DLS is Abbreviation for Dymo Label Software. Just plain old Label Wrighter Software.
'High-Level OLE Demo program for Microsoft Access 97
'February 7, 1997
'Modified: October 24, 1997
'Written by: Andrew Shalhoub
'Modified: April 23, 1999
'Modified by: Sergey Smirnov
'
'Copyright 1997,2002 DYMO Corporation, Stamford CT
'
'This sample database and the code contained herein were designed to demonstrate
'a real world example of communicating with Dymo Label Software using High-Level
'OLE interface from MS Access.
'
'The sample is a simple database that could be used to track seminar attendees
'and print Address or Shipping labels or Name Badges for each record contained
'in the database.
'
'
'Permission to use, copy, modify, and distribute this software for any purpose
'and without fee is hereby granted.
'**********************************************************************************


'Windows API functions and constants to work with the Registry
Private Const REG_SZ As Long = 1
Private Const HKEY_LOCAL_MACHINE = &H80000002
Private Const HKEY_CURRENT_USER = &H80000001
Private Const KEY_ALL_ACCESS = &H3F
Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _
        "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
        ByVal ulOptions As Long, ByVal samDesired As Long, phkResult _
        As Long) As Long
Private Declare Function RegQueryValueExString Lib "advapi32.dll" Alias _
        "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
        String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
        As String, lpcbData As Long) As Long
Private Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias _
        "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
        String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
        As Long, lpcbData As Long) As Long

Global Channum As Long, App As String, Obj As String
Global FileStr As String, BarcodeSKU As String, Codebar As String
'OLE objects to communicate with DLS
Global DymoAddIn As Object, DymoLabels As Object
Global Loaded As Boolean

Function ChangeLabel()
    '****************************************************************************
    'This function updates the Filename caption on the form "frmSkusEntry"
    'when the Label Type is changed
    '****************************************************************************
    Dim dbs As Database, rst As Recordset
    Dim FileStr As String, Label As String
    Set dbs = CurrentDb                               'set the database
    Set rst = dbs.OpenRecordset("LabelTypes")         'open table
        'set Value to current Filename
    Forms![frmSkusEntry]!FileName.Caption = Forms![frmSkusEntry]!LabelType.Value
    Label = Forms![frmSkusEntry]!LabelType.Value              'set Labels to Filename
    rst.MoveFirst
    Do
        If rst![Description] = Label Then             'if match
            FileStr = rst![FileName]                  'then set FileStr to Filename
        End If
        rst.MoveNext                                  'if not then goto next record
    Loop Until rst.EOF                                'search to end of table
    Forms![frmSkusEntry]!FileName.Caption = FileStr           'reset caption on form "frmSkusEntry"
End Function


Function Defaults()
    '****************************************************************************
    'This function sets the Filename caption on form "frmSkusEntry" to current Label Type
    'and sets the variable Loaded to True (assumes DLS is already running)
    '****************************************************************************
    Dim dbs As Database, rst As Recordset
    Dim FileStr As String, Label As String
    Set dbs = CurrentDb                             'set database
    Set rst = dbs.OpenRecordset("LabelTypes")       'open the table
        'set Value to current Filename
    Forms![frmSkusEntry]!FileName.Caption = Forms![frmSkusEntry]!LabelType.Value
    Label = Forms![frmSkusEntry]!LabelType.Value            'set Label to Filename
    rst.MoveFirst
    FileStr = rst![FileName]                        'set FileStr to Filename
    Forms![frmSkusEntry]!FileName.Caption = FileStr         'update Filename caption on form
    Loaded = True
End Function


Function GetDesc(Desc)
    '****************************************************************************
    'This function returns the current Label Description by searching
    'the values in the table "LabelTypes"
    '****************************************************************************
    Dim dbs As Database, rst As Recordset
    Dim FileStr As String, Label As String
    Set dbs = CurrentDb                             'set the database
    Set rst = dbs.OpenRecordset("LabelTypes")       'open table
    Label = Forms![frmSkusEntry]!FileName.Caption           'set Label to filename on form
    rst.MoveFirst
    Do
        If rst![FileName] = Label Then              'if match
            Desc = rst![Description]                'then return description
        End If
        rst.MoveNext                                'if not then goto next record
    Loop Until rst.EOF                              'search to end of table
End Function

Function GetObject(Obj)
    '****************************************************************************
    'This function searches the table "LabelTypes" for a match
    'of the current Filename, then returns the name of the object on
    'that label which is the target for the selected text
    '****************************************************************************
    Dim dbs As Database, rst As Recordset
    Dim Label As String
    Set dbs = CurrentDb                         'set the database
    Set rst = dbs.OpenRecordset("LabelTypes")   'open table
    Label = Forms![frmSkusEntry]!FileName.Caption       'set Label to filename on form
    rst.MoveFirst
    Do
        If rst![FileName] = Label Then          'if match
            Obj = rst![PasteObject]             'then return object name
        End If
        rst.MoveNext                            'if not then goto next record
    Loop Until rst.EOF                          'search to end of table
End Function


Function PrintLabel()
    '****************************************************************************
    'This function prints the current record in the table
    'using the currently selected label
    '****************************************************************************
    Dim FileStr As String
    Dim Str As String, Pipe As String, Desc As String
    Dim hKey As Long, cb As Long, path As String
    Dim q
    BarcodeSKU =  ProductTitle = Forms!frmSkusEntry!SkuNm                  'define Title of seminar
    Codebar = Forms!frmSkusEntry!sbfrmProducts.Form!Text10
    Pipe = Chr(13) + Chr(10) 'line delimiter
    FileStr = Forms![frmSkusEntry]!FileName.Caption
    
    'Read label files path from the Registry
    'RegOpenKeyEx HKEY_CURRENT_USER, "Software\DYMO\LabelWriter\Directories", 0, KEY_ALL_ACCESS, hKey
    'RegQueryValueExNULL hKey, "Label Directory", 0&, REG_SZ, 0&, cb
    'path = String(cb, 0)
    'RegQueryValueExString hKey, "Label Directory", 0&, REG_SZ, path, cb
    'path = Left(path, cb - 1) + "\"
    path = GetDymoLabelFilePath
    
    On Error Resume Next
    Call GetDesc(Desc)                            'gets description of current label
    Call GetObject(Obj)                           'defines object to paste text to
    Call CreateOLEObjects                          'create Dymo OLE objects
        'opens the template in DLS using the correct label type
    DymoAddIn.Open path + FileStr 'open label in DLS

       
        'change the Title on the label
        DymoLabels.SetField "title", BarcodeSKU 'send to the property names of your label template to DLS (TEXT2 is a textbox on the label template in DLS)
        DymoLabels.SetField "Code128", Codebar
            
        q = DymoAddIn.Print(1, True)               'print the label
        Call DestroyOLEObjects ' destroy objects
End Function

Function CreateOLEObjects()
    '****************************************************************************
    'This fuction creates Dymo OLE objects
    '****************************************************************************
    On Error Resume Next
    
    Set DymoAddIn = CreateObject("Dymo.DymoAddIn")
    Set DymoLabels = CreateObject("Dymo.DymoLabels")
    'check if successful
    If (DymoAddIn Is Nothing) Or (DymoLabels Is Nothing) Then
        MsgBox "Unable to create OLE objects"
    End If
End Function


Function DestroyOLEObjects()
    '*********************************s*******************************************
    'This function destroys OLE object
    '****************************************************************************
    On Error Resume Next
    Set DymoAddIn = Nothing
    Set DymoLabels = Nothing
End Function

Public Function GetDBPath() As String 'Use if your database is NOT split. SEE FUNCTION GetDymoLabelFilePath 4 info
    GetDBPath = CurrentProject.path & "\"
End Function

Public Function GetDBPathSplitDB() As String 'Use if you have a Split Database. SEE FUNCTION GetDymoLabelFilePath 4 info
    GetDBPathSplitDB = Replace(CurrentDb.TableDefs("LabelTypes").Connect, ";DATABASE=", "")
'This code below is removing db name
GetDBPathSplitDB = Left(GetDBPathSplitDB, InStrRev(GetDBPathSplitDB, "\"))

End Function


Public Function GetDymoLabelFilePath() As String 'Choose which one to use below.
'Choose One Not Both. Remove the ' in the front of GetDBFilePath or GetDBPathSplitDB but make sure one of them has a ' and the other does not.

    GetDymoLabelFilePath = GetDBPath & "Dymo Labels\" 'Use if your database is NOT split.
    'GetDymoLabelFilePath = GetDBPathSplitDB & "Dymo Labels\"  ''Use if you have a Split Database.
    
End Function

Open in new window

0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41870923
<<How would I make a number combo box that would be linked back to this print code and loop it. Say I need 10 labels?>>

In Function PrintLabel()

        'change the Title on the label
        DymoLabels.SetField "title", BarcodeSKU 'send to the property names of your label template to DLS (TEXT2 is a textbox on the label template in DLS)
        DymoLabels.SetField "Code128", Codebar
           
   For intK = 1 to Me.mycombo
        q = DymoAddIn.Print(1, True)               'print the label
   Next intK

        Call DestroyOLEObjects ' destroy objects

Jim.
0
 

Author Comment

by:Dustin Stanley
ID: 41870955
THANK YOU JIM!

Function PrintLabel()
    '****************************************************************************
    'This function prints the current record in the table
    'using the currently selected label
    '****************************************************************************
    Dim FileStr As String
    Dim Str As String, Pipe As String, Desc As String
    Dim hKey As Long, cb As Long, path As String
    Dim intK
    Dim q
    ProductTitle = Forms!frmSkusEntry!SkuNm                   'define Title of seminar
    Codebar = Forms!frmSkusEntry!sbfrmProducts.Form!Text10
    Pipe = Chr(13) + Chr(10) 'line delimiter
    FileStr = Forms![frmSkusEntry]!FileName.Caption
    
    'Read label files path from the Registry
    'RegOpenKeyEx HKEY_CURRENT_USER, "Software\DYMO\LabelWriter\Directories", 0, KEY_ALL_ACCESS, hKey
    'RegQueryValueExNULL hKey, "Label Directory", 0&, REG_SZ, 0&, cb
    'path = String(cb, 0)
    'RegQueryValueExString hKey, "Label Directory", 0&, REG_SZ, path, cb
    'path = Left(path, cb - 1) + "\"
    path = GetDymoLabelFilePath
    
    On Error Resume Next
    Call GetDesc(Desc)                            'gets description of current label
    Call GetObject(Obj)                           'defines object to paste text to
    Call CreateOLEObjects                          'create Dymo OLE objects
        'opens the template in DLS using the correct label type
    DymoAddIn.Open path + FileStr 'open label in DLS

       
        'change the Title on the label
        DymoLabels.SetField "title", ProductTitle 'send to the property names of your label template to DLS (Title is a textbox on the label template in DLS)
        DymoLabels.SetField "Code128", Codebar
            
   For intK = 1 To Forms!frmSkusEntry!PrintLabelQTY
        q = DymoAddIn.Print(1, True)               'print the label
   Next intK

        Call DestroyOLEObjects ' destroy objects

End Function

Open in new window

0
 

Author Closing Comment

by:Dustin Stanley
ID: 41870957
THANK YOU!
0
 
LVL 57
ID: 41870969
BTW, I did not look at that method.  i.e. if the call has the first argument as the number of copies, then you'd simply do:

 q = DymoAddIn.Print(Forms!frmSkusEntry!PrintLabelQTY, True)

 instead of the loop.

 Printing multiples of a single label is a pretty common thing, so I would suspect that's what that first argument is.

Jim.
0
 

Author Comment

by:Dustin Stanley
ID: 41871036
Nailed it! THANKS!

'change the Title on the label
        DymoLabels.SetField "title", ProductTitle 'send to the property names of your label template to DLS (Title is a textbox on the label template in DLS)
        DymoLabels.SetField "Code128", Codebar
            
   q = DymoAddIn.Print(Forms!frmSkusEntry!PrintLabelQTY, True)
        Call DestroyOLEObjects ' destroy objects

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now