VBA: Save file in different environments

[BIGTONE559]

I've seen many examples of this. However I'm curious of an effective, yet simple way of saving an excel file in a users "My Documents" Folder. Keeping in mind the environment may change. Some Users use Win 7 and others XP

Cheers!

 

[jbeaucaire]

A snippet I have stored away for things such as this to get me started, hopefully works for you:

Function GetSpecialFolderNames()
Dim objFolders As Object
Set objFolders = CreateObject("WScript.Shell").SpecialFolders

    MsgBox objFolders("MyDocuments")
    MsgBox objFolders("Desktop")
    MsgBox objFolders("AllUsersDesktop")
    MsgBox objFolders("SendTo")
    MsgBox objFolders("StartMenu")
    MsgBox objFolders("Recent")
    MsgBox objFolders("Favorites")
    MsgBox objFolders("MyDocuments")

End Function

 

[BIGTONE559]

A snippet I have stored away for things such as this to get me started, hopefully works for you:

Function GetSpecialFolderNames()
Dim objFolders As Object
Set objFolders = CreateObject("WScript.Shell").SpecialFolders

    MsgBox objFolders("MyDocuments")
    MsgBox objFolders("Desktop")
    MsgBox objFolders("AllUsersDesktop")
    MsgBox objFolders("SendTo")
    MsgBox objFolders("StartMenu")
    MsgBox objFolders("Recent")
    MsgBox objFolders("Favorites")
    MsgBox objFolders("MyDocuments")

End Function

jbeaucaire,

this is a great start for identifying the locations. Now how would i refer to these locations in code. Give me an example of using this code in a macro.

For example objfolders("MyDocuments") gives the location of the respective users my document folder.

if i'm using the following function

activeworkbook.saveas how could i incorporate the file path?

 

[jbeaucaire]

We convert the function into one that accepts a parameter being fed to it by another macro.

Option Explicit

Function GetSpecialFolderNames(ObjProperty As String) As String
Dim objFolders As Object
Set objFolders = CreateObject("WScript.Shell").SpecialFolders

    GetSpecialFolderNames = objFolders(ObjProperty)
    
End Function

Then your other code can feed the "MyDocuments" string (or any of the others given before) to the function and get the answer back, then string that together with other strings to create the full path/file instruction.

Sub test()

    ActiveWorkbook.SaveAs GetSpecialFolderNames("MyDocuments") & "\MyFile.xls"

End Sub

Sub test()

ActiveWorkbook.SaveAs GetSpecialFolderNames("MyDocuments") & "\" & "MyFile.xls"

End Sub

 

[jbeaucaire]

Now that I look at it again, I realize we can really eliminate the function and just do it all in the SAVEAS command, too:

Sub test()

    ActiveWorkbook.SaveAs CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\MyFile.xls"

End Sub

 

[mikerickson]

If you are going to make it cross platform, you could use Application.PathSeparator rather than "/" to accomidate Macs.

 

[BIGTONE559]

If you are going to make it cross platform, you could use Application.PathSeparator rather than "/" to accomidate Macs.

Thanks for the heads up mike. Have not had a need to perform cross platform macros but i'm certain this will be very important in the future.

Now that I look at it again, I realize we can really eliminate the function and just do it all in the SAVEAS command, too:

Code:

Sub test() ActiveWorkbook.SaveAs CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\MyFile.xls" End Sub</pre>

jbeaucaire:

How could i use the function that you provided to reference a network drive or something of that nature. I'm extremely curious to see how this would work. I'm not very familiar with the object property and how to use it so if you don't mind explaining the whole Wscript.shell i'd appreciate it.

 

[BIGTONE559]

and as a note. I was able to follow your instructions and save documents on multiple workstations with different environments. So i appreciate it

 

[BIGTONE559]

jbeaucaire:

How could i use the function that you provided to reference a network drive or something of that nature. I'm extremely curious to see how this would work. I'm not very familiar with the object property and how to use it so if you don't mind explaining the whole Wscript.shell i'd appreciate it.[/QUOTE]


bump

 

[jbeaucaire]

Based on this original snippet which demonstrates all the uses I know for this method:

Function GetSpecialFolderNames()
Dim objFolders As Object
Set objFolders = CreateObject("WScript.Shell").SpecialFolders

    MsgBox objFolders("MyDocuments")
    MsgBox objFolders("Desktop")
    MsgBox objFolders("AllUsersDesktop")
    MsgBox objFolders("SendTo")
    MsgBox objFolders("StartMenu")
    MsgBox objFolders("Recent")
    MsgBox objFolders("Favorites")
    MsgBox objFolders("MyDocuments")

End Function

....all of those uses are for referencing your local computer default folder locations. I can't give you technical background on things like wscript.shell, but I do know how to use it... which is demonstrated above. I don't believe this would work for network access since your local computer "default folders" would never be a network folder, for the most part.

Perhaps I'm wrong on that last point, something I couldn't test for you, but you might be able to if you truly have "My Documents" or "Favorites" folder has been configured by you as a network folder.