Solved

How to use VBA macros to print a word document

Posted on 2003-12-01
7
679 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Like many others, we try and discourage users from printing documents unnecessarily and instead send or share them electronically. However, this doesn't always work and documents are still printed. With this simple solution, if the user tries to …
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

773 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