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 and probably beyond.
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.
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.
Yes you can, if you must, have Access open whilst the macro runs but in a crash Acccess will still be open.
|