VBA Write new file to Program Files folder

Question

i have an xlsm file which is being used by a lot of users, i added an update function which needs to check on a server if a new update of the xlsm file is available, and if its available it needs to download the file, and then overwrite the existing file, some how i get an error write to file failed error 3004 can anyone help me with it?

let me explain my code; the client xlsm file has a check for new update button, when user clicks that button, here is what happen,

Private Sub CommandButton5_Click()
Dim Answer As VbMsgBoxResult, N%, MyFile$

Answer = MsgBox("1) You need to be on-line to update" & vbLf & _
"2) The update may take a few minutes" & vbLf & _
"3) Please do not interrupt the process once started" & vbLf & _
"" & vbLf & _
"SEARCH FOR UPDATE?", vbYesNo, "Update?")
If Answer = vbNo Then Exit Sub

 'otherwise - carry on
Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled


On Error GoTo ErrorProcedure

Application.Workbooks.Open ("http://www.mysite.com/Download/Update.xlsm")

 'The book on the site opens and you can do whatever you
 'want now (note that the remote book is "Read Only") - in
 'this particular case a workbook_Open event now triggers
 'a procedure to export the new file to the PC

ErrorProcedure:
MsgBox Err.Description
End Sub

and then the update.xlsm from the server opens, and here is the code;

Private Sub workbook_open()


Dim localfile As Date
Dim newfile As Date
localfile = FileDateTime("C:\Documents and Settings\localhost\Desktop\sample.xlsm")
newfile = "6/6/2013 4:00"
If DateDiff("s", localfile, newfile) > 0 Then

MsgBox "its closed"

Application.StatusBar = "contacting the download"

Dim myURL As String
myURL = "http://www.mysite.com/Download/sample.xlsm"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

Application.StatusBar = "waiting for the response"

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
Application.DisplayAlerts = False
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
oStream.SaveToFile ("C:\Documents and Settings\localhost\Desktop\sample.xlsm")
oStream.Close
End If


MsgBox "Update Completed"
Application.StatusBar = ""
Windows("Update.xlsm").Activate
ActiveWindow.Close
Application.DisplayAlerts = True
Else
MsgBox "There is no New Update"
Application.StatusBar = ""
End If
End Sub

Answer 1

Writing to %PROGRAMFILES% requires administrative privileges on Windows Vista and above (or XP when running as a limited user). Applications are not supposed to store their data there, and that information has been published for more than a decade now.

A good reference here for information about where to store your application's data is in Does Microsoft have a best practices document regarding the storage of App Data vs User Data on different Windows Platforms?

However, your question is confusing, because you refer to Program Files folder in your subject, but your code uses a hard-coded path to C:\Documents and Settings\localhost\Desktop, which is not the same thing. If that's the actual problem, it's probably because of two issues:

1. You've hard-coded in C:\Documents and Settings, which is no longer the proper location for user data since Windows Vista was released. You should be using the WinAPI functions that are available to find that folder instead. (Search here at SO for [winapi] SHGetFolderLocation.)

2. You've hard-coded in the location for the user's Desktop folder, which once again might not be where you think it should be. The same WinAPI function you locate with the search above should be used to find the desktop folder.

3. It's highly unlikely that localhost has a Desktop folder, even if you were looking in the right place for user documents. localhost is an alias for the IP address 127.0.0.1, and I've never known of a desktop folder for an IP address alias. localhost is not a user on the local machine, and only users can have desktop folders.