Printing a list of documents from an excel spreadsheet

[paulslambo]

Hi,

I have searched the forum before posting this and the code that I have used to do this is at least partially ripped from posts on this forum.

Basically I am attempting to make a spreadsheet that lists a variety of documents to be printed and added into a hard copy file.

these docs are either word docs excel docs or pdfs.

the excel and pdfs are no problem but I keep running into problems with printing word docs.

I have tried a couple of ways of getting around this but if I define a word application object in filegen() I can run filegen once and then I get an error box with nothing on it. If I close excel and reopen the workbook then it will run again. If I define a word an in the wordprinter() sub I instead get a dialogue trying to save normal.dot over and over.

either way that I do it I get left with several winword.exe processes in task manager which makes me think that I not doing house keeping correctly

Any help I can get to point out where I am going wrong would be much appreciatied. I am something of a beginner at vb and programming in general.

the code for these subs follows

Sub filegen()

    Select Case Sheet1.ComboBox1.Value
    Case "Office File Utility"
            For Each i In Range("Utility")
                p = i.Value
                Select Case Left(Right(p, 4), 3)
                    Case "pdf"
                      pdfprintin (p)
                    Case "xls"
                     fileprint (Left(Right(p, (Len(p) - 1)), (Len(p) - 2)))
                    Case "doc"
                     wordprinter (p)
                End Select
            Next i
  
    Case "Office File Mining"
        For Each i In Range("Mining")
            p = i.Value
            Select Case Left(Right(p, 4), 3)
                Case "pdf"
                  pdfprintin (p)
                Case "xls"
                 fileprint (Left(Right(p, (Len(p) - 1)), (Len(p) - 2)))
                Case "doc"
                 wordprinter (p)
            End Select
        Next i
    
    Case "Field File"
                For Each i In Range("Field")
                p = i.Value
                Select Case Left(Right(p, 4), 3)
                    Case "pdf"
                      pdfprintin (p)
                    Case "xls"
                     fileprint (Left(Right(p, (Len(p) - 1)), (Len(p) - 2)))
                    Case "doc"
                     wordprinter (p)
                End Select
            Next i
        
    End Select
End Sub

Sub wordprinter(p As String)
    Dim d As Object
    Dim owordapp As Object
    Set owordapp = CreateObject("Word.Application")
    Set d = owordapp.Documents.Add(p)
        If ((d.path & "\" & d.Name) <> "Q:\Quality Manual\Current\Forms\Risk Assessment Site Specific.doc") Then
            d.PrintOut Copies:=1
            Application.Wait Now + TimeValue("00:00:02")
            d.Close (False)
        Else
            d.PrintOut Copies:=2
            Application.Wait Now + TimeValue("00:00:02")
            d.Close (False)
        End If
    Set d = Nothing
    owordapp.Quit
    Set owordapp = Nothing
End Sub

 

[westconn1]

if you have multiple documents to print you should open word, pass each document to print in turn then close word

simplest method is to declare a global object of word in the general section, then it is available to all subs and functions, set the global object on the first word doc to be printed (not needed if no word docs),
close the word app and destroy objects at end of loop

 

[paulslambo]

Thank you so much for you quick response.

just to confirm does this

Set someobject = Nothing

destroy an object?

also to Declare a global variable do I do the following?

Public someobject as Object

rather than

Dim someobject as Object

 

[westconn1]

yes for the first
public makes it available to all forms and modules, probably not required,
Dim is fine for any form or module, just put at the top in the general section

 

[paulslambo]

Thanks westconn1,

I made your changes and it works perfectly now.