p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Excel VBA (http://p2p.wrox.com/forumdisplay.php?f=79)
-   -   shell function problem (http://p2p.wrox.com/showthread.php?t=25503)

bml February 12th, 2005 04:24 PM

shell function problem
I've created a routine create a batch file that will merge some print files and then send the single file to the application.activePrinter

I need to do this to avoid having a banner page printing between every print (I have no option to turn off the banner page on a network printer)

The batch file created from the code runs fine if I run it manually but if I use the shell function, nothing happens.

A portion of the code is listed below:

        For i = 0 To cntrl.ListCount - 1
            .PivotFields(sField).CurrentPage = cntrl.Column(0, i)
            sTemp = "F" & Format(i, "0000") & ".prt"
            Sheets(sSheet).PrintOut PrintToFile:=True, _
                    PrToFileName:=Range("PrtPath") & "\" & sTemp
            If i = 0 Then
                file.write "copy/b " & sTemp & " merge.prt" & vbCrLf
                file.write "copy/b merge.prt + " & sTemp & vbCrLf
            End If
        Next i

    End With
    sTemp = Application.ActivePrinter
    sTemp = Mid(sTemp, 1, InStr(1, sTemp, " ") - 1)

    file.write "copy/b merge.prt " & sTemp & vbCrLf
    file.write "del *.prt" & vbCrLf
    file.write "del " & sBatchFile & vbCrLf

    If Shell(sBatchFile, vbNormalFocus) = 0 Then MsgBox "Shell'd print batch was not successful"

bml February 12th, 2005 05:42 PM

Solved my own problem.

When running the batch file from the run prompt or by double clicking the file
the directory change command to go to the directory where the files are works and
the subsequent copy commands also work. When running from shell function the directory
change doesn't appear to happen and the copy commands can't find the files. By including
the full path name into the file names, the batch works fine.

All times are GMT -4. The time now is 05:21 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.