Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 12th, 2005, 04:24 PM
bml bml is offline
Registered User
Join Date: Feb 2005
Location: , , Canada.
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"
Reply With Quote
  #2 (permalink)  
Old February 12th, 2005, 05:42 PM
bml bml is offline
Registered User
Join Date: Feb 2005
Location: , , Canada.
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.

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 08:57 AM.

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