Saving .xls on local machine without Admin priv.

[EAnton781]

I have a procedure that saves a copy of an Excel 2003 .xls file. It is hard coded to save as C:/DataFile.xls

The problem is some users at my company are not Administrators on their local machine and therefore do not have permission to save directly to C:

Is there another standard location on the local disk that is better fitted for this kind of save procedure regardless of the users permissions?

I'd like to keep it a simple hardcoded path and avoid having to create some kind of custom string (i.e. documents and settings/"UserName"/)

 

[kpark91]

Hi, EAnton781.

You can find the name of the client computer's user name by using Environ function.

Dim strUserName As String
strUserName = Environ("USERNAME")

Moreover, if they use windows OS higher than xp (I think), you will be able to save to
C:\Users\Public folder.

If you want to give the users freedom to pick a folder where they could save files into, you might want to look into msoFolderDialogFilePicker (google it up)

 

[EAnton781]

Thanks K,

We're still an XP shop for the most part.

Would the following be an acceptable location to put it? Do you know if this a standard location in Windows 7 as well?

"C:\Documents and Settings\UserName\Local Settings\Temp"

 

[kpark91]

Hi Eanton781,

Well, Documents and Settings folder is inaccessible in Windows NT 6.1, which is the platform windows 7 use. You can find the specifics of operating system platforms in this site:
http://en.wikipedia.org/wiki/Windows_NT

You can find the alternative path for windows 7 by using this:
C:\Users\"UserName"\AppData\Local\Temp
http://www.byteblocks.com/post/2009/08/10/Documents-and-Settings-folder-in-Windows-7.aspx

I am currently using 32-bit Windows Vista (I left my 64-bit Windows 7 at home) so I don't have a way of verifying but I think that path should be ok.

If that path is not existent in windows XP,
I think you have to determine your version of Windows and alter the path accordingly:
I have not tested this code but this code claims that it will retrieve your OS version.
http://access.mvps.org/access/api/api0055.htm

 

[EAnton781]

I think I may have found a better approach. This should find the active users temp directory regardless of Windows version.

It looks good on XP. I have Windows 7 x64 running at home, I will try it tonight.

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260
Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
    TempPath = TempPath & "/DataFile.XLS"
End Function
Sub TmpPath()
    MsgBox TempPath
End Sub

Props: http://www.vbforums.com/showthread.php?t=544298

 

[kpark91]

Awesome. Thanks for the update.