Create directories linked to a cel value
I have a tiny query.
I would like to have a script that create's a subfolders in the current folder of the workbook, linked to a value in a cellrange ( A1,A2,A3, .... would create 3 folders )
When I use next Script
Sub MakeDirs()
Dim MyRange As String
MyRange = Range("C1")
Dim vFolderList As Variant, i As Long
vFolderList = Range("I6:I" & Cells(Rows.Count, "I").End(xlUp).Row).Value
On Error Resume Next
For i = 1 To UBound(vFolderList, 1)
MkDir MyRange & vFolderList(i, 1) 'amend the directory as required (it must exist)
Next
End Sub
(cell C1 is empty)
the macro works fine on my desktop ( excel 2007 ), and on the desktop of my colleague ( excell 2010 ) as well.
But when the third colleague ( laptop excel 2010 ) uses the macro, the folders are made in c:\my documents.
can anyone help me on this matter
- ericsmpv's blog
- Login or register to post comments
- 3465 reads
Try this
Hello,
Try to use the range like this:
MyRange = ThisWorkbook.Path & "\"
------------------------------------------------------
Sub MakeDirs()
Dim MyRange As String
MyRange = ThisWorkbook.Path & "\"
Dim vFolderList As Variant, i As Long
vFolderList = Range("I6:I" & Cells(Rows.Count, "I").End(xlUp).Row).Value
On Error Resume Next
For i = 1 To UBound(vFolderList, 1)
MkDir MyRange & vFolderList(i, 1)
Next
End Sub