Excel VBA New Folder and copy an existing folder based on a cell.

[PaulOPTC]

Good Afternoon,


This seems to be a pretty common request, and I have tried other codes in other threads, but none of them are exactly what I am looking for.

Heres the Goal:

Create a new folder in a particular part of my server (This changes based on the information they put into the cells), rename that folder based on the data in a cell, then copy subfolders from a fixed folder location and put them in this newly created folder.

Heres what I have:

I found this link in another thread.

Copy and Move files and folders

Sub Copy_Folder()
'This example copy all files and subfolders from FromPath to ToPath.
'Note: If ToPath already exist it will overwrite existing files in this folder
'if ToPath not exist it will be made for you.
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String

FromPath = "L:\Projects\Bid\Single Bid Template" '<< This folder wont change
ToPath = I would like this section to populated from a specific cell. (The cell wont change)


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




The cell I would like it to pull from is on a hidden sheet in my workbook.
It is Logic!$B$6

Logic B6 is: =IF(B11,B13&C9,IF(C11,C13&C9,IF(D11,D13&C9,"Status is not correct")))
and it will print out something like this: L:\Projects\(This location changes)\(This location changes)\(This name changes)

The Location it prints out is exactly where and what I would like to name the folder, so that is working, I just need that particualr cell to talk to the VBA code so it can make that.


Please let me know if you need any more information, or if there is a better way to do this.

 

[gordsky]

If I'm understanding your request right then assuming Logic B6 returns the full sting of L:\Projects\(This location changes)\(This location changes)\(This name changes) then this should work

ToPath = sheets(Logic).range("B6")

If it doesnt return the bit in bold above then I think it would be

ToPath = "L:\Projects\" & sheets(Logic).range("B6")

 

[PaulOPTC]

If I'm understanding your request right then assuming Logic B6 returns the full sting of L:\Projects\(This location changes)\(This location changes)\(This name changes) then this should work

ToPath = sheets(Logic).range("B6")

If it doesnt return the bit in bold above then I think it would be

ToPath = "L:\Projects\" & sheets(Logic).range("B6")

You are correct, it gives the full string. I am getting an error though when I try and run it.

Run-Time error '9':
Subscript out of range

Any ideas on how to fix it? Do I have to Dim ToPath to something other then a string?

 

[gordsky]

You are correct, it gives the full string. I am getting an error though when I try and run it.

Run-Time error '9':
Subscript out of range

Any ideas on how to fix it? Do I have to Dim ToPath to something other then a string?

My apologies I missed the "" from the sheet name so should be

ToPath = sheets("Logic").range("B6")

 

[PaulOPTC]

My apologies I missed the "" from the sheet name so should be

ToPath = sheets("Logic").range("B6")

Dang it, I could have figured that out! I have been googling the issue for 15 mins and it was so simple!

Works like a charm now, thank you so much for your help!