Error - file already exists.

[Jim15]

I have the letters A - O in rows 1 - 15 of column A on my Excel
spreadsheet. However, instead of the following routine making
subfolders under C:\RESERVES I get the error "file already exists".
Can someone help?

Thanks,

Jim


Sub createfolders()
'
' CreateFolders Macro
' Macro recorded 3/22/2006 by JBW
'
Dim cou As Integer, FolderStr As String, FileSys
For cou = 1 To ActiveSheet.UsedRange.Rows.Count
FolderStr = Format(Trim(Str(cou)), "00#") + "_" +
ActiveSheet.Cells(cou, 1)
Set FileSys = CreateObject("Scripting.FileSystemObject")
FileSys.createfolder "C:\RESERVES"
Next
End Sub

 

[Jim Cone]

Jim,
'-------------------
Sub createfolders()
Dim cou As Long
Dim FolderStr As String
Dim FileSys As Object

For cou = 1 To ActiveSheet.UsedRange.Rows.Count
FolderStr = Format$(Trim$(cou), "00#") & "_" & ActiveSheet.Cells(cou, 1).Value
Set FileSys = CreateObject("Scripting.FileSystemObject")
FileSys.createfolder "C:\RESERVES\" & FolderStr
Next
Set FileSys = Nothing
End Sub
'----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Jim15"wrote in message...
I have the letters A - O in rows 1 - 15 of column A on my Excel
spreadsheet. However, instead of the following routine making
subfolders under C:\RESERVES I get the error "file already exists".
Can someone help?
Thanks,
Jim


Sub createfolders()
'
' CreateFolders Macro
' Macro recorded 3/22/2006 by JBW
'
Dim cou As Integer, FolderStr As String, FileSys
For cou = 1 To ActiveSheet.UsedRange.Rows.Count
FolderStr = Format(Trim(Str(cou)), "00#") + "_" +
ActiveSheet.Cells(cou, 1)
Set FileSys = CreateObject("Scripting.FileSystemObject")
FileSys.createfolder "C:\RESERVES"
Next
End Sub

 

[NickHK]

Jim,
The other Jim has fixed the code, but do you really need to create an
instance of the FSO just to make a folder.
You do have the native MkDir.

NickHK

 

[Jim Cone]

Hi NickHK,
I almost fixed the code. <g>
The Set statement should be placed before the loop, not in it.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Jim,
The other Jim has fixed the code, but do you really need to create an
instance of the FSO just to make a folder.
You do have the native MkDir.

NickHK

 

[NickHK]

Jim,
Well, I didn't look too closely at your code..
Just wondering why everybody wants to use FSO nowadays for functions that
are easily accomplished without it.
Given that it may be disabled on some systems, is notoriously slow and
suffers from numerous versions, why bother ?

NickHK

 

[Jim Cone]

NickHK,

You are course correct that it doesn't make sense to use the FileSystemObject
(FSO) when you don't have to. However, FSO does have some virtues?

It remains unchanged since 2001 and essentially will work in any system
with IE 4/ Windows NT4.0 and up.
I don't believe any other MS code system has been that stable.
The code is logical and easy to learn.
It does what it says it will do; you can rely on it.
It is far superior to using FileSearch.

Randy Birch has done some tests using FSO and (no surprise) the winner is...
the FindFirstFile APIs.
http://vbnet.mvps.org/index.html?code/screen/shappbarmessage.htm

Regards,
Jim Cone
San Francisco, USA


Jim,
Well, I didn't look too closely at your code..
Just wondering why everybody wants to use FSO nowadays for functions that
are easily accomplished without it.
Given that it may be disabled on some systems, is notoriously slow and
suffers from numerous versions, why bother ?
NickHK