Consulting

Results 1 to 8 of 8

Thread: Custom Access to Word VBA Problem, Question

  1. #1
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location

    Custom Access to Word VBA Problem, Question

    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.
    [VBA]
    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
    [/vba]
    The problem is when I have created a Second Command Button( to open another Doc. File) with the following Code,

    [VBA]
    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
    [/vba]

    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

  2. #2
    VBAX Regular GP George's Avatar
    Joined
    Jul 2004
    Location
    Puget Sound, WA
    Posts
    26
    Location
    The first thing to check might be to verify that the Word Macro actually exists in "C:\Divisions\Report.doc"

    HTH
    George
    "We're all in this together."
    -Red Green

  3. #3
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location
    Quote Originally Posted by GP George
    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.

  4. #4
    VBAX Regular GP George's Avatar
    Joined
    Jul 2004
    Location
    Puget Sound, WA
    Posts
    26
    Location
    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
    "We're all in this together."
    -Red Green

  5. #5
    VBAX Newbie
    Joined
    May 2004
    Location
    Germany
    Posts
    2
    Location

    Wink

    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
    If you perceive that there are four possible ways in which a procedure can go wrong, and circumvent these, then a fifth way, unprepared for, will promptly develop!
    (Murphy's 6th law)

  6. #6
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  7. #7
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location
    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


    [vba]
    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
    [/vba]

  8. #8
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •