Solved

How to use VBA macros to print a word document

Posted on 2003-12-01
7
677 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
Comment Utility
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
Comment Utility
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
Comment Utility
I have put msgboxes in, to check if the macro gets executed or not....it doesn't.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:rtc_kiran
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

When creating Microsoft Word-based forms there may be a need to have a form field repeated throughout the whole document. For instance, with a company name, you may want this information repeated automatically throughout the document rather than man…
Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

744 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

13 Experts available now in Live!

Get 1:1 Help Now