Saving all over the place

alcorjr

Active Member
Joined
Dec 29, 2002
Messages
416
hi guys, I got this command:
Code:
ActiveWorkbook.SaveAs Filename:= _
        "HORSES_TO_DATE.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

The problem, is that for some reason, the book gets saved "all over the place", as it were.

That is, I have to look for it in different folders instead of getting saved over the current one, which is in "My desktop"

How can I make it save it always in My desktop, in spite of some users being in a network environment, and others not?

Thanks a bunch
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
A quickie might be to record a new macro and then manually save the file using "save as" to the desktop directory. Go ahead and browse to the directory you want (ex: desktop) and save it. Then stop recording the macro and inspect its code.
 
Upvote 0
Bill, thanks for your interest. Your suggestion could work out of a network environment.
In a network environment things could get trickier, but actually you have given me an idea...

Thanks for your suggestion.
:cool:
 
Upvote 0
ActiveWorkbook.SaveAs Filename:= _
"c:\Windows\Desktop\HORSES_TO_DATE.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Will cause the file to save to the specific folder C:\windows\desktop\

If you are on XP or NT if will be
c:\documents and Settings\"Your UserName"\desktop\
so
C:\documents and settings\Mike\desktop\

Hope this helps.
 
Upvote 0
Just for the heck of it, try this macro with all the API calls that our friend Ivan Moala has posted before, to see if it works on your users' network. If it does, just use the return value in the Sub DeskTop macro as the file path for your SaveAs macro.

Put this in a dedicated stnadard module (thanks again Ivan):

Option Explicit

Const CSIDL_DESKTOP = &H0
Const CSIDL_PROGRAMS = &H2
Const CSIDL_CONTROLS = &H3
Const CSIDL_PRINTERS = &H4
Const CSIDL_PERSONAL = &H5
Const CSIDL_FAVORITES = &H6
Const CSIDL_STARTUP = &H7
Const CSIDL_RECENT = &H8
Const CSIDL_SENDTO = &H9
Const CSIDL_BITBUCKET = &HA
Const CSIDL_STARTMENU = &HB
Const CSIDL_DESKTOPDIRECTORY = &H10
Const CSIDL_DRIVES = &H11
Const CSIDL_NETWORK = &H12
Const CSIDL_NETHOOD = &H13
Const CSIDL_FONTS = &H14
Const CSIDL_TEMPLATES = &H15
Const MAX_PATH = 260
Declare Function SHGetSpecialFolderLocation Lib "Shell32" _
(ByVal hwnd As Long, ByVal nFolder As Long, ppidl As Long) As Long

Declare Function SHGetPathFromIDList Lib "Shell32" _
(ByVal Pidl As Long, ByVal pszPath As String) As Long

Function SpecFolder(CSIDL As Long) As String
Dim DeskTopSysFile As String
Dim hwnd As Long
Dim Pidl As Long

'Get the windows desktop Pidl
SHGetSpecialFolderLocation 0, 0, Pidl

'assign spaces
DeskTopSysFile = Space(260)

'Get the path
SHGetPathFromIDList Pidl, DeskTopSysFile

'Now shorten
SpecFolder = Left(DeskTopSysFile, InStr(1, DeskTopSysFile, vbNullChar) - 1)

End Function


Sub DeskTop()
MsgBox SpecFolder(CSIDL_DESKTOP), , "Desktop file path is..."
End Sub
 
Upvote 0
Hey, Tom....Thanks a lot. Specfolder is getting loaded with the right path.
Just one question, if the user was operating out of a network, (Isolated PC), would the same Sub return an error, or would it return the standard path for the desktop.
For that matter, is there any way that can be used to determine if the terminal is "hooked" to a network or not, (run the Specfolder sub or use the Desktop standard path)
Thanks :p
 
Upvote 0
I wish I could help to answer your follow-up questions but I am not on a network and cannot test it to be sure. I only post solutions after I've tested them and know for a fact that they work, unless I preface my responses such as I did in my first reponse to you on this thread, with a "see if this works" blurb. So, unfortunately, I'm not able to tell you with confidence that this code will identify people on a network from people who are not, especially with the myriad of network settings and domains in use today.

I can tell you it worked for me, with my system set-up in my home office with a LAN, but I'm not on a large corporate-level network as found in many workplace environments.

I'd suggest that you do what you've probably already started doing, which is take the time to experiment with your computer and other people's computers on a network to see if it works the way you want. Certainly, other people reading this thread that are on a network might test this and let us both know how it goes for them.

Good luck.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top