I am trying to copy a complete folder into a new folder through excel macro, but i need the new folder name to be entered by the user every time
This is the current code i have that copies to a permanent/static folder
Sub Copy_Folder()
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
FromPath = "C:\Users\hayekn\Desktop\AR Reports\0MENACA Working File\AR Working File\3- FINAL Country Files\1" '<< Change
ToPath = "C:\Users\hayekn\Desktop\AR Reports\0MENACA Working File\AR Working File\Weekly Back" '<< Change
Application.CutCopyMode = False
If Right(FromPath, 1) = "\" Then
FromPath = Left(FromPath, Len(FromPath) - 1)
End If
If Right(ToPath, 1) = "\" Then
ToPath = Left(ToPath, Len(ToPath) - 1)
End If
Set FSO = CreateObject("scripting.filesystemobject")
If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If
FSO.CopyFolder Source:=FromPath, Destination:=ToPath
MsgBox "You can find the files and subfolders from " & FromPath & " in " & ToPath
End Sub
I worked out a way for the user to enter a folder name, but unable to link this name to the new folder being created
Dim strName As String
Dim WeekStr1 As String
Dim WeekStr2 As String
Reenter:
strName = InputBox(Prompt:="Enter the week you would like to update", _
Title:="Week Selection.", Default:="0")
If strName = vbNullString Then
Exit Sub
Else
Select Case strName
Case Else
MsgBox "Incorrect Entry."
GoTo Reenter
End Select
End If
I need the "StrName" to be placed in the following context for it to work, but cant seem to get the right syntax
ToPath = "C:\Users\hayekn\Desktop\AR Reports\0MENACA Working File\AR Working File\Week "StrName"" '<< Change
Perhaps like below?
ToPath = "C:\Users\hayekn\Desktop\AR Reports\0MENACA Working File\AR Working File\Week" & StrName
To concatenate Text/String simply use &
(ampersand) . +
(plus) works too but I'm comfortable with &