Using Ghostscript to create PDF need to delay Access while Word prints 1

[mych]

Hi,

I have managed to ammend the code that I currently use to create a PDF via ghostcript of an Access report to also create a PDF of the Word document too. (Amazed myself --- )

Unfortunately two minor hic-cups

1. The PDF gets converted as US Letter even though the PS file is A4. Is there some switch I need to use to make sure that Ghostscript converts to A4 size? The script I use currently is:

    gsscript = GhostscriptDir & "bin\gswin32c.exe -dQUIET -dNOPAUSE -dBATCH -sDEVICE=pdfwrite -sOutputFile=" & Chr$(34) & OutputPath & Chr$(34) & " " & Left(gsFileList, Len(gsFileList) - 1)

2. My code currently has:

Public Function gsPrintIt(gsReportName, gsWhereCond, gsOption)

Dim gsFile As String

Static gsFileList As String
Static gsFileName As String

    Select Case gsOption

        Case gsFirstRpt, gsOnlyRpt
            gsFileList = ""
            gsFileName = "A"
            gsFile = Dir(gsOutputPath & "*.ps ")
    
        Do While gsFile <> ""
            Kill gsOutputPath & gsFile
            gsFile = Dir()
        Loop
        
    Case Else
        gsFileName = Chr(Asc(gsFileName) + 1)
    End Select
    
    Call ChangeToPostscriptPrinter
    
    [green]'************************************************************************
    
    'My modification to the code to enable me to use same code to output either
    'a Word file or an Access Report as PDF.
    
    'Originally this code just had the following line at this point...
    'DoCmd.OpenReport gsReportName, acNormal, , gsWhereCond

    'I have defined a flag - GlbWrdReportFlag - which will enable the code to
    'decide which bit of code to run next. If GlbWrdReportFlag = 1 then its a
    'Word Doc. If GlbWrdReportFlag = 2 then its an Access Report. [/green]
    
        Select Case GlbWrdReportFlag
        
            Case 1  [green]'Word Doc to PDF
                'Here we need to open the HLOR MergeDoc [/green]
                Dim objWord As New Word.Application
                Dim docWord As Word.Document
                Dim strMergeDoc As String

                    If fSetAccessCaption Then
                        On Error Resume Next
        
                        strMergeDoc = GlbLocWorkDoc & GlbWrdDocName
        
                [green]'Opens the MailMergeDocument minimised [/green]
    
                        With objWord
                            .Visible = True [green]'Eventually change this to False [/green]
                            .WindowState = wdWindowStateNormal [green]'Eventually change this to Minimised [/green]
                            Set docWord = objWord.Documents.Open(strMergeDoc)

                             [green]'Run Macros [/green]
                                .Run ("ImportTempTeamDocs") [green]'Inserts the Temp docs one by one [/green]
                                .Run ("InsertTblContents")  [green]'Inserts a Table of Contents [/green]
                                .Run ("RunMailMerge")       [green]'Runs the mailmerge [/green]
                        End With
                        
                            objWord.ActiveDocument.PrintOut
                            
                        [green]'NEED TO ADD A PAUSE HERE TO LET WORD FINISH PRINTING [/green]
       
                    [red] docWord.Close wdDoNotSaveChanges [/red]
        
                    objWord.Application.Quit wdDoNotSaveChanges
        
        Set objWord = Nothing
        Set docWord = Nothing
        strMergeDoc = ""

        [green]'restore the caption [/green]
        Call sRestoreTitle
        
    End If
                           
            Case 2  [green]'Access Report to PDF [/green]
                DoCmd.OpenReport gsReportName, acNormal, , gsWhereCond
            
            Case Else [green]'Undefined value... error
            'If GlbWrdReportFlag is not 1 or 2 then code has fallen over. Need to put suitable error coding [/green]
            
        End Select
    
    [green]'End of my modification
    '************************************************************************ [/green]
    
    Call ResetDefaultPrinter
    
    FileCopy gsOutputPath & "tfile.ps", gsOutputPath & gsFileName & ".ps"
    
    gsFileList = gsFileList & gsOutputPath & gsFileName & ".ps "
    
    gsPrintIt = gsFileList
    
End Function

I need to add a pause to enable Word to print. If I run the code as it stands, I get a single blank page pdf. If I place a break on the line I've highlighted [red] red [/red] the word document is output and coverted to a PDF (Allbeit - with problem 1 above)

Is there a way of getting Access to wait until Word has finished?

Any Help Appreciated.

Mych

 

[njitter]

API: Shell and Wait
Author(s): Terry Kreft

(Q) Whenever I shell out to another application, Access doesn't seem to wait before executing the next VBA statment. How do I make Access wait till I'm done with my other application.


Answer at :

http://www.mvps.org/access/api/api0004.htm

---
It's never too late to do the Right thing

 

[mych]

Thanks Njitter

This did the trick... Cheers