+ Reply to Thread
Results 1 to 4 of 4

Create folder structure based on values in Cells

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Create folder structure based on values in Cells

    I'll try to explain as best i can so as not to be confusing. i have a macro that will currently save my workbook file and give it a name based on the value in a cell. In my current macro i have the folder i want the workbook file copied to typed in. Here is the next level i need to go with the macro. i want the macro to automatically create the folders i need based on the value it finds in certain cells. The base folder i work out from is C:\Quotes

    Example: i would need the macro to create the folder structure based on the values it finds in the following cells . start with A1 and finish with A3.

    Note: ( in the attached file the cells are different i just used these cells to basically show how it needs to work.)

    A1 = acme products
    A2 = 2011
    A3 = 5-Jan

    i would need to the macro to create the following folder structure.. and copy my workbook file into the 5-Jan folder.

    C:\Quotes\acme products\2011\5-Jan

    keep in mind C:Quotes already exists and always will. the macro just needs to create the acme products\2011\5-Jan folders off of C:\Quotes

    it will need to do error checking to see if the folders(s) already exist etc...

    i attached a sample workbook file with my current macro so you can see the code i am using right now. I'll answer any questions that need be ... Thanks ,
    Attached Files Attached Files
    Last edited by Traymond; 01-05-2011 at 03:01 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Create folder structure based on values in Cells

    Like so:
    Sub SvMe()
    'Saves filename as value of A1 plus the current date
    Dim svPath As String
    Dim newFile As String, fName As String
        
    'assemble save path string
        svPath = "C:\QUOTES\" & Range("O3").Text & "\" & Range("O4").Text & "\"
    
    'create directories if needed
        Call MakeFolders(svPath)
        
    'Don't use "/" in date, invalid syntax
        fName = Range("O2").Text
    'Change the date format to whatever you'd like, but make sure it's in quotes
        newFile = fName & "_" & Format$(Date, "mm-dd-yyyy hh mm AMPM")
    
    'Save it
        ActiveWorkbook.SaveAs Filename:=svPath & newFile
    End Sub
    
    Function MakeFolders(MyStr As String)
    'Author:    Jerry Beaucaire
    'Date:      7/14/2010
    'Summary:   Create directories and subdirectories based
    '           on the text strings fed to the function
    '           This version is to be called by other macros
    Dim MyArr   As Variant
    Dim pNum    As Long
    Dim pBuf    As String
    
    On Error Resume Next
      
        MyArr = Split(MyStr, "\")
        pBuf = MyArr(LBound(MyArr)) & "\"
        For pNum = LBound(MyArr) + 1 To UBound(MyArr)
            pBuf = pBuf & MyArr(pNum) & "\"
            MkDir pBuf
        Next pNum
    
    End Function

    This is based on the code published here:
    Make Folders and Subfolders
    Attached Files Attached Files
    Last edited by JBeaucaire; 01-05-2011 at 02:38 PM. Reason: Added UDF to create all folders needed.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Re: Create folder structure based on values in Cells

    This looks like what i need ....
    Last edited by Traymond; 01-05-2011 at 03:00 PM.

  4. #4
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Re: Create folder structure based on values in Cells

    It works perfectly !!!!!! Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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