Help using FileSystemObject CopyFile
 

I have this code in a workbook:

Sub Main()
Dim filesys As Object
Dim objNet As Object
Dim ReportDate As String
Dim ReportsFolder As String

ReportsFolder = "C:\MyPath\"

Set objNet = CreateObject("WScript.Network")
objNet.MapNetworkDrive LocalName:="L:",
RemoteName:="\\NetworkPath\NetworkFolder",
UserName:="MyUserName", Password:="mypassword"

Set filesys = CreateObject
("Scripting.FileSystemObject")
If filesys.fileexists(ReportsFolder & "FileName.xls")
Then _
filesys.copyfile ReportsFolder
& "FileName.xls", "L:\FileName.xls"

objNet.RemoveNetworkDrive "L:"

End Sub

If I open the workbook and run the procedure manually, it
works. If I create a vbscript to open the workbook and run
the procedure, it also works. However, if I create a
scheduled task to run the script, it gets as far as
opening the workbook. But then the workbook just stays
open in memory. No file gets copied. I have to end task on
Excel.

Can someone help me with this?

tod

Help using FileSystemObject CopyFile
 

Is the Excel workbook throwing up a message that doesn't get responded to
when running as a scheduled task?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tod" wrote in message
...
I have this code in a workbook:

Sub Main()
Dim filesys As Object
Dim objNet As Object
Dim ReportDate As String
Dim ReportsFolder As String

ReportsFolder = "C:\MyPath\"

Set objNet = CreateObject("WScript.Network")
objNet.MapNetworkDrive LocalName:="L:",
RemoteName:="\\NetworkPath\NetworkFolder",
UserName:="MyUserName", Password:="mypassword"

Set filesys = CreateObject
("Scripting.FileSystemObject")
If filesys.fileexists(ReportsFolder & "FileName.xls")
Then _
filesys.copyfile ReportsFolder
& "FileName.xls", "L:\FileName.xls"

objNet.RemoveNetworkDrive "L:"

End Sub

If I open the workbook and run the procedure manually, it
works. If I create a vbscript to open the workbook and run
the procedure, it also works. However, if I create a
scheduled task to run the script, it gets as far as
opening the workbook. But then the workbook just stays
open in memory. No file gets copied. I have to end task on
Excel.

Can someone help me with this?

tod