+ Reply to Thread
Results 1 to 16 of 16

Help Creating folders from a column

  1. #1
    Registered User
    Join Date
    08-23-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    8

    Help Creating folders from a column

    I want to create a list of folders based on the entries in column A. I found this code online but always run into an error for line 52 saying "Bad file or name number."

    I am new to excel VBA and don't know how to fix it. Can someone help me resolve this, or find another way to create folders from a column in Excel?

    The debugger shows that this is the line with the error: If Len(Dir(ActiveWorkbook.Path & "" & Rng(r, c), vbDirectory)) = 0 Then

    I've attached the file I'm trying to make folders with.

    Please and thank you!

    Sub MakeFolders()
    Dim Rng As Range
    Dim maxRows, maxCols, r, c As Integer
    Set Rng = Selection
    maxRows = Rng.Rows.Count
    maxCols = Rng.Columns.Count
    For c = 1 To maxCols
    r = 1
    Do While r <= maxRows
    If Len(Dir(ActiveWorkbook.Path & "" & Rng(r, c), vbDirectory)) = 0 Then
    MkDir (ActiveWorkbook.Path & "" & Rng(r, c))
    On Error Resume Next
    End If
    r = r + 1
    Loop
    Next c
    End Sub
    Attached Files Attached Files
    Last edited by pedrojose; 08-24-2022 at 10:28 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Help Creating folders from a column

    "Scripting.FileSystemObject" is a very common approach to these types file/folder process , since it has lots of built in methods to do most of the work for you

    for example "If Len(Dir(ActiveWorkbook.Path & "" & Rng(r, c), vbDirectory)) = 0 " is replaced with simply "filesys.FolderExists(newfolderpath)"

    TOTAL CODE WITH A FileSystemObject
    Please Login or Register  to view this content.
    ACTUAL RESULTS
    ScrShot20.gif

    EXPLAINATION OF CODE
    ScrShot21.gif


    REQUIREMENTS
    1) ROOT_FOLDER must already exist (ie. the root folder that all new folders will be created in )
    2) activesheet column "A" contains folder list
    3) folder list are constants i.e. not formulas


    if this has helped please consider clicking "add reputation" , thx
    Last edited by nimrod1313; 08-24-2022 at 03:36 AM. Reason: fixing grammer

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help Creating folders from a column

    Quote Originally Posted by pedrojose View Post
    The debugger shows that this is the line with the error: If Len(Dir(ActiveWorkbook.Path & "" & Rng(r, c), vbDirectory)) = 0
    Your actual code in attachment in that part is
    Please Login or Register  to view this content.
    and it is working without any issue.

    You don't need "On Error Statement" in that position... useless...

  4. #4
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Help Creating folders from a column

    Also try a solution with the use of Win API functions. Paste the following code into a new standard module.
    Please Login or Register  to view this content.
    Artik

  5. #5
    Registered User
    Join Date
    08-23-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    8

    Re: Help Creating folders from a column

    Quote Originally Posted by jindon View Post
    Your actual code in attachment in that part is
    Please Login or Register  to view this content.
    and it is working without any issue.

    You don't need "On Error Statement" in that position... useless...
    Pardon my noobness, I replaced the code with yours and it still doesn't work. Am I supposed to modify any of the code to include the file directory or something?

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help Creating folders from a column

    No, just don't change the code in your attachment in post #1.

    It is working fine.

  7. #7
    Registered User
    Join Date
    08-23-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    8

    Re: Help Creating folders from a column

    Quote Originally Posted by Artik View Post
    Also try a solution with the use of Win API functions. Paste the following code into a new standard module.
    Please Login or Register  to view this content.
    Artik
    I attached the error message received when trying to run the script...is there anything else I need to to do make it work?
    Compile Error.png

  8. #8
    Registered User
    Join Date
    08-23-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    8

    Re: Help Creating folders from a column

    Here's what I tried to run:

    Sub MakeFolders()
    Dim Rng As Range
    Dim maxRows, maxCols, r, c As Integer
    Set Rng = Selection
    maxRows = Rng.Rows.Count
    maxCols = Rng.Columns.Count
    For c = 1 To maxCols
    r = 1
    Do While r <= maxRows
    If Len(Dir(ActiveWorkbook.Path & "" & Rng(r, c), vbDirectory)) = 0 Then
    MkDir (ActiveWorkbook.Path & "" & Rng(r, c))
    End If
    r = r + 1
    Loop
    Next c
    End Sub

    Still get the Error 52 popup. I noticed your suggestion has a "" where the code I tried only shows "". Not sure if that's making the difference...

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help Creating folders from a column

    That's not what you have in your attachment.
    Here's the actual code in your workbook in Module2
    Please Login or Register  to view this content.
    As you can see it has path separator \, while you changed it "".
    Last edited by jindon; 08-24-2022 at 11:08 AM.

  10. #10
    Registered User
    Join Date
    08-23-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    8

    Re: Help Creating folders from a column

    I clearly am struggling here: I copied and pasted the code as-is from your prev post, which includes the path separators \, and still get the run-time error 52. I'm hoping your solution will work because it seems the simplest, though since I have not used VBA before I might be the hold-up here.

  11. #11
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Help Creating folders from a column

    In the case of my code, try replacing the API function declaration to:
    Please Login or Register  to view this content.
    Artik

  12. #12
    Registered User
    Join Date
    08-23-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    8

    Re: Help Creating folders from a column

    Quote Originally Posted by Artik View Post
    In the case of my code, try replacing the API function declaration to:
    Please Login or Register  to view this content.
    Artik
    I attached a different error that popped up this time. I'm wondering if it has something to do with the folder names themselves?
    Folder Error.png

  13. #13
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Help Creating folders from a column

    Is folder 2870971 the first in the list? Or any one in a row?

    Artik

  14. #14
    Registered User
    Join Date
    08-23-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    8

    Re: Help Creating folders from a column

    Quote Originally Posted by Artik View Post
    Is folder 2870971 the first in the list? Or any one in a row?

    Artik
    It's the first in the first one.

  15. #15
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Help Creating folders from a column

    It looks like you don't have permissions to the root folder.
    If the Excel file was opened from a web location (e.g. OneDrive), then our codes will not work.

    Artik

  16. #16
    Registered User
    Join Date
    08-23-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    8

    Re: Help Creating folders from a column

    Quote Originally Posted by Artik View Post
    It looks like you don't have permissions to the root folder.
    If the Excel file was opened from a web location (e.g. OneDrive), then our codes will not work.

    Artik
    That was it! I saved outside of the web location, ran the script, and it WORKED! Thank you Artik, you have my kudos.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Creating folders and sub-folders
    By Elainefish in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2019, 02:41 AM
  2. [SOLVED] Creating Folders and Subfolders based off Column A and B
    By Sdthaman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-12-2015, 06:41 PM
  3. Creating folders folders need to set permissions
    By overlord6920 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-14-2014, 07:08 PM
  4. [SOLVED] Creating Folders and Sub Folders
    By qoios in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-30-2012, 12:57 PM
  5. Need a macro for creating folders with their names given in a column
    By Dileep6661 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2012, 09:35 AM
  6. probems creating folders: "......... folders already existed"
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2010, 03:04 PM
  7. Creating Folders.
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-08-2010, 01:23 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1