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.
September 7, 2006