I need help with the file path script

[Erays]

I woul like help with the script to save an activesheet to a specified folder on the desktop for windows 2000 with multiple user profiles so that the excel workbook works with each profile.

Sub SaveToFileTLA()
'Variable declaration
Dim oApp As Object, _
WB As Workbook, _
FileName As String
Dim FName As String
Dim FPath As String
'Turns off screen updating
Application.ScreenUpdating = False
'Makes a copy of the active sheet and save it to
ActiveSheet.Copy
Set WB = ActiveWorkbook
FPath = "d:\documents and settings\clc6b\Desktop\Monthly Reports"
FName = Cells(6, 2).Value & " " & Cells(5, 2).Value & " " & Cells(5, 5).Value & " TLA.xls"
WB.SaveAs FileName:=FPath & "\" & FName
WB.Close SaveChanges:=False
'Restores screen updating and release Outlook
Application.ScreenUpdating = True
End Sub

[austenr]

If I understand you correctly this should work:

ThisWorkbook.SaveAs (MyPath & "\" & MyName)

OOPS...Should be ThisWorksheet.SaveAs

[Erays]

here is my file path d:\documents and settings\clc6b\Desktop\Monthly Reports
the clc6b is the user profile on windows 2000 pro.

[mvidas]

Hi Erays,

Here is a function that will return the desktop's address for the current user:

Public Function DesktopAddress() As String
 Set T = CreateObject("WScript.Shell")
 DesktopAddress = T.SpecialFolders("Desktop")
 Set T = Nothing
End Function

If you put that in the same module as your code above, just change the line

FPath = "d:\documents and settings\clc6b\Desktop\Monthly Reports"

to

FPath = DesktopAddress & "\Monthly Reports"

Matt

[Erays]

where would i insert the code

Public Function DesktopAddress() As String 
    Set T = CreateObject("WScript.Shell") 
    DesktopAddress = T.SpecialFolders("Desktop") 
    Set T = Nothing 
End Function 

[Zack Barresse]

In any Standard Module.

Quick question: will these user's be logged in when saving? Or is the username only found in a cell value?

[Erays]

This is creating an error message

Set T = CreateObject("WScript.Shell")

[Zack Barresse]

What about ..

Public Function DesktopAddress() As String
    DesktopAddress = CreateObject("WScript.Shell").SpecialFolders( _
        "Desktop") & Application.PathSeparator
End Function

.. found inhttp://www.vbaexpress.com/kb/getarticle.php?kb_id=216

[mvidas]

As Zack said, just put it in a standard module in the same project as your other code. You may have to insert

Dim T As Object

as the first line in the function. Zack's fix should work too

[Erays]

I get a runtime error 429 ActiveX cannot create object

[mvidas]

Not sure why you'd get that error, any ideas Zack?

Erays,
If your users will have the same setup as you, you could also do

FPath = Environ("userprofile") & "\Desktop\Monthly Reports"

[Zack Barresse]

Yeah, the next thing I'd do is hardcode for all versions you may come across. Not sure why you're receiving such an error. Will have XL 2000 to test with in a few hours later tonight.

[mvidas]

I've got xl2000 at home and at work (win2k at work, xp at home), and it works fine both places for me

[Erays]

This works great

Sub SaveToFiletlabobo()
'Variable declaration
Dim oApp As Object, _
WB As Workbook, _
FileName As String
Dim FName As String
Dim FPath As String
'Turns off screen updating
Application.ScreenUpdating = False
'Makes a copy of the active sheet and save it to
ActiveSheet.Copy
Set WB = ActiveWorkbook
FPath = Environ("userprofile") & "\Desktop\Your Folder"
FName = Cells(6, 2).Value & " " & Cells(5, 2).Value & " " & Cells(5, 5).Value & " TLA.xls"
WB.SaveAs FileName:=FPath & "\" & FName
WB.Close SaveChanges:=False
'Restores screen updating and release Outlook
Application.ScreenUpdating = True
End Sub

[mvidas]

Glad to hear it. You can mark this solved by going to "Thread Tools" at the top of the page, and clicking the "Mark Solved" button in that menu. Since I don't really know the layout of all operating systems, going the Environ route may not always work. I have used it in my office, as we all have the same directory structure, but I thought I'd throw the warning out there anyways.
Let us know if you need any more help!
Matt