PDA

View Full Version : Custom Access to Word VBA Problem, Question



Jack58
07-19-2004, 09:02 AM
I am using the Code listed below in Access 2000 with a Command Button to Open Word, Open a Document and then run a Word Macro.

Private Sub Command7_Click()
On Error GoTo Err_OpenGuide

Dim Oapp As Object

Set Oapp = CreateObject("Word.Application")
Oapp.Documents.Add "C:\Divisions\PrintOut.doc"
Oapp.Run "PrintOut"
ActiveDocument.ActiveWindow.Close
Set Oapp = Nothing
Exit_OpenGuide:
Exit Sub



Err_OpenGuide:
MsgBox Err.Description
Resume Exit_OpenGuide

End Sub

The problem is when I have created a Second Command Button( to open another Doc. File) with the following Code,


Private Sub cmdReport_Click()

Call ReportCreation(Me![cboDiv], Me![cboBuyer])

On Error GoTo Err_OpenGuide

Dim Oapp As Object

Set Oapp = CreateObject("Word.Application")
Oapp.Documents.Add "C:\Divisions\Report.doc"
Oapp.Run "PrintOut"
ActiveDocument.ActiveWindow.Close
Set Oapp = Nothing
Exit_OpenGuide:
Exit Sub

Err_OpenGuide:
MsgBox Err.Description

Resume Exit_OpenGuide



End Sub


When I ran the Code from the button the Macro did not run. The Document did open.

I sure could use some help with this.


Thanks



Jack

GP George
07-19-2004, 11:45 AM
The first thing to check might be to verify that the Word Macro actually exists in "C:\Divisions\Report.doc"

HTH
George

Jack58
07-19-2004, 11:50 AM
The first thing to check might be to verify that the Word Macro actually exists in "C:\Divisions\Report.doc"

HTH
George


The Macro is in the Normal.Dot I checked the other Document and the Macro is not attached to that Document.

GP George
07-19-2004, 11:53 AM
I am not a Word expert, but I don't believe macros are automatically attached to new docs, so I would try attaching it to the current doc.

HTH
George

MakeItSo
07-20-2004, 03:13 AM
Hi there!

Have a try with using the full macro name instead:

Oapp.Run "Normal.NewMacros.PrintOut

You see the full name, if you click on
-->Tools-Customize
-->Commands-Macros

Hope that helps,
Cheers,
MakeItSo

TonyJollans
07-22-2004, 12:25 PM
Let's look at this!

The macro is in Normal.dot so it doesn't matter what document you open it should be available. It works in the first case, not in the second. What are the differences?

It seems very unlikely but to rule out the possibility of there being something in ReportCreation which causes the problem, comment out the call. Does it work then?

If not, then the only difference between the two procedures is the document you are opening and so the problem can only be there. Some questions ..

Does either document have an attached template other than Normal?
Does either document have any autorun macros? Or other event-driven code?
Are you quite sure the macro does not run - or does it perhaps run but not do what you expect?

Jack58
07-23-2004, 10:14 AM
I have had someone helkping me write the code to format the reports in Word, however their References are Microsoft Word 11 Object Libary, and I only have 9.0 Object libary. Here is the section of code that is not formatting the documents.

I have added the entire database and working files in hope that this will help.

Thanks in advance

ps.. I have not been getting a message when I receive a responce on this thread,
my e-mail is : jjackelini@amerisourcebergen.com



objWord.Documents.Open strPrint
objWord.Selection.Find.Execute "1REPORT", , , , , , , , , "^m", wdReplaceAll
With objWord.ActiveDocument.PageSetup
.Orientation = wdOrientLandscape
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.5)
.LeftMargin = InchesToPoints(0.2)
.RightMargin = InchesToPoints(0.2)
End With
objWord.Selection.WholeStory
objWord.Selection.Font.Name = "Courier"
objWord.Selection.Font.Size = 9#
'objWord.Visible = True
'Documents("PrintOut.txt").Close
' Documents("PrintOut.doc").Close


' Close .PrintOut.Doc
' Documents("Printout.Doc").Close

' Print the PrintOut.doc file
' strLine = MsgBox("Do you wish to print the report?", vbYesNo, "Print?")
' If strLine = vbYes Then ActiveDocument.PrintOut

' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = "*"
Forms![frmProgressBar].Refresh
DoEvents
j = 0
For i = 1 To 10000
objWord.Selection.HomeKey unit:=wdLine
objWord.Selection.MoveRight unit:=wdCharacter, Count:=1, Extend:=wdExtend
'Test = Selection.Text
If objWord.Selection = "0" Then
objWord.Selection.Delete
objWord.Selection.TypeText Chr(32)
objWord.Selection.HomeKey unit:=wdLine
objWord.Selection.TypeText Chr(13)
objWord.Selection.MoveDown unit:=wdLine, Count:=1
j = 0
GoTo NextI
End If
If i <> "0" Then
objWord.Selection.MoveDown unit:=wdLine, Count:=1
objWord.Selection.HomeKey unit:=wdLine
j = j + 1
End If
If j = 100 Then Exit For
NextI:
If i Mod 50 = 0 Then
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
End If
Next i

objWord.Selection.WholeStory
With objWord.Selection.ParagraphFormat
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceExactly
.LineSpacing = 8
.CharacterUnitLeftIndent = 0
.CharacterUnitRightIndent = 0
.CharacterUnitFirstLineIndent = 0
.LineUnitBefore = 0
.LineUnitAfter = 0
objWord.ActiveDocument.SaveAs strRootPath & "PrintOut.doc", wdFormatDocument

End With
objWord.Selection.HomeKey unit:=wdLine



Set objWord = Nothing



DoCmd.Close acForm, "frmProgressBar"

End Sub

TonyJollans
07-23-2004, 12:54 PM
Sorry, I'm probably being a bit dense, but I'm confused. What is the code in your last post and how does it relate to the code in your first post?