Solved

How to use VBA macros to print a word document

Posted on 2003-12-01
7
681 Views
Last Modified: 2008-02-01
Hi,
I've designed a page in word and saved it as an asp page (word.asp). I would like to have a link from another asp page to automatically open the asp page(word.asp) in word, print a copy of it and then close the document again. I'm building this for an intranet application, so sending word documents which contain macros is not an issue. Where I'm stuck is basically accessing the active document from a vba macro as well as including the macro in the document when it is sent to word

the asp page is sent to word like this..
 Response.ContentType = "application/msword";
 Response.AddHeader("content-disposition","attachment; filename=Orig.doc");

the macro code so far...

Private Sub Document_Open()
Application.Visible = False
ActiveWindow.PrintOut
ActiveDocument.Close
End Sub

I don't think the macro gets included when sent to word although I had it signed and enabled signed macros in word. any help would be greatly appreciated
0
Comment
Question by:rtc_kiran
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:Steiner
ID: 9850891
I don't know that much about asp, but one other thought:
If you use Application.Visible = False and don't close Word using code, what are you doing with all the open instances of Word??

Have you tried putting a msgbox in your code to see whether it get's executed at all? Maybe putting it into Private Sub Document_New might help if it's a template.
0
 

Author Comment

by:rtc_kiran
ID: 9851110
Hi,
I the above macro is just example code, I check to see if any other documents are open, if they are only my doc gets closed, else I shut down word

these are not the issues however, what I did was

1. Create a document in word ( to make it look like I wanted it to)

2. Write a vba macro by going to Tools->Macros, adding my code and saving the macro

3. Save the document as type web page (as an asp page by saying  "test.asp" for example)

4. Open the asp page, stick my code in, add the correct content type and the content.addheader

Now I have another asp page with a button, when the user clicks the button he/she gets prompted to open or save the document. since I am working on a intranet application for a small company EVERYBODY opens the doc instead of saving it :)
What I need to do is after the user has clicked on open....

1. Open the document in word (works fine)
2. Automatically print it off (doesn't work)
3. Automatically close the active document (doensn't work)

can this be done?
0
 

Author Comment

by:rtc_kiran
ID: 9851124
I have put msgboxes in, to check if the macro gets executed or not....it doesn't.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:rtc_kiran
ID: 9851501
If I take out the Response.AddHeader("content-disposition","attachment; filename=SAMENAMEASASPFILE.doc");, it reckonginzes that there are macros in the document (it prompts me to enable or disable macros), but then it starts a file download box and stays there (gets stuck?), usually it comes up with an error message if it can't download the file doesn't it? not in this case..I'm completely lost, can u specify multiple attachments in Response.AddHeader, so I could include the xml file word created?
0
 
LVL 11

Expert Comment

by:Steiner
ID: 9856256
Sorry, but that goes too far beyond my knowledge.
But somehow I doubt Word saves the macros when you save your file as web page (because macros are for Word only, and a Webpage you don't necessarily open in Word).
In don't know if that's just Word97 (what I'm using here), but if I close the doc and open it again, there is no VBA-Code. I can add some, close it and open it again --> nothing.
Why do you want to save the doc as asp if the user has to open it using Word anyway?
0
 

Author Comment

by:rtc_kiran
ID: 9857721
Hi,
I think word saves the macros in a folder with the webpage or more specificallly, I had saved the vba code on the server first for example in a macro called printout. Word then saves a link to this macro in a separate folder, I think in an xml document.
The reason why my macro isn't working when sending the asp page to word is because the document gets downloaded into a temporary internet folder first before it gets openend in word. Hence the link to the macro is no longer valid, because the link to the xml document is lost.
I need to somehow pull some records from a database. Now my SQL query needs to accept parameters, that's why I saved the word document as an asp page. I don't know how to run parameter queries from word directly. If this is possible that'd be even better...any ideas?
0
 
LVL 11

Accepted Solution

by:
Steiner earned 500 total points
ID: 9864814
Sound like you have a newer Word than I, because Word 97 can't do anything with XML. So I don't know anything about that.

About the SQL-Query: it should be possible to program the database connection completely in Word-VBA using the "Microsoft ActiveX Data Objects Library" and the following code as an example:


Sub test()
   Dim con As Connection, cmd As Command, rs As Recordset
   
   Set con = New Connection
   
   With con
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Programme\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False" 'Create the connection to your DB
      .Open
   End With
   
   Set cmd = New Command
   With cmd
      .ActiveConnection = con
      .ActiveConnection.CursorLocation = adUseClient
      .CommandType = adCmdText
      .CommandText = "SELECT * from Artikel where einzelpreis<20" 'You can create an SQL statement here, using values from textboxes or variables for parameters
   End With
   
   Set rs = cmd.Execute
   'You can use the recordset from here
   Debug.Print rs.RecordCound  

   Set rs = Nothing
   Set cmd = Nothing
   con.Close
   Set con = Nothing
End Sub

You could then use the data from the recordset rs for your document, e.g. inserting it through VBA.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

726 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