Macro using Save As Problem

**mbmadiw** · 07-07-2006, 08:48 AM

I recorded a macro that will save the file under another name in My Documents, then close it. It works great, but only on my computer.

The problem is that when you're using the file on another computer, the macro won't work because the user has a different login name. The Save destination includes my username, which is obviously different on another computer. C:\Documents and Settings\Tech\My Documents\Old Timesheet.xls

Is there a way around this, other than saving it in a parent directory of the username?

Thanks!

Dave Peterson · 07-07-2006, 12:10 PM

I think I would ask the user where they wanted to save the file--using
application.getsaveasfilename.

If you're trying to save to "my documents" or the user's desktop, maybe this'll
give you an alternative:

Option Explicit
Sub testme()
Dim myDocumentsPath As String
Dim myDeskTopPath As String
Dim WSHShell As Object

Set WSHShell = CreateObject("WScript.Shell")
myDocumentsPath = WSHShell.SpecialFolders.Item("mydocuments")
myDeskTopPath = WSHShell.SpecialFolders.item("Desktop")

MsgBox myDocumentsPath & vbLf & myDeskTopPath

End Sub

mbmadiw wrote:
>
> I recorded a macro that will save the file under another name, then
> close it. It works great, but only on my computer.
>
> The problem is that when you're using the file on another computer, the
> macro won't work because the user has a different login name. The Save
> destination includes my username, which is obviously different on
> another computer. Is there a way around this?
>
> Thanks!
>
> --
> mbmadiw
> ------------------------------------------------------------------------
> mbmadiw's Profile: http://www.excelforum.com/member.php...o&userid=36153
> View this thread: http://www.excelforum.com/showthread...hreadid=559288

--

Dave Peterson