I am using Excel 2000 and have a procedure that works fine as long as the
project is unprotected. With protection in place, it dies on the first
'saveas' command (see below). Also, I read that the Shell function may not
finish before subsequent code executes, and found that to be true for me -
the msgbox relaying that the file was transferred is appearing before the
file is actually transferred. I added a wait command, but would prefer that
the msgbox not appear until the shell command actually completes.

Thanks in advance for your help!

Sub Prepare_TXT()

Application.ScreenUpdating = False
Workbooks.Open Filename:= _

"I:\AcctSys\Interfaces\TNET\TNET_Vendor\Tnet_Vendo r_Backfeed_Query.xls"
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("tnet_vdr_template.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Workbooks("Tnet_Vendor_Backfeed_Query.xls").Close
Range("A1").Select

Application.DisplayAlerts = False

' DIES HERE
ActiveWorkbook.SaveAs
Filename:="I:\AcctSys\Interfaces\TNET\TNET_Vendor\ tnet_vdr.prn", _
FileFormat:=xlTextPrinter, CreateBackup:=False
ActiveWorkbook.SaveAs
Filename:="I:\AcctSys\Interfaces\TNET\TNET_Vendor\ tnet_vdr.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
Kill "I:\AcctSys\Interfaces\TNET\TNET_Vendor\tnet_vdr.t xt"
Name "I:\AcctSys\Interfaces\TNET\TNET_Vendor\tnet_vdr.p rn" As
"I:\AcctSys\Interfaces\TNET\TNET_Vendor\tnet_vdr.t xt"
Application.ScreenUpdating = True

sftp

Application.Wait (Now + TimeValue("0:00:07"))

MsgBox "Vendor file transferred to TrialNet server"

End Sub