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
Trim Marks
PhotoPaint
Miscellaneous VBA
Help
 
Variable Data From Access


The program I have written creates rows & columns of text suitable for a sheet of labels or business cards.

This data merge program reads data from an Access 2000 file but will read from Access 2003 & Access 2007 files.

Download the CorelDraw 11 file Object Placement Access.cdr and the sample Access file Access Data.mdb. CorelDraw 11 files with macros will work with CorelDraw 12 through to X6.

When ready to run the macros do not have the "Access Data.mdb" file open. You do not even have to have Access open as Access will open, as a hidden version to read the data. You must have Access installed on your computer. Also because I have used an Excel function you must also have Excel installed onto your computer.

Open the CorelDraw file and run the macro START.

Several questions will then be asked of you.

It will warn you that the Access file must be located in the same folder as the CorelDraw file. If so select OK otherwise Cancel & move the Access file.

What is the Page orientation? Portrait or Landscape. Your answer is case insensitive as your answer is converted to uppercase & then analysed. Enter for this example Portrait. Your answer is not case sensitive.

What is the distance from the left edge? This is asking for the placement of the left hand column of text in millimetres. Enter for example 72.5

What is the distance from the bottom edge? This is asking for the placement of the bottom row of text in millimetres. Enter for example 34

What is the horizontal step distance? This is the spacing of the columns in millimetres. Enter for example 89

What is the vertical step distance? This is the spacing of the rows in millimetres. Enter for example 57

How many across? This is the number of columns on a sheet. Enter for example 2

How many up? This is the number of rows on a page. Enter for example 5

What is the name of the Access data file? Enter Access Data.mdb

Now wait a few moments, there is no need to touch the computer. Access will open whilst hidden and data will be read & placed on the CorelDraw sheets.

Sometimes on slow computers a warning dialog box will appear saying "SERVER BUSY" "This action cannot be completed because the other program is busy. Choose 'Switch To' to activate the busy program and correct the problem." You are then given 2 buttons "Switch To' or "Retry". Always select Retry. The warning may reappear again but select Retry again to give your computer time to open Access in the background.

The Access file I have supplied has 39 data sets.

The hidden version of Access will close automatically. Often you will see ghost type images of the data being placed on the Corel page behind the Visual Basic Editor page. This is OK. When the macro stops look at your CorelDraw document and all the data will have been placed in position.

Notice that I have used a Master Layer to avoid having to create a new static background for each sheet. This works well when there is nothing behind the variable data. If there is color or a graphic behind the variable data & you place this in the Master Layer the Master Layer will be in front of your variable data. Unless the color or graphic is partially transparency you will not see the variable data. You instead must create or copy the static data to every page.

Look at the comments in the CorelDraw VBA procedure. Here you can modify the procedure to suit your particular problem.

In the VBA Editor under Tools select References & you will see the following references.

Access References.jpg

Here I refer to Microsoft Access 11.00 Object Library, this is because I have Access 2003 installed on this computer but you can use another version depending on what you have installed. Normally, at least on newer versions of Access you have Microsoft ActiveX Data Objects 2.1 Library installed by default. Deselected it by removing the tick in front of it & scroll down and find Microsoft DAO 3.6 Object Library and tick it and save the selection. The order that the references appear is not important to the VBA module we will run. As you can see above you will need a reference to Microsoft Excel Object Library. Do not worry about the version number. You refer to Excel even though you will read an Access file because I have used an Excel WorkSheet function found in Excel known as WorkSheetFunction.RoundDown. There is no direct RoundDown function in VBA.

Note above that I have referred to Microsoft Excel. If you want to use Excel or any function of Excel you must refer to Microsoft Excel. For instance if you want to use the Excel worksheet function RoundUp, I have in his macro, you just make a reference to Excel in the VBA Editor under Tools then select References, scroll to the reference you want and click the small square to its left. Select OK and now this reference will be moved to near the top of all the references used. In the picture above it is number six from the top.

Try not to force the macro to finish early, as the hidden instantance of Access will still be running when the macro is forced to close. This hidden version will however close when the computer shuts-down.

2015_08_06