ExcelBanter - An old macro needs updating

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - An old macro needs updating (https://www.excelbanter.com/excel-programming/405706-old-macro-needs-updating.html)

An old macro needs updating

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

An old macro needs updating

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

"Linda Martinis" wrote:

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

An old macro needs updating

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.

Linda Martinis wrote:

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

--

Dave Peterson

An old macro needs updating

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.

"Linda Martinis" wrote:

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