PDA

View Full Version : Solved: Excel VBA invokes Word



jwise
04-04-2007, 08:25 AM
Hi,

I'm a newbie, so excuse my ignorance... It was very difficult for me to get here! I write mainframe assembler... OO VBA is just a little different!

The objective is to use a VBA macro in Excel to kick off a Word document, and to change some words (placeholders) in this document to values that come from the spreadsheet.

The included code only attempts to make 1 change, and it dies with an error 449 from VBA. Here is the code:


Private Sub WordTest()
Dim rcMsg As Integer
Dim tCell As Long

Dim wrdApp As Word.Application
On Error GoTo errHandler

rcMsg = MsgBox("WordTest 2.3 has been entered")

Set wrdApp = New Word.Application

' wrdApp.Visible = True

rcMsg = MsgBox("WordTestBefore Open")

wrdApp.Documents.Open Filename:="c:\chuck\Recap.doc"

rcMsg = MsgBox("WordTest Before Selection.Find.Clear...!")

wrdApp.Selection.Find.ClearFormatting

rcMsg = MsgBox("WordTest Before FIND.Repl!")

wrdApp.Selection.Find.Replacement.ClearFormatting

rcMsg = MsgBox("WordTest Before WITH!")

With wrdApp.Selection.Find
.Text = "AAAAAQ01"
.Replacement.Text = "Charleston Square"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

rcMsg = MsgBox("WordTest Before Selection.Find.Execute!")

wrdApp.Selection.Find.Execute

rcMsg = MsgBox("WordTest Before With wrdApp.Selection!")

With wrdApp.Selection
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseStart
Else
.Collapse Direction:=wdCollapseEnd
End If
.Find.Execute Replace:=wdReplaceOne
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseEnd
Else
.Collapse Direction:=wdCollapseStart
End If
.Find.Execute
End With

rcMsg = MsgBox("WordTest... Before Prt!")

wrdApp.Application.PrintOut Filename:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0

rcMsg = MsgBox("Wordtest- Before Quit")

wrdApp.Quit savechanges:=False

rcMsg = MsgBox("Wordtst- Before wrdApp Nothing")

Set wrdApp = Nothing

tCell = Cells(1, 1)

rcMsg = MsgBox(tCell) 'Another test

rcMsg = MsgBox("WordTest is ending.")

GoTo endIt

errHandler:
rcMsg = MsgBox("The error handler was entered!")

wrdApp.Quit savechanges:=False

Set wrdApp = Nothing

rcMsg = MsgBox("The error handler issued Quit!")


endIt:
End Sub

Edit Lucas: VBA tags added to code
-------------------------------------------

The program dies at:

wrdApp.Selection.Find.Replacement.ClearFormatting

The message "WordTest Before FIND.Repl!" is the last message displayed before going into the error handler.

The word VBA came from recording a macro under Word to do the first change (Change "AAAAAQ01" to "Charleston Square"). Then I copied this to my XL macro and added the wrdApp to the object methods. This was the result of another problem, and I think this means the methods could be Word or XL and "wrdApp" makes the decision of which method to use. I may be misusing "method" as my OO terminology is not very good.

It just seems to me that this has to be something pretty simple. If I remove all the "change" code, Word happily prints my document.

The long names "AAAAAQ01" were chosen to make the spacing easier to "guess" on the Word document. Once I get this to work, I'll probably set this to just "Q01". I tried to use "bookmarks" in ".dot" file to no avail in Word. Perhaps my approach could be improved. The vast majority of the 15 changes to be made to the document is numbers that XL is calculating.
Only one change is made in this example.

TIA

JonPeltier
04-05-2007, 05:31 AM
I think if you use the Word selection object, you have to make the Word application visible first. I generally try to work with the Word range object instead. There are MSDN articles that describe how to do this, but I am having trouble finding them, because Microsoft changes the MSDN web site structure about once a month. Here are articles written for Office 2007:

Working with the Selection Object
http://msdn2.microsoft.com/en-us/library/bb221280.aspx

Working with Range Objects
http://msdn2.microsoft.com/en-us/library/bb221274.aspx

Here are the corresponding articles for Office 2000:

Working with the Selection Object
http://msdn2.microsoft.com/en-us/library/aa264168(office.10).aspx

Working with Range Objects
http://msdn2.microsoft.com/en-us/library/aa264167(office.10).aspx

(I know the URLs say office.10, but the articles are in the office 9 library).

jwise
04-05-2007, 06:09 AM
Thank you for addressing my problem. I appreciate your time.

It seems strange to me that the "macro recorder" would create code that would not work from outside its environment. Perhaps my problem was not knowing this fact in the first place.

I originally addressed this problem using "bookmarks", but I was unable to figure out how to get rid of the bookmarks or the command to "change" the bookmark to text. From what I read in the MS documents, you can set a range and then replace text.

Ideally, the range sounds like a good way to address this problem. I would insert the property name and date, and then the dollar amounts. None of these items are always the same length, so a true byte offset is altered by the insertion of the text (for all but the first inserted text). I am hoping the Range somehow accomodates this fact.

Thanks again for your help.

JonPeltier
04-05-2007, 06:20 AM
The macro recorder records what you do, which isn't always the best way to code a solution, but it is a start. The macro recorder thus works on the selection in whatever application you're using, and it doesn't address the full qualification of objects you should always use.

There must be MSDN articles about bookmarks. Also check out the Word MVP FAQ site:
http://word.mvps.org/FAQs/MacrosVBA/index.htm

I use bookmarks, and generally reference the BookMark.Range. When you change the bookmark's text, the bookmark is destroyed, but the range remains, and you can recreate the bookmark so it includes the new range.

Be prepared for some frustration. I've always thought that the Word VBA Object Model was designed by an escapee from a mental institution.

lucas
04-05-2007, 06:48 AM
jwise, if you select your code when posting and hit the button marked VBA, it will format it for easier reading. I have edited your first post in this thread.

Norie
04-05-2007, 06:50 AM
Why not just use Mail Merge?

JonPeltier
04-05-2007, 06:57 AM
Mail Merge is good for what it's good for, but if you're populating a whole set of locations of a Word document with bits of information from Excel, using VBA to manipulate Word bookmarks and Excel ranges is far more flexible and capable.

jwise
04-05-2007, 07:22 AM
Thanks again to Jon and others who have replied to my question. I appreciate your time, patience, and expertise.

I examined Jon's site and was very impressed. Thanks Jon for the reference and for the MS MVP site reference. I'm learning...

I think I can assign labels to bookmarks. Let's assume there are ten bookmarks with names Q01, Q02, ... Q10. These names are defined in Word when the document is initially produced. The task in XL would be to "find" Q01, and insert the appropriate value from a cell. This would be done in a loop as all ten bookmarks would be replaced every time. The document is then printed, XL moves to the next row, and populates the Word document again. The number of Word documents produced is the number of data rows in the XL worksheet. The same document is used every time.

TIA

From MSDN I found:
Dim rngDoc as Range
Set rngDoc = ActiveDocument.Range(Start:=0, End:=10)

So how do I set the Range to the Bookmark?

TIA

Norie
04-05-2007, 08:41 AM
Jon

Of course using VBA is more flexible, but it wasn't 100% clear what the OP was doing.

I just thought I'd through in the idea of Mail Merge as I don't see any point in re-inventing the wheel when you have built-in functionality that might achieve your objective.

Perhaps in this case VBA is the way to go, but I just thought I'd make the suggestion.:)

Edit: I've just reread the OP's last post.

Sounds to me like Mail Merge could be used.

Charlize
04-05-2007, 01:39 PM
A little example of filling in word (starten from excel) at specified places with values of cells in excel. Place the doc in a certain directory and specify this in the code. Have put some comment with it. This code will basically search for the text "bookmark1", "bookmark2" and "bookmark3" and will insert the textvalues of the cells at those places.

Hope it helps a little.

Charlize

jwise
04-06-2007, 06:42 AM
Thanks again to all responders.

I found some example code on a website that is very similar to what I'm doing..., i.e. it is an XL/VBA macro that invokes Word to modify a template. The example code is for a letter to be sent to job candidate for an interview.

This means I'll go back to bookmarks and "with ActiveDocument", for example


With ActiveDocument
.Bookmarks("Q01").Range.Text = "Charleston Square"
.Bookmarks ...

End With

Edit: Please use the VBA Tags........
We'll see how this works. My concern is now the proper OOP requirements.

I will update later. Thanks again to all who have contributed. I was unable to include the website in this post because of some rule.

lucas
04-06-2007, 06:53 AM
Hi jwise,
I think the problem your having with posting links will be gone after you have 5 posts in the forum......

Norie
04-06-2007, 08:14 AM
What 'proper OOP requirements' do you mean?

You do realise that VBA isn't actually considered to be a 'true' OOP programming language by some folks.:)

JonPeltier
04-06-2007, 09:00 AM
... and VBA developers aren't real developers. Big deal.

JonPeltier
04-06-2007, 09:08 AM
With ActiveDocument
.Bookmarks("Q01").Range.Text = "Charleston Square"
.Bookmarks ...

End With



Set a range to the bookmark so you can recreate it, because the bookmark is deleted when you replace its text.

Dim rBkmk As Object ' or Word.Range
With ActiveDocument
Set rBkmk = .Bookmarks("Q01").Range
rBkmk.Text = "Charleston Square"
.Bookmarks.Add rBkmk, "Q01"
End With

Better yet to have an array of bookmarks, and populate them from an array of Excel sources.

Sometimes I'll keep a list in an Excel worksheet, Excel range in column 1, Work bookmark name in column 2. Or I'll have special bookmarks in Word which have a prefix "xl_", and I'll use an Excel name that matches, so the Word bookmark xl_Bkmk1 will be populated by the Excel range wd_Bkmk1; this way I can use either of the following to find them:

For Each Bkmk In MyWdDoc.Bookmarks
If Left$(Bkmk.Name, 3) = "xl_" Then

For Each nm In MyWkbk.Names
If Left$(nm.Name, 3) = "wd_" Then

Norie
04-06-2007, 09:12 AM
Jon

I'm just wondering what the OP means and also mentioning some people's opinion.

I'm in no way trying to offend anyone.:confused:

JonPeltier
04-06-2007, 09:19 AM
I didn't know what that meant either (OOP requirements??), so I was ignoring it.

The thing with VBA is that if you keep in mind the OOP principles, even if the language doesn't really implement them all so well, it can make your own programming better.

Norie
04-06-2007, 09:35 AM
Jon

Totally agree.:)

I've got a feeling, though I may be wrong, that the OP is concerned about the automation aspect of his project.

I suppose we'll just need to wait till they post back.:)

lucas
04-06-2007, 09:42 AM
Set a range to the bookmark so you can recreate it, because the bookmark is deleted when you replace its text.

Hi Jon,
I've been tinkering with Word a little while and I find that if your populating a template to send out that it doesn't matter if the bookmarks are destroyed in the process.....but I have had no need to go back and find them, etc. but in a case like that would it matter if they are gone after you do the replacement?

I'm assuming a template from this in the op's first post...

The objective is to use a VBA macro in Excel to kick off a Word document, and to change some words (placeholders) in this document to values that come from the spreadsheet.


For that matter Charlize's find/replace would work too if ....if they are using a template....am I missing something?

jwise
04-06-2007, 03:09 PM
Thanks again to all who posted. You are educating me.

By "OOP requirements", I meant those somewhat strange statements like
"Set XX as Object"
etc. The strange is relative to someone who comes from a strictly procedural background (and mainframe at that). Sorry, but it just doesn't look like code. "N = N + 1" looks like code to an old asm programmer. There is nothing political in this at all. Perhaps I should be asking for a good OOP text reference. All of this was designed to give my advisors a clue as to my lack of OOP understanding.

The "destroyed bookmark" could be a problem since I will essentially be printing one document for each row in my spreadsheet. I am assuming this means that I must recreate the bookmark after I insert the cell (or text) in the document. There are around ten bookmarks, so what I hearing is insert text 1 at bookmark 1, put bookmark back, go to remaining 9 in sequence, then print the page.

The other thought was just to "end nosave" Word. It just seemed that restarting Word for each row is a lot of overhead. This may not be a consideration in this realm. In the mainframe realm, I processed millions of records and this could be a big problem. Today there will be around 40 rows in this spreadsheet, and if it takes two minutes, that's no big deal. I am just accustomed to thinking in the old way... this old dog is returning to his ______.

Thanks again.

JonPeltier
04-06-2007, 03:20 PM
A good VBA reference would be Excel Power Programming by John Walkenbach (http://www.amazon.com/exec/obidos/redirect?path=ASIN/0764540726&link_code=as2&camp=1789&tag=peltiertechni-20&creative=9325).

Using the mail merge interaction of Word and Excel is one way to do what you want, making multiple copies of a document, one per row if data in Excel. See
http://word.mvps.org/FAQS/MailMerge/CreateAMailMerge.htm
http://www.mvps.org/dmcritchie/excel/mailmerg.htm (and links at the end of the page)
http://office.microsoft.com/en-us/excel/HP052037601033.aspx
http://j-walk.com/ss/excel/tips/tip92.htm

You could use the bit of code I provided before to allow reuse of the bookmarks, or you could just close the Word document and reopen it to get a fresh copy. Or make a template, and create a new file based on the template for each row of the Excel worksheet.

Charlize
04-07-2007, 11:56 AM
The "destroyed bookmark" could be a problem since I will essentially be printing one document for each row in my spreadsheet. I am assuming this means that I must recreate the bookmark after I insert the cell (or text) in the document. There are around ten bookmarks, so what I hearing is insert text 1 at bookmark 1, put bookmark back, go to remaining 9 in sequence, then print the page.

The other thought was just to "end nosave" Word. It just seemed that restarting Word for each row is a lot of overhead. This may not be a consideration in this realm. In the mainframe realm, I processed millions of records and this could be a big problem. Today there will be around 40 rows in this spreadsheet, and if it takes two minutes, that's no big deal. I am just accustomed to thinking in the old way... this old dog is returning to his ______.
When you save your template to a different name (xxx-temp.doc) and close the doc, the original doc will remain intact because you have saved it to another name (so there is no need to restore the bookmarktext --- the way I used them ---). Print this and use kill command to delete file and begin with the new row. So for every row that needs to be done, open, fill in, save, print, close, kill.

When you don't quit the Word object unitl the last row ??? What is the overhead. I think printing will take more time then processing those rows.

Charlize

jwise
04-09-2007, 06:19 AM
Thanks again to all who have posted in response to my questions.

I have had an interruption in this project, and it will be a couple of days before I get back to this.

I have always been a collector of reference texts, and I now have "Excel VBA for Dummies" and "Excel 2003 Power Programming with VBA (J. Walkenbach), "Professional Excel Development" (Bullen, Bovey, and Green), and "Excel Programming 2nd Edition" (Jinjer Simon). I like and have used all these texts. I do not plan on reading all of any one book; I bought them for reference purposes. I may decide to read all of the "Dummies" book because that is certainly my level. I have two more books on order by authors Linda Johnson and Juan Pablo Gonzalez (et al) which address Excel and Office integration more directly. I also have another Excel VBA text on order by Richard Shepherd which supposedly addresses the Excel object model in depth.

In my "interrupt project" I am able to utilize much of the code I have written for this project. I was struggling with the technique to determine the "highlighted" row until I found the answer quite by accident in the Simon text. Is there a good reference text for finding this kind of information? For example, I need to know the last row that has any data in it.

TIA

JonPeltier
04-09-2007, 06:54 AM
There isn't necessarily much sense in collecting more and more books on the same topic. For in-depth object model reference I find it better to use online help, lots of trial and error, and Google. The Excel 200X VBA Developer's Handbooks are pretty good for this as well, except for the 2003 version, which was cobbled togather by a different author who didn't really get it.

I haven't seen the book by Linda and Juan Pablo; I have a different book by Simon which I'm not much impressed with.

To find specific answers, try the book's index, then try Google.

Google {excel last row}:
Results 1 - 10 of about 1,220,000 for excel last row. (0.23 seconds)

Norie
04-09-2007, 07:09 AM
One example of getting the last row.

LastRow = ThisWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Note this is only one example and there are various other methods which you'lll probably find if you try a search like Jon suggests.

Bob Phillips
04-10-2007, 02:12 AM
Thanks again to all who posted. You are educating me.

By "OOP requirements", I meant those somewhat strange statements like
"Set XX as Object"
etc. The strange is relative to someone who comes from a strictly procedural background (and mainframe at that). Sorry, but it just doesn't look like code. "N = N + 1" looks like code to an old asm programmer. There is nothing political in this at all. Perhaps I should be asking for a good OOP text reference. All of this was designed to give my advisors a clue as to my lack of OOP understanding.

I don't think you need to worry to much about OOP in this context, but more about the language syntax.



Set xx = New Object


is just in loading a variable, but in this case an object variable as against a numeric or string variable. It really is not that much different to



N = 17


which originally we would have written as



Let N = 17


the Let has just fallen intro dis-use.


The "destroyed bookmark" could be a problem since I will essentially be printing one document for each row in my spreadsheet. I am assuming this means that I must recreate the bookmark after I insert the cell (or text) in the document. There are around ten bookmarks, so what I hearing is insert text 1 at bookmark 1, put bookmark back, go to remaining 9 in sequence, then print the page.

You could use a template, and just re-open that.


The other thought was just to "end nosave" Word. It just seemed that restarting Word for each row is a lot of overhead. This may not be a consideration in this realm. In the mainframe realm, I processed millions of records and this could be a big problem. Today there will be around 40 rows in this spreadsheet, and if it takes two minutes, that's no big deal. I am just accustomed to thinking in the old way... this old dog is returning to his ______.

You should always try to keep the code effeicient, even on small jobs. Practising such techniques always makes it easier to apply when really necessary.

jwise
04-11-2007, 09:10 PM
Thanks again to all who have posted. You have been most helpful. I will do additional testing and get back with you. I have completed the other task. Now I have to enter 41 x 8 x 14 numbers into my "DATA" worksheet. Then I'll take on Word...

jwise
04-18-2007, 12:19 PM
I'm back from my other task. Since I understand the objects a little better, I am getting to Word. The document was basically designed with blanks in it initially. These blanks were filled-in by the user once the calculations were made.

The purpose of this project was to eliminate transcribing these numbers from Excel to the paper form, i.e. Excel would invoke Word and insert the numbers in the document and print it.

The problem I'm encountering is that the inserted numbers do not have their Excel formatting, e.g. "12345" instead of "$12,345". How can I get this formatting into the fields?

Second question: How do I leave a "hole" in the Word document for the data? When I insert this data, the print lines are extended and wrapped, thus the single page form spills into the next page. Perhaps this is an inappropriate question.

TIA

jwise
04-23-2007, 11:46 AM
Thanks to several responders, I was able to get the XL to Word print working... sort of. For some strange reason, it only works when I run it in the debugger. If I invoke it using "Alt-F8", the macro appears to work, but the Word page never prints. If I single-step it through the debugger,
it works as advertised. This is XL2000 under XP Pro SP2 with 256mb memory.

I am attaching the workbook, with the macro source and test data. Since there is a one attachment limit, I will post again with the Word document template. Please remember to update the code to point to the template.

The basic problem is that I get no output from Word if I just run the macro. If I run the macro in the debugger using F8 "single step", it works as designed. To use this macro, you must SELECT a row (the one to print) BEFORE you invoke "prtOneRecap".

FYI... All data is FICTIONAL.

Perhaps some of you can spot my blunder in the code, so you can take a look here:



Sub prtOneRecap()
'
' prtOneRecap Prints one recap sheet. Code is basically the
' same as prtRecap, except that it prints only the
' selected row.

Dim rcMsg As Integer
Dim i As Integer

Dim wrdApp As Word.Application
Dim rngDoc As Word.Range
Dim wrdDoc As Word.Document

Dim sPropName As String
Dim sMoYr As String
Dim sInitials As String
Dim sTotalOpIncome As String
Dim sTotalGrossIncome As String
Dim sGrossPotRent As String
Dim sConcessions As String
Dim sNetIncome As String
Dim sActPercent As String
Dim sPrePaid As String
Dim sFutureRent As String
Dim sAdjIncomeMonth As String
Dim sAdjPercent As String
Dim sDelinq As String
Dim sPastDue As String

On Error GoTo errHandler

rcMsg = MsgBox("prtOneRecap Version1.0")

Set CurrentRange = Selection

i = CurrentRange.Row

Set wrdApp = New Word.Application

wrdApp.Visible = True

Set wrdDoc = wrdApp.Documents.Open(FileName:="c:\chuck\New Monthly Recap.dot")

Set rngDoc = wrdApp.ActiveDocument.Range(Start:=0, End:=400)
'
' Repair the printable data.
' this means Word was printing "12345"
' when we wannted $12,345. There was
' also a problem with dates and percentages
'

sPropName = StrFix(Cells(i, 1), 44)
sMoYr = StrFix(Format(Cells(i, 2), "mmm yy"), 12)
sTotalOpIncome = StrFix(Format(Cells(i, 9), "$###,##0"), 20)
sTotalGrossIncome = StrFix(Format(Cells(i, 10), "$###,##0"), 20)
sGrossPotRent = StrFix(Format(Cells(i, 3), "$###,##0"), 20)
sConcessions = StrFix(Format(Cells(i, 5), "$####,###0"), 20)
sNetIncome = StrFix(Format(Cells(i, 11), "$###,##0"), 20)
sActPercent = StrFix(Format(Cells(i, 13), "##.0#%"), 12)
'sTotalOpIncome2 = sTotalOpIncome
sPrePaid = StrFix(Format(Cells(i, 4), "$###,##0"), 12)
sFutureRent = StrFix(Format(Cells(i, 7), "$###,##0"), 12)
sAdjIncomeMonth = StrFix(Format(Cells(i, 12), "$###,##0"), 20)
sAdjPercent = StrFix(Format(Cells(i, 14), "##.0#%"), 12)
sDelinq = StrFix(Format(Cells(i, 6), "$###,##0"), 20)
sPastDue = StrFix(Format(Cells(i, 8), "$###,##0"), 20)

With wrdApp.ActiveDocument
.Bookmarks("PropName").Range.Text = sPropName 'header line
.Bookmarks("MoYr").Range.Text = sMoYr

.Bookmarks("TotalOpIncome").Range.Text = sTotalOpIncome 'line 1
.Bookmarks("TotalGrossIncome").Range.Text = sTotalGrossIncome

.Bookmarks("GrossPotRent").Range.Text = sGrossPotRent 'line 2
.Bookmarks("Concessions").Range.Text = sConcessions
.Bookmarks("NetIncome").Range.Text = sNetIncome

.Bookmarks("ActPercent").Range.Text = sActPercent 'line 3

.Bookmarks("TotalOpIncome2").Range.Text = sTotalOpIncome 'line 5
.Bookmarks("PrePaid").Range.Text = sPrePaid

.Bookmarks("FutureRent").Range.Text = sFutureRent 'line 6
.Bookmarks("AdjIncomeMonth").Range.Text = sAdjIncomeMonth
.Bookmarks("AdjPercent").Range.Text = sAdjPercent

.Bookmarks("Delinq").Range.Text = sDelinq 'line 7
.Bookmarks("PastDue").Range.Text = sPastDue

End With

wrdApp.Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0

Set rngDoc = Nothing 'Clean up Range

Set wrdDoc = Nothing

wrdApp.Quit savechanges:=False

Set wrdApp = Nothing

GoTo endIt

errHandler:
rcMsg = MsgBox("prtOneRecap- The error handler was entered!")

Set rngDoc = Nothing

Set wrdDoc = Nothing

wrdApp.Quit savechanges:=False

Set wrdApp = Nothing

rcMsg = MsgBox("The error handler issued Quit!")

endIt:
End Sub

I am interested in general comments on this code as well since I am very new to this genre.

jwise
04-23-2007, 12:01 PM
To test the previously included macro, please use this template.

I had to alter the document for file size considerations. I hope this still works. I had problems trying to zip the files, so I couldn't do that to beat the rules. Sorry... I also had to rename it. You will need to change the name back to ".dot" instead of ".doc"

TIA

Charlize
04-27-2007, 12:57 AM
Put this after your printcommand Application.Wait (Now + TimeValue("0:00:10"))Because your printer needs time to process the request. If you close the document immediately, the printspooler can't fetch the request.

Suggestion to put coding in a normal module. Right click with mouse on project window (left side of screen) and choose insert module.

2nd suggestion. Use doubleclick on a line to print the line.

Charlize

jwise
05-09-2007, 08:48 AM
Thanks to Charlize. The Wait fixed that problem.

After I used the routine, the boss asked if I could save the Word document so that it could be emailed later. The net of this is that the "print" statement now needs to be a "save".

ChangeFileOpenDirectory (docPath)

ActiveDocument.SaveAs FileName:=docName, FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False


'wrdApp.ChangeFileOpenDirectory (docPath)

'wrdApp.ActiveDocument.SaveAs FileName:=docName, FileFormat:= _
'wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
'True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
'False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
'SaveAsAOCELetter:=False


The "ChangeFileOpenDirectory" and "docName" variable were traced with the debugger and have the appropriate variables. I turned on Word visibility and I can switch to Word BEFORE the save attempt and see that everything is properly set up.

The commented-out code was a previous attempt to solve this problem.

Here is the original code (and it works thanks to Charlize and others!) that I wanted to replace:


wrdApp.Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0


The error comes immediately in the debugger, so I'm sure that the "object" is checking my request and finding some error.

Any ideas?

JonPeltier
05-09-2007, 09:14 AM
Why do you need "wrdApp.Application.PrintOut"? Isn't wrdApp the Word Application?

From the bottom of the Object Browser:
Sub SaveAs([FileName], [FileFormat], [LockComments], [Password], [AddToRecentFiles], [WritePassword], [ReadOnlyRecommended], [EmbedTrueTypeFonts], [SaveNativePictureFormat], [SaveFormsData], [SaveAsAOCELetter], [Encoding], [InsertLineBreaks], [AllowSubstitutions], [LineEnding], [AddBiDiMarks])

It's pretty much optional, so replace
ChangeFileOpenDirectory (docPath)

ActiveDocument.SaveAs FileName:=docName, FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False

with this

wrdApp.ActiveDocument.SaveAs FullName

where FullName is the path and file name where the document is to be saved.

jwise
05-09-2007, 10:47 AM
Thanks again, Jon. It worked the first time! I discovered another minor problem, but it was easily fixed. The file name I created did not have an extension, so Word was saving the file as ".dot" since the template was so named. I appended the ".doc" to my file name, and this problem was corrected.

Since I tried several different things like "Application...", I was pretty sure that the problem was my understanding of objects. I've been looking at the VBE help files, but I'm obviously missing something. When I use VBE, I see that it SOMETIMES provides parameter choices for various objects. Other times it does not, especially when I am trying to correct a problem.

How would you reccomend that I approach this? I basically guess until I get the correct object. I also noticed that your solution as opposed to my attempts has few parameters. I think I am including defaults whereas you are just taking them. How am I supposed to learn this? I've seen nothing in the doc (meaning help files) to indicate defaults. Your solution is certainly less CLUTTERED than my attempts!

Thanks again.