Macro: Create Directory Based on Active Workbook and Save to it

  • Read “My Documents” Path And Use Result



    Typical user OS will be Windows XP Pro / Win 2K
    Excel version : 97 / 2002 / 2003

    1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.

    2. Per customer request, VBA application needs to save extracted files for future use.

    3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My Documents\Cat”.

    4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:

    Dim filepath As String

    filepath = ThisWorkbook.Path

    As an example – this code would produce a string definition of “filepath” – such as the following:

    D:\Documents and Settings\username\My Documents\Cat

    5. When attempting to use that string (filepath) with the make directory function is where I am stuck – i.e. –

    MakeDirectory " filepath_Cat\Dog\"
    MakeDirectory " filepath_Cat\Dog\Bird\"

    etc – does not work.

    What I need to do – with the above criteria – is to create the following subdirectories:

    D:\Documents and Settings\username\My Documents\Cat\Dog

    D:\Documents and Settings\username\My Documents\Cat\Dog\Bird

    6. It is also my assumption that when we get this solved, I can use the same approach to specify the file save location in the created subdirectories – i.e. -

    ActiveWorkbook.SaveAs Filename:= "filepath_Cat\Dog\Bird\New_File.xls"

    As always – THANKS in advance to this esteemed group of professionals for any/all assistance provided.


    1100 CDT
    September 7, 2006

  • Re: Read “My Documents” Path And Use Result

    Try the following:

    filepath = ThisWorkbook.Path
    MkDir (filepath & "\Cat")
    ActiveWorkbook.SaveAs Filename:=filepath & "\Cat\New_File.xls"
  • Re: Read “My Documents” Path And Use Result

    While i dont fall in that group of "esteemed group of professionals" :-), maybe this msg tread will be of help along the way?
    As for the ActiveWorkbook.Path, you could use this

    ActiveWorkbook.SaveAs Filename:= _
            ActiveWorkbook.Path & "\New Folder\FileName.xls"

    I think that i'd use the direct path without assuming that the ActiveWorkbook is always in the "right" place, mine is like this.

    ActiveWorkbook.SaveAs Filename:= _
            "C:\Documents and Settings\UserName\My Documents\New Folder\FileName2.xls"

    I suppose you could use the Macro Recorder (Tools| Macro| Record New Macro) along the way as well. - I'm sure you'll get a bunch more/different suggestions too.

  • Re: Read “My Documents” Path And Use Result

    Esteemed Gentlemen;


    It is easy - when - you know the tricks!

    As always - a great board!

    Thanks to All

  • Re: Read “My Documents” Path And Use Result

    And it would be better if members followed the Rules particularly on Thread Title, as laid out in the Forum Rules. The use of macro at he beginning of your title is unnecessary & weakens the Search for someone with a similar problem!

  • Re: Read “My Documents” Path And Use Result

    That was me Roy :) The original title was Read “My Documents” Path And Use Result . I don't mind the use of the words Code or Macro (helps serachers determine if it's manual or auto without opening the Thread). Excel and VBA are simply not needed though.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!