My Documents folder - How to add into Macro

[Muttdog117]

I have created a program in Excell that uses several different Macros. Some of them need to either save data or retrieve data to the users My documents folder. Currently when I give the file to a new person to use, they have to go in to each macro manually and put in their My Documents folder address. This is time consuming and fraught with mistakes. Also, I have to save a copy of the program after they get it installed and when I make revisions, I have to do it to all the files and send to each user. I would prefer to have a master file that I can update and then send to everyone in one step. Is there a way to get my programs to automatically find the active users' my documents folder automatically so whenever a new person opens the file on their computer, it will automatically figure it out ?

 

[John_w]

Try this:

Sub test()
    Dim MyDocumentsFolder As String
    MyDocumentsFolder = Get_SpecialFolderPath("MyDocuments")
    MsgBox MyDocumentsFolder
End Sub

Private Function Get_SpecialFolderPath(strSpecialFolder) As String
    With CreateObject("WScript.Shell")
        Get_SpecialFolderPath = .SpecialFolders(strSpecialFolder)
    End With
End Function

For other 'special' folders, e.g. the Desktop, see http://msdn.microsoft.com/en-us/library/0ea7b5xe(VS.85).aspx

 

[Muttdog117]

Thank you for the advice. I should have prefaced that I am not in any way an Excel programmer. I spent weeks/months combing this forum and others to figure out what I needed to do (and learned something I think). Having said that, I do not understand where to insert the code you provided me. Here is an example of one of my Macros, can you tell me where I should put that code you gave me:

Sub Create_Parts_Search_File()
'
' Create_Parts_Search_File Macro
' Macro recorded 6/30/2010 by stan
'
'
Range("B14").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Stan\My Documents\PartSearchSkuImport.txt" _
, FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close
Range("A8").Select
End Sub

 

[John_w]

Like this:

Sub Create_Parts_Search_File()
    
    Range("B14").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Dim MyDocumentsFolder As String
    MyDocumentsFolder = Get_SpecialFolderPath("MyDocuments")

    ActiveWorkbook.SaveAs Filename:=MyDocumentsFolder & "\PartSearchSkuImport.txt", FileFormat:=xlText, CreateBackup:=False
    ActiveWindow.Close
    Range("A8").Select
End Sub


Private Function Get_SpecialFolderPath(strSpecialFolder) As String
    With CreateObject("WScript.Shell")
        Get_SpecialFolderPath = .SpecialFolders(strSpecialFolder)
    End With
End Function

 

[Muttdog117]

Awesome, thank you. I will try this later today and see how it works. Another successful posting to Mr. Excel. You guys are incredible.