Module protection & Shell issues

[Emily Edgington]

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_Vendor_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.txt"
Name "I:\AcctSys\Interfaces\TNET\TNET_Vendor\tnet_vdr.prn" As
"I:\AcctSys\Interfaces\TNET\TNET_Vendor\tnet_vdr.txt"
Application.ScreenUpdating = True

sftp

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

MsgBox "Vendor file transferred to TrialNet server"

End Sub

 

[Jim Rech]

Chip Pearson discusses the Shell issue here:

http://www.cpearson.com/excel/ShellAndWait.aspx

--
Jim
message |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_Vendor_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.txt"
| Name "I:\AcctSys\Interfaces\TNET\TNET_Vendor\tnet_vdr.prn" As
| "I:\AcctSys\Interfaces\TNET\TNET_Vendor\tnet_vdr.txt"
| Application.ScreenUpdating = True
|
| sftp
|
| Application.Wait (Now + TimeValue("0:00:07"))
|
| MsgBox "Vendor file transferred to TrialNet server"
|
| End Sub