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

    Read “My Documents” Path And Use Result


    Problem:


    Note:


    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.


    Choppork


    1100 CDT
    September 7, 2006

    Re: Read “My Documents” Path And Use Result


    Try the following:


    Code
    filepath = ThisWorkbook.Path
MkDir (filepath & "\Cat")
ActiveWorkbook.SaveAs Filename:=filepath & "\Cat\New_File.xls"

    Re: Read “My Documents” Path And Use Result


    Hi,
    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

    Code
    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.

    Code
    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.
    Stefan

    Re: Read “My Documents” Path And Use Result


    Esteemed Gentlemen;


    THANKS!!!!!!


    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!

Register Yourself Login