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 Excel

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 Excel file.

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

When ready to run the macros do not have the "Excel Data.xls" file open. You do not even have to have Excel open as Excel will open, as a hidden version to read the data. You however, must 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 Excel file must be located in the same folder as the CorelDraw file. If so select OK otherwise Cancel & move the Excel 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 Excel data file? Enter Excel Data.xls

Now wait a few moments, there is no need to touch the computer. Excel 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 either Excel in the background.

The Excel file I have supplied has 39 data sets. Excel will read a column up to the maximum number of rows in Excel which is 65,536 provided the array size is set large enough. See the note at the start of the Object Placement Excel VBA procedure.

The hidden version of Excel 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.

For Object Placement Excel you will notice that if you go to Tools - References you will get the following.

Excel References.jpg

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. If you want to use the Excel, as I have, 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 4 from the top.

The order that the references appears is not important to the VBA module we will run. In this example I use version Microsoft Excel 11.0 Object Library of Excel. You can use what every version of Excel that is on you machine. If you do not have Excel 11 installed there will be the word MISSING beside the Excel reference. Deselect the missing Excel reference by removing the tick and then tick the version of Microsoft Excel Object Library that you do have. The version of Microsoft Excel Object Library that you have installed will be well down the alphbetical list. After you have ticked your version select OK to close the reference dialog box. Naturally your selection will not be permanantly saved until you save the CorelDraw file.

Try not to force the macro to finish early, as the hidden instantance of Excel will still be running when the macro is forced to close. This hidden version will however close when the computer shuts-down. In the past the procedure would open the Excel worksheet and read the data, place it on the CorelDraw document & then read another row of data from the Excel spreadsheet. This was not time efficient. The procedure now reads all the data file into an array early in the macro and then close the hidden data application, before placing the data on the Corel document from the array. This speeds up the program, particularly on medium to big databases & slow computers. It also allows more opportunity to stop the macro without leaving a hidden version of Excel or Access still running.

2015_08_06