Printing word doc from excel

[Craig1]

Hi Guys,

I have got this code working but I still have one little (hopefully) issue to resolve.
The code is:-

Private Sub CommandButton1_Click()
' Set a reference to Microsoft.Word X.0 Object Library
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim sPath As String
Dim sFile As String
Dim bPrinted As Boolean

sPath = "Z:\Engineering Costs & issue control\Line 2\"
sFile = Workbooks("Issue Control sheet for SMP's.xls").Worksheets(1).Range("B4").Value
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open(sPath & sFile)
wdDoc.PrintOut Background:=False
wdDoc.Close False
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub


It works fine when the range is B4, I now need it to be more dynamic so as the worksheet info starts filling up the range will go down to C4, D4 etc.

Again Guys, any help is appreciated.

Craig.
<!-- / message -->

 

[VoG]

Sorry, I had misunderstood. To print just the last file in column B try

Private Sub CommandButton1_Click()
' Set a reference to Microsoft.Word X.0 Object Library
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim sPath As String
Dim sFile As String
Dim bPrinted As Boolean
Dim LR As Long, i As Long
Set wdApp = CreateObject("Word.Application")
sPath = "Z:\Engineering Costs & issue control\Line 2\"
With Workbooks("Issue Control sheet for SMP's.xls").Worksheets(1)
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    sFile = .Range("B" & LR).Value
    Set wdDoc = wdApp.Documents.Open(sPath & sFile)
    wdDoc.PrintOut Background:=False
    wdDoc.Close False
End With
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

 

[Craig1]

Thanks for that Peter it worked perfectly and thank you for your patience.
It is much appreciated.

I'll now post a different thread I posted this morning.

Craig

 

[Darchcruise]

Hi,
I tried this code and it didn't work. When I pressed "step into" it spotted an error in the first line: wdApp as Word.Application. It gives me a "Compile error:User-Defined type not defined.

Any thoughts?

Thanks

 

[VoG]

In the VBE, Tools > References and tick Microsoft Word x.x Object Library. x.x is the version number of Word.

 

[Darchcruise]

Thank you

 

[Craig1]

Hi guys,

This worksheet has been working great for months and saves a great deal of time for the end user and creating a full log and issue control of the documents.
I have just come up with a little issue I need to resolve with the help of you guys.
Basically it looks in column B for the title of the doc from a drop down list. But some alterations to some of the documents were needed so the number has to change slightly so the VBA cannot find and print the doc because of the title change.
What I need is the code to find the title with the nearest wording or something similar.

e.g.
This is the original document.
Bead Prod SMP DRY L2 003.001 Disconnect or reconnect fines pipework under DE 602

After alterations it is now
Bead Prod SMP DRY L2 003.002 Disconnect or reconnect fines pipework under DE 602

Because of the alterations the issue control number is now 2.

If I'm at work I can modify the validation list but if not the end user gets an error.

Any ideas guys.

Craig.

 

[Craig1]

Hi guys,

Has anyone got any idea's on this latest problem???

Craig

 

[Craig1]

Hi Guys,

Another little issue has arisen.

The References and tick Microsoft Word x.x Object Library

now staes that

MISSING: Microsoft Word 14.0 Object Library

so it now won't find any word document. The only thing that has changed is another computer that has access to the same folder is now using Office 2010 instead of my Office 2003

Any ideas.

Craig.

 

[VoG]

This may help http://www.cpearson.com/excel/MissingReferences.aspx

 

[Craig1]

Forgot to say the PC with Office 2010 works perfect.

Craig.