Hi
I have a set of Excel files sitting on a shared network drive which can only
be accessed through a file managing system developed with MS Access.

The users need the ability to save a copy of the file on their desktop. I
did it by using following code and it works fine.
Set WshShell = CreateObject("WScript.Shell")
ThisWorkbook.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\" &
ThisWorkbook.Name, password:=""

But our Information System department decided to execute the MS Access file
manager through a Citrix server. Everything else works fine, but
automatically saving of the copy on the user's desktop. I know this is
something to do with the file path through the Citrix server, however, I
don't know a method to get set the path automatically to the users desktop
and save a copy there.

Can anyone help me with this?
Thank you in advance
Toyfixer

See if this macro gets you what you need. All the envirnomental variables
can be seen by doing the following

Start - Run - cmd.exe (this will open a DOS window)

In DOS window type set. Any parameter that is returned from the SET command
can be put into the code below

Sub test()
apdata = Environ("APPDATA")

End Sub

I think you need a mapped drive that contains the drive letter such as H:\.

"ToyFixer" wrote:

Hi
I have a set of Excel files sitting on a shared network drive which can only
be accessed through a file managing system developed with MS Access.

The users need the ability to save a copy of the file on their desktop. I
did it by using following code and it works fine.
Set WshShell = CreateObject("WScript.Shell")
ThisWorkbook.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\" &
ThisWorkbook.Name, password:=""

But our Information System department decided to execute the MS Access file
manager through a Citrix server. Everything else works fine, but
automatically saving of the copy on the user's desktop. I know this is
something to do with the file path through the Citrix server, however, I
don't know a method to get set the path automatically to the users desktop
and save a copy there.

Can anyone help me with this?
Thank you in advance
Toyfixer

Hi Joel,
I am trying to get to my system administrator to do this for me. They have
disabled access to Start - Run - cmd.exe. Therefore I have to wait for him to
get the settings for me.

I don't know how to progress from there. How do I use a mapped drive to save
a copy on the desktop? plese help me a bit further on this.
Thank you
Toyfixer.

"Joel" wrote:

See if this macro gets you what you need. All the envirnomental variables
can be seen by doing the following

Start - Run - cmd.exe (this will open a DOS window)

In DOS window type set. Any parameter that is returned from the SET command
can be put into the code below

Sub test()
apdata = Environ("APPDATA")

End Sub

I think you need a mapped drive that contains the drive letter such as H:\.

"ToyFixer" wrote:

Hi
I have a set of Excel files sitting on a shared network drive which can only
be accessed through a file managing system developed with MS Access.

The users need the ability to save a copy of the file on their desktop. I
did it by using following code and it works fine.
Set WshShell = CreateObject("WScript.Shell")
ThisWorkbook.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\" &
ThisWorkbook.Name, password:=""

But our Information System department decided to execute the MS Access file
manager through a Citrix server. Everything else works fine, but
automatically saving of the copy on the user's desktop. I know this is
something to do with the file path through the Citrix server, however, I
don't know a method to get set the path automatically to the users desktop
and save a copy there.

Can anyone help me with this?
Thank you in advance
Toyfixer

A mapped drive will have a drive letter like H:. You can then save the file
like any other file.

This code will do the same as the cmd.exe
Sub test()
Number = 1
Do
returnstring = Environ(Number)
Range("A" & Number) = returnstring
Number = Number + 1
Loop While returnstring < ""
End Sub

You should open your window explorer and see where you MIS department has
setup the desk top. I'm not sure if it is going to be the same for each
user. That is why you "MAY" need to use the environment variables.

My Desktop at work is set to the follwing
C:\Documents and Settings\joel\Desktop

Where C: is my local drive. I also have a network drive which is H:. Your
network admin can setup the Documents and Settings to be the PC local drive
(c:) or the users Network drive (H:).

Once you find where the Desktop is located you can create a string which cna
be used to save the file on the desktop.

filename = Environ("USERPROFILE) & "Desktop\" & yourfilename

I don't know what environmental variable are set up by your Admin to
determine what the correct path will be. Try my macro and see if it helps.



"ToyFixer" wrote:

Hi Joel,
I am trying to get to my system administrator to do this for me. They have
disabled access to Start - Run - cmd.exe. Therefore I have to wait for him to
get the settings for me.

I don't know how to progress from there. How do I use a mapped drive to save
a copy on the desktop? plese help me a bit further on this.
Thank you
Toyfixer.

"Joel" wrote:

See if this macro gets you what you need. All the envirnomental variables
can be seen by doing the following

Start - Run - cmd.exe (this will open a DOS window)

In DOS window type set. Any parameter that is returned from the SET command
can be put into the code below

Sub test()
apdata = Environ("APPDATA")

End Sub

I think you need a mapped drive that contains the drive letter such as H:\.

"ToyFixer" wrote:

Hi
I have a set of Excel files sitting on a shared network drive which can only
be accessed through a file managing system developed with MS Access.

The users need the ability to save a copy of the file on their desktop. I
did it by using following code and it works fine.
Set WshShell = CreateObject("WScript.Shell")
ThisWorkbook.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\" &
ThisWorkbook.Name, password:=""

But our Information System department decided to execute the MS Access file
manager through a Citrix server. Everything else works fine, but
automatically saving of the copy on the user's desktop. I know this is
something to do with the file path through the Citrix server, however, I
don't know a method to get set the path automatically to the users desktop
and save a copy there.

Can anyone help me with this?
Thank you in advance
Toyfixer