Solved

specialtag request.

Posted on 2002-04-01
28
141 Views
Last Modified: 2013-12-24
any one now of a cfx that could read a query an place it into word.ie
I have a query that would return some fields that would be placed in a preformated letter(mailMerge document, I would like to see if the results could be transfered into word and be able to print out a page per page result.
0
Comment
Question by:jriver12
  • 13
  • 13
  • 2
28 Comments
 
LVL 14

Accepted Solution

by:
SBennett earned 75 total points
ID: 6910776
you don't need a cfx just use cfobject to open word and edit the template via cfscript. Here is an example of how to do this:

Step 1
 
 open a new word document and type the following text:
    First Name: @FirstName@
    Last Name: @LastName@

Step 2

 Save the word document as Name.doc

Step 3

 copy the code snippet below and save it as a cfm template.

----------------Code Snippet----------------------------
<!--- Try to connect to the Word application object --->
<CFTRY>
    <!--- If it exists, connect to it --->
    <CFOBJECT
        ACTION="CONNECT"
        CLASS="Word.Application"
        NAME="objWord"
        TYPE="COM">
  <CFCATCH>
    <!--- The object doesn't exist, so create it --->
    <CFOBJECT
        ACTION="CREATE"
        CLASS="Word.Application"
        NAME="objWord"
        TYPE="COM">
  </CFCATCH>
</CFTRY>

<CFSCRIPT>
     First_Name = "Fred";
     
     Last_Name = "Flinstone";
     
    /* This will open Word if running locally */
    objWord.Visible = true;
   
    /* This returns the 'Documents' collection of the Word object */
    objDoc = objWord.Documents;  
     
    /* Specify a document to open */  
    newDoc = objDoc.open("C:\name.doc");
     
     newDoc.Content["Text"] = replace(newDoc.Content["Text"], "@FirstName@", First_Name, "ALL");
    newDoc.Content["Text"] = replace(newDoc.Content["Text"], "@LastName@", Last_Name, "ALL");

   
    /* If you want to save it as a new document */
    newDoc.SaveAs("C:\" & First_Name & "_" & Last_Name & ".doc");    
   
    /* Close the document */
    newDoc.Close();
   
    /* Quit Word */
    objWord.Quit();
</CFSCRIPT>
----------------/Code Snippet----------------------------

Step 4

 Edit the Part of the code snippet that reads:

    /* Specify a document to open */  
    newDoc = objDoc.open("C:\name.doc");

to reflect the proper file path to the word document you just created.

Step 5

When you run the template it will open the word application, replace the text "@FirstName@" and "@LastName@" with the appropriate values (in this case it will be "Fred Flinstone"). It will then save the document in the format "FirstName_LastName.doc" ( in this case ("Fred_Flinstone.doc".)

This snippet is only ment to give you an idea of how to interact with Word via CFML. If would like me to give an example that is tailored to the query result and word document format you wish to use then you will have to provide those details.

Regards,
Scott
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6910778
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6910779
or what Scott posted :-)
0
 

Author Comment

by:jriver12
ID: 6910781
SCOTT,

will your solution allow for the records to be printed individualy.  

ie my query will return a number of invoices anywhere between 5 and 100.  if so do I set up the doc as a mail merge doc?
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6910802
you want this to be printed to a printer that is connected to you cfserver correct?
0
 

Author Comment

by:jriver12
ID: 6910819
scott I have 23 fields returned in the Query(View)

what would help me alot more than already provided is how to pass the results of the query from a web based page to the word application and still be able to print from word.

now if there is a way to view/and print all records returned from the browser itself that would even be better?

is that even possible?
0
 

Author Comment

by:jriver12
ID: 6910820
not necessarily connected to the server but yes to the same network and or local machine.
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6910858
to print you use the .PrintOut method here is the syntax for it:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/womthPrintOutApplicationObj.asp

so just before the close statement you should just have to add the following code and it will print to your servers default printer:

    /* Print the document */
    newDoc.PrintOut();
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6910903
to view/and print all records returned from the browser itself... the only way I can think of if you want each record to be in a separate word document is to pop open each of the word documents that get created in a new browser window, Then print them trough the browser.
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6910959
I have just added another cftry/cfcatch blod to the code snippet so that if an error occurs writing to the printer(which just happened to me), the word object doesn't get left open, thereby locking the files and preventing you from effectively running the script again until you restart the computer.

Here it is:


----------------Code Snippet----------------------------
<!--- Try to connect to the Word application object --->
<CFTRY>
    <!--- If it exists, connect to it --->
    <CFOBJECT
        ACTION="CONNECT"
        CLASS="Word.Application"
        NAME="objWord"
        TYPE="COM">
  <CFCATCH>
    <!--- The object doesn't exist, so create it --->
    <CFOBJECT
        ACTION="CREATE"
        CLASS="Word.Application"
        NAME="objWord"
        TYPE="COM">
  </CFCATCH>
</CFTRY>
<CFTRY>
<CFSCRIPT>
     First_Name = "Fred";
     
     Last_Name = "Flinstone";
     
    /* This will open Word if running locally */
    objWord.Visible = true;
   
    /* This returns the 'Documents' collection of the Word object */
    objDoc = objWord.Documents;  
     
    /* Specify a document to open */  
    newDoc = objDoc.open("C:\Name.doc");
     
     newDoc.Content["Text"] = replace(newDoc.Content["Text"], "@FirstName@", First_Name, "ALL");
    newDoc.Content["Text"] = replace(newDoc.Content["Text"], "@LastName@", Last_Name, "ALL");

   
     
    /* If you want to save it as a new document */
    newDoc.SaveAs("C:\" & First_Name & "_" & Last_Name & ".doc");    
   
     /* Print the document*/
    newDoc.PrintOut();
     
    /* Close the document */
    newDoc.Close();
   
    /* Quit Word */
    objWord.Quit();
</CFSCRIPT>
<CFCATCH>
     <b>Object Error Caught</b><br>
     <CFSCRIPT>
     /* Quit Word */
    objWord.Quit();
     </CFSCRIPT>
     <cfrethrow>
</CFCATCH>
</CFTRY>
----------------/Code Snippet----------------------------
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6910965
"blod" in first sentance of last comment should be "block"
0
 

Author Comment

by:jriver12
ID: 6910968
scott,
just to make sure that I understand
the above code will insert all record sets into a word document, depending on how many there is. so if I had 100 records returned it would create 100 .docs in child windows in the browser?

how about just opening them in an instance of word.

I haven't gotten the code to work yet.

I keep getting acess denied/cant find errors I will keep trying.  

I beleive that I am getting lost at the point of where receive and change @firstname@ @lastname@.

now within the <script> tags can I place or place the <script>within <cfoutput> so when the code states that firstname= firstname; i could try firstname=#firstname#
where #firstname# could be a result from the query?
0
 

Author Comment

by:jriver12
ID: 6910975
scott,
just to make sure that I understand
the above code will insert all record sets into a word document, depending on how many there is. so if I had 100 records returned it would create 100 .docs in child windows in the browser?

how about just opening them in an instance of word.

I haven't gotten the code to work yet.

I keep getting acess denied/cant find errors I will keep trying.  

I beleive that I am getting lost at the point of where receive and change @firstname@ @lastname@.

now within the <script> tags can I place or place the <script>within <cfoutput> so when the code states that firstname= firstname; i could try firstname=#firstname#
where #firstname# could be a result from the query?

is there  a special configuration for this code?

Just because I keep getting denied access errors.
0
 

Author Comment

by:jriver12
ID: 6910977
scott is there a email I could send my doc to so you may get a better picture?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jriver12
ID: 6910988
I am realy disliking this 500 error
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6910990
scott@coldfusionguy.com
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6910994
you have word installed on the server correct? wich version?
0
 

Author Comment

by:jriver12
ID: 6911008
as for now it is just running on my machine for development purposes

I am running the following:

os:  XP Pro
word: officeXP
cfserver: 4.5
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6911019
hmmm never tried this on xp before just 2000 and below. I will look at the msdn documentation to see if there are differences.
0
 

Author Comment

by:jriver12
ID: 6911027
just sent that file to you
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6911153
I sent another version of the code I posted earlier in response to your email.

To fix the problem with the formatting, you may wish to first create an html file and use the idea described here:

http://www.cfcomet.com/cfcomet/Word/index.cfm?ArticleID=F0108E11-5FCE-400D-9D87CBB5BA2229C6
0
 

Author Comment

by:jriver12
ID: 6913148
scott,
Im getting a print error at the rethrow (line 82),
I assume it is with the newobj.activeprinter.
I am using a network printer
I am setting it as "TRAIN-PCL on Myserver" and have also tried "\\myserver\Train-pcl.  any suggestions on what that print error may be?
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6913619
you have to set it in this format:

    newApp.ActivePrinter = "TRAIN-PCL on \\Myserver";
   
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6913643
The following is the format for a specifying a network printer:

    newApp.ActivePrinter = "HP DeskJet 820C Series v8.5 on \\Machine\Share";
   

And this is the format for a specifying a Local printer:

    newApp.ActivePrinter = "HP DeskJet 820C Series v8.5 on LPT1:";
   
0
 

Author Comment

by:jriver12
ID: 6913832
all in all I got it to work :)
i had to set the print path to be "\\myserver\printername\on portname.

one more thing before I release this:  
on the page that you sent to me you have the following:

<cfset MyQuery = querynew("bt_ContractDate, ehxNumb, showlocation, Showdates, exconame, booth1, dim1, dim2, tsft, textver, totalCost, paymentrules, CONAME")>
<cfset QueryAddRow(MyQuery, 1)>
<cfset QuerySetCell(MyQuery, "bt_ContractDate", "Variable Value" , 1 )>
<cfset QuerySetCell(MyQuery, "ehxNumb", "Variable Value" , 1 )>

etc...

could you explain what the is if my understanding is incorrect.

I understand it to be the setting or variables so you could run the test on the page.(am I understanding)  

however,

if I was to use predefined query such as

select bt_ContractDate, ehxNumb, showlocation, Showdates, exconame, booth1, dim1, dim2, tsft, textver, totalCost, paymentrules, CONAME
from my table
would that replace the

cfset MyQuery = querynew("bt_ContractDate, ehxNumb, showlocation, Showdates, exconame, booth1, dim1, dim2, tsft, textver, totalCost, paymentrules, CONAME")>

if that is true then I could replace the

<cfset QuerySetCell(MyQuery, "bt_ContractDate", "Variable Value" , 1 )>

with <cfoutput>#myvariable#</cfoutput>


am I understanding this?
0
 

Author Comment

by:jriver12
ID: 6915373
thanks for pointing me in the right direction scott.

I used what you said and it worked great, however the formating issue still came in to the picture so my solution is this.

I have made a mail merge doc that will be opened by the code you suggested and to keep the formating I have placed the whole entire doc in a table.  

works great thanks for the time and the expertise.


J
0
 
LVL 14

Expert Comment

by:SBennett
ID: 6915493
In the first 15 lines of code that you were asking about, I was just using the query functions to  build a query result set identical to the one you would get from the following cfquery:

<cfquery name="MyQuery" Datasource="MyDatasource">
select bt_ContractDate, ehxNumb, showlocation, Showdates, exconame, booth1, dim1, dim2, tsft, textver,
totalCost, paymentrules, CONAME
from my table
</cfquery>

If you are not familiar with those functions you should consider studying up on them, They are very useful for helping people on ee who need help with procesing data from queries they made from their database, because you don't have to go and build any database tables and go through all those extra steps to come up with code to help them. in some simple cases I will just write the code and not test it, but for more complicated code, I like to test it and make sure I didn't make any errors before posting it on ee.

I'm glad I could help you get this working.

-Scott
0
 

Author Comment

by:jriver12
ID: 6915755
thanks again.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now