LinkBack URL
About LinkBacks
I have not been able to write code that will open an existing word document from within Excel. I can open a new document, but that doesn't help!
Can anyone please help me?
John
I have not been able to write code that will open an existing word document from within Excel. I can open a new document, but that doesn't help!
Can anyone please help me?
John
Try.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
I have now managed to open my existing word document. I am still in trouble though because I cannot make code which refers to that document 9but is in Excel) work.
I think I need a link.
The code I am using is
The MakeLabels call at the end of the first procedure doesn't work of course.Please Login or Register to view this content.
Is this the right approach? Can someone help me here.
John
Bakerman2,
Thanks for your help. I am sorry that my additional post and yours have crossed.
I think that your code and mine are much the same, but yours is neater so I will use it.
Can you help on my follow up post ? I am still very much stuck here. Some how I need to tell the MakeMerge sub which is the active document, and I have not yet found a way od doing that.
John
Hi John,
Do you have a reference set to the Word library? If you do not, you cannot make use of Word's native constants without declaring them explicitly. I would guess you do not since your code is late bound?
There are several other issues with your object references that I will endeavour to fix shortly.
Don
Please remember to mark your thread 'Solved' when appropriate.
I admit I have not tested the following but I believe it should fix the syntax issues
Please Login or Register to view this content.
xlNitWit
I can't thank you enough for your efforts. Your code worked perfectly as written. I did not have a reference to Word and did not understand the effect this would have.
There is one problem left. The Excel data source is in the form of a dynamic named range which is a list of a given number of barcodes. (The whole exercise has been to enable packers to first identify the required code and then print out the right number of labels with this code.) . Both my manual mail merge and the code print out the requested barcode labels and then continue to print out empty labels for several sheets. If I look at Edit recipient List I find that this number of blank rows are checked as well as the required ones.
It occurs to me that I could cure this by modifying SQLstatement:= "SELECT * FROM 'BARCODE_Table' where this is the named range. Is this a goer? I,m going to try anyway.
John
In my experience dynamic ranges do not work with queries, although this may not be true if the container workbook is already opened.
You are right it doesn't work. Would it be better if I changed the source to a fixed range? Otherwise I can see mountains of label stock on the floor at the end of the day.
John
You can use a WHERE clause to only return records where a field is not blank
Please Login or Register to view this content.
I am not very familiar with SQL. Should the field name "CODE" be written as ['CODE'] or without the brackets or as "CODE"
I have tried the first variation and it was ignored.
Could the source of the problem in fact be that:-
Unfortunately wdDefaultLastRecord is not a constant but depends on the number of labels I need to print. I have tried to vary this by passing through the number of labels and calculating the number of sheets of 6 labels needed.Please Login or Register to view this content.
If I try and use this number in the Const definition I am told that a constant is required. Is there a way round this.?
It would be written as [CODE] without the quote marks.
Anything beginning with 'wd' is a built in constant from the Word application. You should not try to change that- it simply means the merge should use all the records available.
Since you already have a document for the mailmerge, why are you using this code:
Whatever fields you need should already be there in your document, all appropriately formatted. Moreover, since you're apparently trying to do a label merge, simply adding this field to the document is unlikely to produce the desired results - at best your field would be added to a single label and the rest of the sheet would be wasted.Please Login or Register to view this content.
The Constant declarations are not the problem, neither was the lack of a reference to Word. Your code is written for use with late binding, which is why the reference to Word isn't required but the Constant declarations are. Declaring Constants in this way allows your code to be read as if it were using early binding (for which a reference to Word would be required). Using late binding can be an advantage if your code is to be used with different Office versions.
If you want to print a particular range of labels, you need code to solicit the start and/or end numbers. For example:
Please Login or Register to view this content.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks