File Save Question

[Ken Hudson]

At the end of a macro I have the following code:

strFileSvaeName = Application.GetSaveAsFilename("Test " & strMnth, _
fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
If strFileSvaeName = "False" Then
MsgBox "Action canceled."
GoTo End_it
End If
ActiveWorkbook.SaveAs Filename:=strFileSvaeName, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

The WB that contains the macro also has a hyperlink in one of the cells.
This cell is never referenced in the macro. However, when I run the macro,
the default file save location is the hyperlink folder. I don't want that to
be the default file save location.

Have I explained this well enough for someone to tell me how to prevent it?

 

[JLGWhiz]

I have never used this method but I would think you need to include the path
to the folder you want to save it in as part of the strFileSaveName variable:

strFileSvaeName = Application.GetSaveAsFilename _
("C:\Documents and Settings\Test " & strMnth, _
fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

 

[Ken Hudson]

The way this usually works is that the default file save location is always
the location from which the last workbook was opened. My macro opens and
closes several WB's from the same folder and I expected that folder to be the
defailt file save location. It always has been.

 

[Ken Hudson]

I think I got it figured out.
Using the code I had, the macro wanted to save the output to the folder
listed as the default file location in Tools > Options... > General.
I changed the code to add a string path variable to the strFileSaveName
variable before the save code executes.

The reason I was confused was because other macros I create import text
files, manipulate the data, and then save the WB's as .xls files. When I use
the same save code for those, the default file save location is the folder
from which the text file was opened.

 

[JLGWhiz]

The Save method does save to the same folder as the workbook's source folder.
In the Help file it states that the method may change the current path. If
the current path is different than the defualt user path and no other path is
specified, it will use the default user path. That was the basis of my
suggestion to include the file path in the name. Very similar the the old
SaveAs method which is what I am more familiar with.

 

[Ken Hudson]

Hi JLG,

Well, maybe I'm not understanding.

" If the current path is different than the defualt user path and no other
path is
specified, it will use the default user path. "

Assume my default file location is C:\Default.
Assume I open a text file from C:\Other.

I have a bunch a macros that open text files, do some stuff, and then save
the files as .xls WB's using the code I posted.

The default file location for those text files being saved as .xls files is
C:\Other.

That seems to go against your statement.

The point is moot right now because I programmed around it, but it would be
nice to know for sure why the macros seem to behave differently.

 

[JLGWhiz]

The way I understand it is, if I have a path set up in Tools>Options>Save for
my Excel default destination folder and I create a new file, use the SaveAs
method to save the file and only give it a file name without including the
path, it will save to my destination default. The only way that I can get
any file, no matter what the extension I use, that I create in Excel to save
to a folder other than the default is to specify a different path in the file
name to be saved.

Now this rule does not apply to existing documents opened in Excel. As far
as I know, those documents will be saved to the same folder that they were
opened from. Even if you use SaveAs, unless you specify a different path,
the new filename will be saved to the same folder as the workbook that it was
created from.
At least it worked that way when I tried it.

Maybe we are saying the same thing, different ways.