Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 12th, 2005, 04:24 PM
bml bml is offline
Registered User
Join Date: Feb 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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"
Old February 12th, 2005, 05:42 PM
bml bml is offline
Registered User
Join Date: Feb 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Context Menu Shell Extension problem aldwinenriquez .NET Framework 2.0 1 December 18th, 2007 07:35 AM
how to put result of shell function into variable method Beginning VB 6 1 April 20th, 2006 11:42 AM
Use of VB.NET's Shell function in C# jude_wong C# 8 December 16th, 2005 07:49 PM
Using the Shell Function RAPSR59 Access 1 April 13th, 2005 07:14 AM
Shell Function Question SerranoG Access VBA 4 November 12th, 2003 07:57 PM

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