Open a word doc and print selection till value is found from Excel VBA

[Mick17]

Hi,

I have searched the whole internet and i couldn't find anything that matches my needs. I hope someone here can help me out.
At work we make programs for CNC machines. Each program is saved the following: a letter (depends on machine), then 1,2 or 3(occur all 3 for every program), then the program number ( it can be 2-5 digits long, the digits between are zero's ).
For example program 20 for machine N is saved as : N1000020 & N2000020 & N3000020.

I am making an excel file where you can choose the machine, fill in the program number. Then with this data he should open all three of the files, make a selection till the word "END" is found, then print this selection and close the files again.

I have the following. This code will open print and close the needed files. the only thing I can't figure out is how to print the selection instead of the whole document. The selection I wan't to print is from the beginning untill the word "END"

Does someone know how to do this, or at least can help me going???

If my question is not clear, don't hesitate to ask

my code:

[COLOR=#101094]Private[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336] OKbutton_Click[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] iLetter [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094] Long[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] letter [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094] String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] objdoc [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094] Object[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Case[/COLOR][COLOR=#303336] machinebox[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value
[/COLOR][COLOR=#101094]Case[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"CTX510"[/COLOR][COLOR=#303336]
       letter [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"C"[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Case[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Lu25"[/COLOR][COLOR=#303336]
       letter [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"F"[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Case[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"LB45"[/COLOR][COLOR=#303336]
       letter [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"N"[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] CreateObject[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"word.application"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] iLetter [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]3[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] objdoc [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]documents[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Open[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"\\path\Machine"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] machinebox[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]""[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] letter [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] iLetter [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] Format[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]programbox[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"000000"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]".OPT"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        objdoc[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]PrintOut
        objdoc[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Close [/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] iLetter
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]sub
[/COLOR]

 

[NdNoviceHlp]

I made some changes. This is untested but seems reasonable. HTH. Dave

Dim MyRange As Variant
Dim Wapp As Object

'With CreateObject("word.application")
Set Wapp = CreateObject("Word.Application")
For iLetter = 1 To 3
Set objdoc = Wapp.documents.Open("\\path\Machine" & _
machinebox.Value & "" & letter & iLetter & Format(programbox, "000000") & ".OPT")
Wapp.ActiveDocument.Select
With Wapp.Selection.Find
.Text = "END"
.Forward = True
.MatchWholeWord = True
.Execute
If .found = True Then
Set MyRange = Wapp.ActiveDocument.paragraphs(1).Range
MyRange.SetRange Start:=MyRange.Start, _
    End:=Wapp.ActiveDocument.paragraphs(Wapp.Selection).Range.End
MyRange.PrintOut
'MyRange.Select
'Selection.PrintOut
End If
End With
'objdoc.PrintOut
objdoc.Close False
Next iLetter
Wapp.Quit
Set objdoc = Nothing
Set wdapp = Nothing
'EndWith

 

[Mick17]

Hi Dave, Thanks for your help. The code runs for a long time, and then i get the MsgBox "microsoft excel is waiting for another application to complete an ole action". I've tried the following:

1. Open your Excel sheet.
2. Go to File-> Options.
3. Go to Advanced tab and click 'Ignore other applications that use Dynamic Data Exchange (DDE)' in the General area

That didn't helped either.
Any suggestions???

 

[NdNoviceHlp]

This doesn't look right....

Select Case machinebox.Value
Case"CTX510"
 letter ="C"
Case"Lu25"
 letter ="F"
Case"LB45"
 letter ="N"
End Select

Does machinebox have some value? It should be dimmed as a string. Code should go...

Select Case machinebox
Case"CTX510"
 letter ="C"
Case"Lu25"
 letter ="F"
Case"LB45"
 letter ="N"
End Select

The basic idea for the code I posted is to open the specified docs, find "END" in the doc, then set a range from the start of the doc to the selection "END", then printout the range, close the doc and continue until done. Trial the suggestion. If it doesn't work maybe Macropod will offer a more elegant solution. Good luck. Dave
ps. the "iletter" in your document path also has to be a string not a long. ie. the whole document path must be a string

 

[Macropod]

At work we make programs for CNC machines. Each program is saved the following: a letter (depends on machine), then 1,2 or 3(occur all 3 for every program), then the program number ( it can be 2-5 digits long, the digits between are zero's ).
For example program 20 for machine N is saved as : N1000020 & N2000020 & N3000020.

I am making an excel file where you can choose the machine, fill in the program number. Then with this data he should open all three of the files, make a selection till the word "END" is found, then print this selection and close the files again.

What is the point of having 'END' in the document? Why can't you use documents or templates that finish at the appropriate point? Even assuming you can't do that, a more efficient (though less robust) way would be to bookmark the ranges that are to be printed (it could be the same bookmark name (e.g. PrintOut) in all of the documents), then just print the bookmarked range without the need for a 'Find'. Alternatively, and more robust, if each document consists of only a single Section, or the same fixed number of Sections before the 'END', you could insert a continuous Section break there and only print up to that break.

That said, given your current setup, try something along the lines of:

Private Sub OKbutton_Click()
Dim i As Long, StrVer As String, wdApp As Object, wdDoc As Object, wdRng As Object

Select Case MachineBox.Value
  Case "CTX510": StrVer = "CTX510 C"
  Case "Lu25": StrVer = "Lu25 F"
  Case "LB45": StrVer = "LB45 N"
  Case Else: Exit Sub
End Select

Set wdApp = CreateObject("Word.Application")
With wdApp
  .Visible = True
  For i = 1 To 3
    Set wdDoc = .Documents.Open("\\path\Machine" & StrVer & i & _
      Format(programbox, "000000") & ".OPT", False, True, False)
    With wdDoc
      Set wdRng = .Range(0, 0)
      With .Range
        With .Find
          .Text = "END"
          .Forward = True
          .MatchWholeWord = True
          .MatchCase = True
          .Execute
        End With
        If .Find.found = True Then
          wdRng.End = .Duplicate.Start
          wdRng.PrintOut
        End If
      End With
      .Close False
    End With
  Next
  .Quit
End With
Set wdRng = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

Note the line:
.Visible = True
That's only needed while testing, so you don't end up with orphaned Word sessions you can't easily close sitting in memory.

 

[Mick17]

It is the code for clicking "OK" on a userform. machinebox.value is the machine they have chosen.

 

[Mick17]

Thank you all for all your help, i will look into it and see if its possible. @macropod, the files i want to print are programs for CNC machines, they start with a tool list and after that follows the code. the tool list only last 1/2 pages, the code however can go until 20 pages. the tool list will end whit the words "END TOOLLIST". So thats why i cant sepperate it into two documents.

 

[Mick17]

@ Macropod. Your code gives an error at wdRng.PrintOut. Any thaughts?

 

[Mick17]

figured it out myself. Needed to replace wdRng.PrintOut with
wdRng.select
wdDoc.printout Range:=1