PDA

View Full Version : [SOLVED] I need help with the file path script



Erays
03-17-2005, 02:34 PM
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
03-17-2005, 02:43 PM
If I understand you correctly this should work:


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

OOPS...Should be ThisWorksheet.SaveAs

Erays
03-17-2005, 02:46 PM
here is my file path d:\documents and settings\clc6b\Desktop\Monthly Reports
the clc6b is the user profile on windows 2000 pro.

mvidas
03-17-2005, 02:57 PM
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
03-17-2005, 03:12 PM
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
03-17-2005, 03:14 PM
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
03-17-2005, 03:15 PM
This is creating an error message



Set T = CreateObject("WScript.Shell")

Zack Barresse
03-17-2005, 03:16 PM
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
03-17-2005, 03:54 PM
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
03-17-2005, 04:17 PM
I get a runtime error 429 ActiveX cannot create object

mvidas
03-17-2005, 04:22 PM
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
03-17-2005, 04:26 PM
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
03-17-2005, 04:29 PM
I've got xl2000 at home and at work (win2k at work, xp at home), and it works fine both places for me

Erays
03-17-2005, 04:36 PM
:beerchug: 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
03-17-2005, 04:43 PM
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