An old macro needs updating

[Linda Martinis]

We are using this macro to setup folders and files under another name. We
put in the Job Number and the files from the directory "STARTJOB" should copy
to the new Job # Directory. But it gets as far al the RetVal line and errors
out. Heres the macro, can someone please help us? Thanks in advance.

Sub Auto_Open()

Drive = "J:"
ChDrive Drive
BasePath = "J:\Jobs"
ChDir BasePath
Jobnum = ""
Message = "Please Enter The Job Number "
Title = "Select Job"
Jobnum = InputBox(Message, Title)
JobPath = "J:\Jobs\" + Jobnum
If Jobnum <> "" Then
If Dir(JobPath, 16) = "" Then
Title = "Setup Job " + Jobnum
Message = " "
YesNo = MsgBox(Message, 260, Title)
If YesNo = 6 Then
MkDir JobPath
DoEvents
ChDir JobPath
RetVal = Shell("J:|JOBS\XCOPY J:\JOBS\STARTJOB\*.* /S/E/V/Y")
Title = "Job " + Jobnum + " Created!"
Message = " "
YesNo = MsgBox(Message, 0, Title)
Else
Auto_Open
End If
End If
ChDir JobPath
End If
FName = Application.GetOpenFilename()
If FName <> False Then
Workbooks.Open (JobKitR)
End If
Exit Sub
End Sub

 

[Corey]

Are the directory(Drive locations) still correct?
maybe try:
On Error Resume Next on the line before RetVal?

We are using this macro to setup folders and files under another name. We
put in the Job Number and the files from the directory "STARTJOB" should copy
to the new Job # Directory. But it gets as far al the RetVal line and errors
out. Heres the macro, can someone please help us? Thanks in advance.

Sub Auto_Open()

Drive = "J:"
ChDrive Drive
BasePath = "J:\Jobs"
ChDir BasePath
Jobnum = ""
Message = "Please Enter The Job Number "
Title = "Select Job"
Jobnum = InputBox(Message, Title)
JobPath = "J:\Jobs\" + Jobnum
If Jobnum <> "" Then
If Dir(JobPath, 16) = "" Then
Title = "Setup Job " + Jobnum
Message = " "
YesNo = MsgBox(Message, 260, Title)
If YesNo = 6 Then
MkDir JobPath
DoEvents
ChDir JobPath
RetVal = Shell("J:|JOBS\XCOPY J:\JOBS\STARTJOB\*.* /S/E/V/Y")
Title = "Job " + Jobnum + " Created!"
Message = " "
YesNo = MsgBox(Message, 0, Title)
Else
Auto_Open
End If
End If
ChDir JobPath
End If
FName = Application.GetOpenFilename()
If FName <> False Then
Workbooks.Open (JobKitR)
End If
Exit Sub
End Sub

 

[JLGWhiz]

What is the error message? XCopy can be contankerous in VBA.

 

[Dave Peterson]

Without looking closely, this line:
RetVal = Shell("J:|JOBS\XCOPY J:\JOBS\STARTJOB\*.* /S/E/V/Y")
looks like it should have a backslash after J:
RetVal = Shell("J:\JOBS\XCOPY J:\JOBS\STARTJOB\*.* /S/E/V/Y")

But that xCopy command doesn't look correct at all.

Untested...

RetVal = Shell("xcopy J:\JOBS\*.* J:\JOBS\STARTJOB\*.* /S/E/V/Y")

If I were you, I'd shell to DOS:
click on the Windows start button|Run
Cmd
(and hit enter)
CD\
(to get to the root directory)

And type in your command. Get it to work ok and write down the exact syntax.

If you have more trouble, post the command you used and the version of windows
that should run this--and the version of excel, too.

 

[JLGWhiz]

maybe this will help. it is copied from the knowledge base help files.

The following command line includes the syntax and the switches that you can
use with the xcopy and the xcopy32 commands in MS-DOS mode:
xcopy source [destination] [/a | /m] [/d:date] [/p] [/s] [/e] [/v] [/w]

Dave is correct about the syntax you are using needing to be changed. Yours
had xcopy between the source and the destination.