Hi!

I'm new to excel but kinda familiar with VBA for Access. I would like to
copy stored shortcuts(.lnk) to the users desktop. I have the following code.
I was trying the FileCopy but I'm pretty sure I should be using CopyFile.
What do I need to make the FileSystemObject work?

Private Sub cmdCreateDesktopShortcut_Click()

'Dim FSO As FileSystemObject
Dim strUserDesktop
Dim strDrive As String
Dim strTempPath As String
Dim strSrcePath As String

'Set FSO = New FileSystemObject

'Set up Source string

strSrcePath = "bwes.net\Barberton file
shares\DocControlPLM\BatchLoadFiles\ScriptsAndZips\Shortcuts\Consolidated -
Approve.lnk"

'Set up Destination string

strDrive = "D:"
strTempPath = "\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"

strUserDesktop = Dir(strDrive & strTempPath)

Kill strUserDesktop

FileCopy strSrcePath, strUserDesktop

End Sub

filecopy should work. You have the following error

from
strDrive = "D:"
strTempPath = "\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"

strUserDesktop = Dir(strDrive & strTempPath)

Kill strUserDesktop

FileCopy strSrcePath, strUserDesktop
to
strDrive = "D:"
strTempPath = "\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"

Kill strTempPath & "*.*"

FileCopy strSrcePath, strTempPath & "Approve.lnk"



DIR will remove the PATH name from the filename giving you just the file name

I would change the envirnomenttal settings you are using since not
everybody will be on drive D:.

first I would look at the envirnomental setting at your company by doing the
following:
1) From start button: select RUN. In the run box type cmd.exe the OK
2) From command prompt type SET. This will give you the list of
envirnomental settings.

from
strDrive = "D:"
strTempPath = "\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"

to

strTempPath = environ("USERPROFILE") & "\Desktop\"




"Michelle" wrote:

> Hi!
>
> I'm new to excel but kinda familiar with VBA for Access. I would like to
> copy stored shortcuts(.lnk) to the users desktop. I have the following code.
> I was trying the FileCopy but I'm pretty sure I should be using CopyFile.
> What do I need to make the FileSystemObject work?
>
> Private Sub cmdCreateDesktopShortcut_Click()
>
> 'Dim FSO As FileSystemObject
> Dim strUserDesktop
> Dim strDrive As String
> Dim strTempPath As String
> Dim strSrcePath As String
>
> 'Set FSO = New FileSystemObject
>
> 'Set up Source string
>
> strSrcePath = "bwes.net\Barberton file
> shares\DocControlPLM\BatchLoadFiles\ScriptsAndZips\Shortcuts\Consolidated -
> Approve.lnk"
>
> 'Set up Destination string
>
> strDrive = "D:"
> strTempPath = "\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
>
> strUserDesktop = Dir(strDrive & strTempPath)
>
> Kill strUserDesktop
>
> FileCopy strSrcePath, strUserDesktop
>
> End Sub
>
>

Joel

Thanks. I made the changes that you suggested but I get a run-time error
"76" Path not found.

Is there a problem with the way it sees the shortcut "Consolidated -
Approve.lnk"? The way its stored on the network is - (star icon)Consolidated
- Approve No .lnk suffix.
I took the Kill statement out. Don't want to delete strTempPath before I
have a chance to use it. If I put my cursor over the variables they show the
paths exactly the way they should.

"Joel" wrote:

> filecopy should work. You have the following error
>
> from
> strDrive = "D:"
> strTempPath = "\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
>
> strUserDesktop = Dir(strDrive & strTempPath)
>
> Kill strUserDesktop
>
> FileCopy strSrcePath, strUserDesktop
> to
> strDrive = "D:"
> strTempPath = "\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
>
> Kill strTempPath & "*.*"
>
> FileCopy strSrcePath, strTempPath & "Approve.lnk"
>
>
>
> DIR will remove the PATH name from the filename giving you just the file name
>
> I would change the envirnomenttal settings you are using since not
> everybody will be on drive D:.
>
> first I would look at the envirnomental setting at your company by doing the
> following:
> 1) From start button: select RUN. In the run box type cmd.exe the OK
> 2) From command prompt type SET. This will give you the list of
> envirnomental settings.
>
> from
> strDrive = "D:"
> strTempPath = "\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
>
> to
>
> strTempPath = environ("USERPROFILE") & "\Desktop\"
>
>
>
>
> "Michelle" wrote:
>
> > Hi!
> >
> > I'm new to excel but kinda familiar with VBA for Access. I would like to
> > copy stored shortcuts(.lnk) to the users desktop. I have the following code.
> > I was trying the FileCopy but I'm pretty sure I should be using CopyFile.
> > What do I need to make the FileSystemObject work?
> >
> > Private Sub cmdCreateDesktopShortcut_Click()
> >
> > 'Dim FSO As FileSystemObject
> > Dim strUserDesktop
> > Dim strDrive As String
> > Dim strTempPath As String
> > Dim strSrcePath As String
> >
> > 'Set FSO = New FileSystemObject
> >
> > 'Set up Source string
> >
> > strSrcePath = "bwes.net\Barberton file
> > shares\DocControlPLM\BatchLoadFiles\ScriptsAndZips\Shortcuts\Consolidated -
> > Approve.lnk"
> >
> > 'Set up Destination string
> >
> > strDrive = "D:"
> > strTempPath = "\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
> >
> > strUserDesktop = Dir(strDrive & strTempPath)
> >
> > Kill strUserDesktop
> >
> > FileCopy strSrcePath, strUserDesktop
> >
> > End Sub
> >
> >