CorelDraw & VBA
Summary & Introduction
Guide to CorelDraw VBA
Creating Variable Data
Variable Data From Access
Variable Data From Excel
Variable Data Without a Data File
CorelDraw BarCode Wizard
EAN13 BarCodes Without The Wizard
Code 39 BarCodes Without The Wizard
ITF-14 BarCodes Without The Wizard
Code 128 BarCodes Without The Wizard
QR BarCodes
Variable Pictures
Sorting for Guillotining
Repositioning Data
Pantone Colors
Saving VBA Code to a Previous Version of CorelDraw
PhotoPaint
Miscellaneous VBA
Help
 
Miscellaneous VBA

Manipulating Files

When you want VBA to read file names, change file names, change file extensions, count files, change file attributes such as read only then you must use an antiquated part of VBA that relies on a script command.
Here is a bas file. Either right button the bas file and select "Save Target As...." and then import the bas file into your VBA code.
Alternatively select the bas file with the left button of your mouse and open the bas file.
You can select all and copy it into your VBA code. It should be self explanatory although complex.

Here is some code that replaces all of the under-scores with spaces in all file names within a named folder on your desktop.

        Dim objFOLDER As Object
        Dim objMAIN_FOLDER As Object
        Dim objFILES_ALL As Object
        Dim objFILE As Object
        Dim intFILE_DETAILS As Integer

        Set objFOLDER = Interaction.CreateObject("Scripting.FileSystemObject")
        'Specify the folder to examine.
        'Insert your details in the locations marked with <.....>.
        'Notice that there is no back-slash at the end of the folder path.
        Set objMAIN_FOLDER = objFOLDER.GetFolder("C:\documents and settings\<YOUR_NAME>\Desktop\<YOUR_FOLDER_NAME>")
        Set objFILES_ALL = objMAIN_FOLDER.Files
        For Each objFILE In objFILES_ALL
                'Check that the file name contains "_". If it does replace all the undercore characters.
                'Note that it does not search any folders within the folder.
                If InStr(1, objFILE.Name, "_") > 0 Then
                        objFILE.Name = Replace(objFILE.Name, "_", " ")
                End If
        Next




Selecting Folders or Files

VBA does not have an easy way of graphically selecting folders or files with a mouse. You need to use API commands.
I do not understand these, but standing on the shoulders of others you can use API commands.
Using a graphical user interface to select a file or folder does not open the file or folder.
Instead the code outputs the full path to the file/s or folder. You the use the path to open the file or path.

Here is some API based code, 32 bit Folders32Bit.bas and a 64 bit version Folders64Bit.bas that will display a directory structure of your computer.
You have the option to show the files as well.
They create a VBA version of Windows Explorer. You cannot unfortunately double click on a file or folder to accept.
The 32 bit version is by Jim Rech, Ken Getz and Michael Kaplan. I have modified their version to suit a 64 bit system.

Download the .bas file for your system and import the bas file into CorelDraw, Photo-Paint, Excel, Word, Access, Publisher or PowerPoint.

In your VBA code add

Dim strDESTINATION As String
strDESTINATION = <A reference to FOLDER_SELECTION>

The reference to FOLDER_SELECTION depends on the application and where the module Folders is located relative to your code calling the function FOLDER_SELECTION.

The variable strDESTINATION can be renamed to anything you like. When you run your VBA code a dialog box showing your computer's directory structure will appear.
After you have selected a folder or file your code strDESTINATION will contain the full path and name of the folder you selected.
With your own VBA code you can now examine and manipulate the contents of this folder or file.

At the end of the Folders code there is a sample program called TEST_DIALOG. Run this to see the dialog box.
There is also instructions on how to reference FOLDER_SELECTION and some options.

Here is an alternative file selector. It can select single or multiple files.
Its dialog box is the same as that used by windows. Here is a 64 bit version FileBrowser64Bit.bas



CorelDraw has a very good File Browser written in CorelScript, GetFileBox, but it still works in VBA. It is compact but you can only select single files.

       CorelScriptTools.GetFileBox(([Filter as String], _
         [Title as String], _
         [Type as Long], _
         [Default File to Select as String], _
         [Extension as String should the user not specify an extension], _
         [Default Folder as String], _
         [Button as String]) as String

The GetFileBox function by itself does not open or save a file; it only returns a string corresponding to the selected file.
It returns the selected filename and its full path, or an empty string if the user chooses Cancel.

Filter
The filter is text that alternates between text that describes the filter type and the extension filter.
Between each description and extension type is a | symbol.
If there are multiple extensions allowed with the one type of description separate the extensions with a semi-colon.

GetFileBox(File description|File type;Another file type|2nd File description|File type, Title of dialog box, Find or Save, Default file name or full path of default file if no default folder is specified or *.* for all files, , Default folder)

Title
If not specified, "Open" is displayed for an Open dialog box and "Save As" is displayed for a Save As dialog box.

Type
Numeric expression specifying the type of dialog box to display:
0 File Open dialog box (default if omitted)
1 File Save dialog box (Change the title to change to SaveAs.)
This means that this same function can be used for both Open, Save and SaveAs.
In all cases it returns the full file path that you use in
    OpenDocument(FileName As String, [CodePage As Long]) As Document
    ActiveDocument.SaveAs FileName As String, [SAVE_OPTIONS]

Default File to Select
Text to display in the File name text box of the dialog box. If not specified, the text box is empty.
If no Default Folder is specified then it can be specified here but it must also include a file even if it is just *.* or *.cdr

Extension
String expression specifying the default extension to append to a File name if the user omits the extension.

Default Folder
String expression specifying the default folder used by the dialog box.
If not specified, or the specified folder does not exist, the current folder is used.

Button
String expression specifying a button name to override the Open or Save button in the dialog box.
If not specified, the button's name remains unchanged.
I have not found this to work.

Examples
    Dim strPath As String
    ´Show all file types.
    strPath = CorelScriptTools.GetFileBox("All Files (*.*)|*.*", "Select a file", 0, "*.cdr", , "D:\")
    ´Show bitmap files of types bmp, tif gif and cpt plus text files of types txt and asc.
    strPath = CorelScriptTools.GetFileBox("Bitmap files|*.bmp;*.tif;*.gif;*.cpt|Text files|*.txt;*.asc, "Select a file", 0, "Rock.tif", , "D:\")
    ´Show CorelDraw files of types cdr plus text files of type txt.
    strPath = CorelScriptTools.GetFileBox("CorelDraw files|*.cdr|Text files|*.txt|All Files|*.*", "Select a file", 0, "Test.cdr", , "D:\")



Handling Num-Lock, Caps-Lock & Scroll-Lock

VBA does not have a way of activating Num-Lock, Caps-Lock or Scroll-Lock other than by using SendKeys.
The use of Sendkeys often turns off Num-Lock which is very annoying particularly to other people using your program. If you need to control Num-Lock use API's or use some 3rd party code.

Here is some API based code Num_Lock.bas that will activate Num-Lock. There appears to be no direct code that will activate Num-Lock every time only a means to toggle it on or off.
To overcome this, this bas file first checks that Num-Lock is off then runs code to toggle Num-Lock on. If Num-Lock is already on then it does not toggle Num-Lock and so leaves Num-Lock on.

You can import the bas file into your CorelDraw project so that it is stored within your CorelDraw file. If you want to run it when other CorelDraw files are open you must place it in each CorelDraw file.
The better alternative is to place it within CorelDraw itself so that whenever CorelDraw opens it is available to every CorelDraw file. To do this import the bas file into your "GlobalMacros" project.

Put the following code into your CorelDraw project to run the Lock_Numbers macro stored in your GlobalMacros.
'GMSManager.RunMacro "Name of gms project", "Name of module.Name of macro"
GMSManager.RunMacro "GlobalMacros", "Num_Lock.Lock_Numbers"


Alternatively if you import the bas file into your CorelDraw file simply inserting the command Lock_Numbers into your code will run the module and lock Num-Lock.

As stated earlier run Lock_Numbers some time after the last SendKey command to re-Lock Num-Lock.

If you study the bas file, I have left in the options to control Caps-Lock and Scroll-Lock.
In the code, if Const VK_LOCK = 144 then you control Num-Lock but if you want to control Caps-Lock or Scroll-Lock change the value from 144 to 20 or 145 respectively.


A Function to Control all 3 Key Locks

Here is Change_Locks.bas that allows complete control of locking and un-locking of the 3 keys; Num-Lock, Caps-Lock and Scroll-Lock.
Download the bas file then import it into either CorelDraw's GlobalMacros project or into your CorelDraw file.
At the end of the bas file is Sub TEST() so you can test the function and it contains instructions for its use.
Note that this bas file can be imported into any application that runs VBA such as Access, Excel and Word.
Within the bas file there are instructions to run the function in CorelDraw, Excel and Word.




Dealing with the Registry

CorelScriptTools.RegistryQuery

Corel has the corel script function CorelScriptTools.RegistryQuery(MainKey As Long, SubKey As String, Value As String) As Variant.

MainKey
    Specifies the main registry value key to query:
    0 = HKEY_CLASSES_ROOT
    1 = HKEY_CURRENT_USER
    2 = HKEY_LOCAL_MACHINE
    3 = HKEY_USERS
    4 = HKEY_PERFORMANCE_DATA
    5 = HKEY_CURRENT_CONFIG
    6 = HKEY_DYN_DATA

SubKey
    Specifies the sub registry value key to query. This must be a complete key path.

Value
    Specifies the registry value key to query.
    To specify a default value, use an empty string. e.g. ("").


To know the last URL typed into Internet Explorer.
    Dim RECENT_TYPED_URL As Variant
    RECENT_TYPED_URL = _
      CorelScriptTools.REGISTRYQUERY(1, "Software\Microsoft\Internet Explorer\TypedURLs", "url1")


To Determine a Font File Name
    Dim FONT_FILE_NAME As Variant
    FONT_FILE_NAME = _
      CorelScriptTools.REGISTRYQUERY(2, "Software\Microsoft\Windows NT\CurrentVersion\Fonts", "Arial (TrueType)")

Unfortunately it is not possible using this method to read each font unless the each font name is known.


RegObj.dll

This gives you the user friendliness of VBA but accesses the Windows API on you behalf.
Unfortunately it only deals with the registry not the rest of windows.
It allows you to read, modify, create and delete in the registry.

Because of the limitations of this free site. I am unable to store zip, rar or exe files.
To get around it I have removed the last "e" from the RegObj.exe file so it reads as Regobj.ex.
Please download RegObj.ex and manually add the e to the file extension so that it reads as RegObj.exe.

Run RegObj.exe to install RegObj.dll and it will install in C:\VBWebSamples\RegObj.
It will appear in the VBA References as Registration Manipulation Classes.
Unfortunately there is no help file but the document that is in the folder C:\VBWebSamples\RegObjcomes is excellent.


In this example it reads every installed Windows Font

  Dim objREGKEY As RegKey
  Dim lngFONT_COUNT As Long
  Dim strFONT_NAME As String
  Dim strFONT_FILE As String
  Dim regFONT As RegValue

  Set objREGKEY = _
    RegKeyFromString("\HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Fonts")

  'Total No of fonts.
  lngFONT_COUNT = objREGKEY.Values.Count

  'For each installed font.
  For Each regFONT In objREGKEY.Values

      'The first font's name is
      strFONT_NAME = regFONT.Name

      'The first font's file name is
      strFONT_FILE = regFONT.value

  Next

Downloading from the Internet

Automatic downloading from the internet is not well documented.
I have a way that pastes into PhotoPaint then saves the bitmap but here is method that does not rely on any particular application only the use of VBA and API's.

Here is an Excel file. The module and class modules also work in CorelDraw.

In the module DownloadHTTP you must personalise the destination address for your computer.
There is no need for you to alter the class modules. Run DownloadHTTP in the module RunModule to save a file.


Adding a Progress Meter

Windows 32 Bit Versions
Whilst VBA code is running it can be handy so see a moving line to indicate that the code is still progressing and it enables you to estimate when it will be completed.
Microsoft Access has a built-in progress meter but for other VBA programs you can use MSComCtl.ocx that is located in Windows\System32 if you are using a 32 bit Windows version such as XP or 7.
For 64 bit Windows versions MSComCtl.ocx does not work properly.

To use MSComCtl.ocx you will have to search for the actual file. It does not appear amongst the registered VBA files. Also by default the Reference Dialog Box only it shows *.olb, *.tlb and *.dll files.
After MSComCtl.ocx is added as a reference you can then add the the VBA ToolBox.
Insert a UserForm right button the ToolBox and select Add Additional Controls and then select Microsoft ProgressBar Control 6.0 (SP6).
Once selected a small cross will appear beside the text. Select OK and now there will be a new icon in the Toolbox circled in this picture below.



Select the icon then add the control to a form. You can alter the progress bar width, length etc.

Here is a CorelDraw X5 file that will open in either CorelDraw X5 or X6.
There is no need to reference MSComCtl.ocx or add it to the toolbar as the code does this for you.
The progressbar is started by running the procedure Run_This in the ProgressCode module.



If you have an earlier version of CorelDraw you will have to download the following 3 files by right clicking and selecting save target as ProgressCode.bas, UserForm1.frm and UserForm1.frx.
Import both the ProgressCode and the UserForm into a CorelDraw file. Note that the 2 files, .frm & .frx, will import as 1 file.
Then you must make reference to MSComCtl.ocx that is located in Windows\System32.

The code is started by the running the procedure Run_This in the ProgressCode module.

In use it is advisable to update the progress meter not every time that data is entered or calculated.
Instead update the progess meter less frequently such as every new page or every 10 or 100 loops otherwise constant updating of the ProgressMeter slows your code.

Windows 32 & 64 Bit Versions
See the section on "Code 39 BarCodes Without The Wizard" for a working CorelDraw example of a Progress Indicator that will work for both 32 & 64 Bit versions of windows.
See ProgressMeter.xlsm for a working example in an Excel 2010 file.



Rounding Up & Down


VBA does not provide built-in functions for rounding up or down. Here some code you can use.
These functions can round numbers from 0 to 11 decimal points.



    Function RoundUp(dblValue As Double, intDecPlaces As Integer)
        Dim lngMultiple As Long

        'Move the decimal point to the right by the number of decimal points required.
        'Add a fraction less than 0.5 to ensure a number will round up.
        'Then round the answer to an long number.
        lngMultiple = (dblValue) * 10 ^ intDecPlaces + 0.5 - 10 ^ -15
        'Now move the decimal point back to its initial position.
        RoundUp = lngMultiple / 10 ^ intDecPlaces
    End Function

0.99 when rounded up to 0 decimal places will become 1
-0.99 when rounded up to 0 decimal places will become 0



    Function RoundDown(dblValue As Double, intDecPlaces As Integer)
        Dim lngMultiple As Long

        'Move the decimal point to the right by the number of decimal points required.
        'Subtract a fraction less than 0.5 to ensure a number will round down.
        'Then round the answer to an long number.
        lngMultiple = dblValue * 10 ^ intDecPlaces - 0.5 + 10 ^ -15
        'Now move the decimal point back to its initial position.
        RoundDown = lngMultiple / 10 ^ intDecPlaces
    End Function


0.99 when rounded down to 0 decimal places will become 0
-0.99 when rounded down to 0 decimal places will become -1


Determining When Another Application Has Stopped Running

When VBA has opened a command prompt window or is has run a exe file such as barcode.exe it is useful to know when the other application has closed so that the VBA code can proceed.
You can make the VBA code wait say 10 seconds but this slows the code and what if the computer is very busy and it takes 11 secods to complete the other procedure.

You can determine if the other application has stopped using a Windows API but there is some VBA code that you can use instead.
The Windows Task Manager shows all running processes. VBA accesses the Windows Task Manager using WMI.

In the example below substitute AAA.exe for your application. The code will keep looping until the other application has closed.


     'List each process running.
     Dim objWMIService As Object
     Dim objProcess As Object
     Dim colProcess As Object
     Dim strComputer As String
     Dim strList As String
     Dim OtherAPP_Exists As Boolean

     OtherAPP_Exists = True
     strComputer = "."
     'Look to see if other application is running.
     While OtherAPP_Exists = True
          Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" _
          & strComputer & "\root\cimv2")
          Set colProcess = objWMIService.ExecQuery("Select * from Win32_Process")

          OtherAPP_Exists = False
          For Each objProcess In colProcess
               strList = strList & vbCr & objProcess.Name
               'If mailsend.exe is running then you have to wait until it stops.
               If objProcess.Name = "AAA.exe" Then
                    OtherAPP_Exists = True
                    'Wait 2 seconds.
                    WAIT_TIMER (2)
               End If
           Next
               Set colProcess = Nothing
               Set objWMIService = Nothing
      Wend


    Sub WAIT_TIMER(SECS As Double)
        Dim WAITTIME As Date
        WAITTIME = Now() + SECS / (60 * 60) / 24
        While WAITTIME > Now()
        Wend
    End Sub



Windows API

This is not easy. A knowledge of C is helpful. It is powerful and difficult to master.

API's that are around today were originally written for 32 Bit Applications.
With the introduction of 64 Bit Applications VBA has been update from VBA6 to VBA7.
Note that I refer to Applications and not Operating Systems.
You cannot run a 64 Bit Application on a 32 Bit System but a 32 Bit Application will work on a 64 Bit System.
All 64 Bit Applications that fully support VBA use VBA7.
Code written for VBA6 will run on a VBA7 Application without change only if the code contains not API references.
If there is an API reference it will appear near the start of modules looking something like below.
Notice that the VBA7 version always has the word PtrSafe whilst the VBA6 version does not.
The VBA7 version variable types may also have a type known LongPtr whilst in VBA6 it it called Long.

VBA6 Version
Private Declare Function <VBA Function Name> Lib "<API Function Name>" Alias <API Function Name> _
              (<variable1> As <variable type>, _
               <variable2> As <variable type>, _
              ..............................................
              <variable> As <variable type> ) _
              As <variable type>

VBA7 Version
Private Declare PtrSafe Function <VBA Function Name> Lib "<API Function Name>" Alias <API Function Name> _
              (<variable1> As <variable type>, _
               <variable2> As <variable type>, _
              ..............................................
              <variable> As <variable type> ) _
              As <variable type>

Most API procedures require that you know the handle (a unique multiple digit number that represents every window and many window components) of the object.
The handle number will change each time the window is opened so to identify a new object you must find the new handle.
An application window always has one stable reference and that is its class name.
However several windows can have the same class name. An application window also has a Caption Name.
The Caption Name appears appears in the Taskbar. The Caption Name can vary depending upon the file opened by the application.
CorelDraw has the Caption Name "CorelDraw X6" but when it opens a saved file its Caption Name changes to CorelDraw X6 - [C:\Users\............]" or if unsaved to "CorelDraw X6 - [Untitled-1].
Calculator has a Class Name of "CalcFrame" in Win7 but it is different in WinXP. Its Caption Name is "Calculator", although you can force it to change to something else.
If you have Microsoft Visual Studio there is a tool called Spy+++ to identify the Class & Caption (Text) Name.
Joacim Andersson has created the same thing.
He has supplied it in an uncompiled form at http://www.vbforums.com/showthread.php?337952-Window-Control-Finder-Tool-(like-in-Spy-)
However here it is compiled.
I have supplied it as an .ex file as often internet security will not allow you to download an exe file.
Please add the the e on the end of the extension to make it an exe file.
The program does not install, just unzip it and run it from the exe file. In the top left corner of the dialog box that opens there is a picture of a magnifying glass in a square box.
Simply drag the magnifying glass over an object. The object will then be surrounded by a thick black line.
The Class and Text (Caption Name) and Handle numbers will be revealed.
The handle number will be shown in Hexadecimals not numbers of base 10.



Determine Windows Handles

http://www.vb-helper.com/howto_find_window_hierarchy_info.html provides a good method to determine window classes for use with api.
Here is their VB procedure. This website does not allow zip files so it is saved as .zi file. Please add the p to the extension to open it.
Here is their compiled code for 32 bit systems. A lot of systems will not allow the downloading of exe files so it is saved as .ex Please add the e at the end.
Similarly here is the same code for a 32 bit Excel system pre-2010 Office.
Here is an Excel file for Office 2010 & 2013 which can handle both 32 & 64 bit systems.
Here 64 bit program Show Handles - 64 Bit.xlsm it is a rival to to Spy++ but the result is on an Excel Spreadsheet.
It enables searching the Handles, Classes and Names. It was originally written by Mark Rowlinson but I have modified it for 64 bit.
If you have Microsoft Visual Studio you will find Spy++ in the Tools section.




Choose Font or Color Dialogs

Choose Font or Color Dialogs was written several years ago by Terry Kreft and Stephen Lebans and was written for Access 97 and the current software cannot open the file.
I have converted the Access 97 version to the Access 2003 format, Word 2013 & Excel 2013.
The Bit of the opening application must match the Bit of the file. I have not been able to get the Font Dialog to open in Excel 64 Bit.

If a file requires VBA7 then Office 2010, that was when VBA7 was introduced, or a newer version must be used.
If a file requires 32 or 64 Bit then the application opening the file must be the same number of bits not the operating system.

In the table below you can download various files for different application. The files are compressed and download as .zi files.
This web site cannot store zip files so you may have to manually complete the .zip extension.

Description File Name Comments
Access 2003 VBA6 32 Bit Choose Font and Color Dialogs VBA6 32 Bit(Access2003).mdbWill only run on a 32 Bit version of Office.
Access 2003 VBA7 32 Bit Choose Font and Color Dialogs VBA7 32 Bit(Access 2003).mdbOffice 2010 or newer and must be 32 Bit.
Access 2003 VBA7 64 Bit Choose Font and Color Dialogs VBA7 64 Bit(Access 2003).mdb Office 2010 or newer and must be 64 Bit.
CorelDraw 12 VBA6 32 Bit Choose Font and Color Dialogs VBA6 32 Bit(CorelDraw 12).cdr CorelDraw 12 or newer. A new version must be 32 Bit & use VBA6.
CorelDraw X6 VBA7 64 Bit Choose Font and Color Dialogs VBA7 64 Bit(CorelDraw X6).cdr CorelDraw X6 or newer. A new version must be 64 Bit & it will use VBA7.
Excel VBA7 32 Bit Choose Font and Color Dialogs VBA7 32 Bit(Excel).xlsm Office 2010 or newer and must be 32 Bit.
Word VBA6 32 Bit Choose Font and Color Dialogs VBA6 32 Bit(Word).doc Will only run on a 32 Bit version of Office.
Word VBA7 32 Bit Choose Font and Color Dialogs VBA7 32 Bit(Word).docm Office 2010 or newer and must be 32 Bit.
Word VBA7 64 Bit Choose Font and Color Dialogs VBA7 64 Bit(Word).docmOffice 2010 or newer and must be 64 Bit.



Date Pickers

There maybe a time when you want a user to add a date to your project. CorelDraw does not have a Date Picker. This is a form that opens showing a calender and by picking a day on the calender it is entered into your project. Similarly Microsoft Excel & Word also do not have Date Pickers although Microsoft Access since Access 2007 has an in-built Date Picker.



The following code was derived from Brendan Kidwell's web site .
Brendan wrote his code with Access 97, 2000 & 2002 in mind since they do not have Date Pickers. Brendan no longer has the time to work on his code.

Bendan has saved his file as an Access 97 file which will not open in Access 2013 so here is his file converted to Access 2002. I have saved the file as a .mdb file but you may notice that your broswer may change it to a .accdb file. You can manully change the file name back to a mdb file if you wish. You may find this access macro useful if you are progamming on a machine running Access 2013 for a machine running Access 2002. Alternately you may want an alternative to the in-built Access Date Picker. The code does not rely on API's so it will run on both 32 & 64 bit applications.

Here is Brendan's code rewritten for CorelDraw X6 & Excel 2013.

2017_07_14