About Us

Jim Rech

Chip Pearson discusses the Shell issue he

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

--
Jim
"Emily Edgington" <Emily wrote in
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_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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cell protection issues	Stuart Mantel[_2_]	Excel Discussion (Misc queries)	0	August 21st 08 08:03 PM
worksheet protection issues	robyn	Excel Worksheet Functions	2	November 26th 06 11:55 PM
protection issues . . . HELP!	Wayne Knazek	Excel Discussion (Misc queries)	3	July 5th 06 09:31 PM
VBA shell command - issues...	JWM6[_2_]	Excel Programming	7	April 19th 06 10:59 PM
Protection issues	[email protected]	Excel Discussion (Misc queries)	1	June 27th 05 03:54 PM

Menu

About Us