ActiveWorkbook.Save creates a new spreadsheet in Documents instead of saving
the spreadsheet in its current location. I am using Excel 2007 with a 2003
file (.xls).

The macro is in personal.xlsb and is used to make a few minor fixes to the
active spreadsheet. I want to save the active spreadsheet before I make
those fixes, in case something goes wrong. But, it needs to save it in
place, not create a new file somewhere else.

Thank you.
Jerry

hi Jerry,

Has the file previously been saved?
Do you have the right permissions to save it in the particular
directory?

If the file hasn't been previously saved, make sure that you explicitly
define the entire filepath to ensure that it saves in the right place.

Try checking [alt + t + o] - Save - Default File Location & emptying
this field if it's populated.

Is your current directory actually what you think it is?
You can check this in the VBE - [ctrl + g] to bring up the Immediate
pane and type in

VBA Code:
--------------------


?curdrive
--------------------



If it isn't the drive you expect to see, you can modify it within your
macro using the below (Google for examples).


VBA Code:
--------------------


chdrive
'and
chdir
--------------------





hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197500

http://www.thecodecage.com/forumz

I have the same problem. We have a bunch of spreadsheets, and from
time to time save them all by looping through workbook.save

Unfortunately, every once in a while, a spreadsheet will be saved to
the wrong directory! I don't think the user could have done it -
they're not sophisticated enough to do a 'save as' and look through all
the options.

What I've done as a workaround has been to do a saveas and explicitly
say what the folder is. I also had to inhibit the message about
replacing a file with application.displayalerts = false before the
saveas and .. = true afterwards.

I think there's a bug with windows 7 and excel 2007 that causes this,
I'd like to know if anyone else has encountered this problem.

Bruce


broro183;705841 Wrote:

hi Jerry,

Has the file previously been saved?
Do you have the right permissions to save it in the particular
directory?

If the file hasn't been previously saved, make sure that you explicitly
define the entire filepath to ensure that it saves in the right place.

Try checking [alt + t + o] - Save - Default File Location & emptying
this field if it's populated.

Is your current directory actually what you think it is?
You can check this in the VBE - [ctrl + g] to bring up the Immediate
pane and type in

VBA Code:
--------------------


?curdrive
--------------------



If it isn't the drive you expect to see, you can modify it within
your macro using the below (Google for examples).


VBA Code:
--------------------


chdrive
'and
chdir
--------------------





hth
Rob


--
bruce2g
------------------------------------------------------------------------
bruce2g's Profile: http://www.thecodecage.com/forumz/member.php?u=2042
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197500

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

bruce2g expressed precisely :
I have the same problem. We have a bunch of spreadsheets, and from
time to time save them all by looping through workbook.save

Unfortunately, every once in a while, a spreadsheet will be saved to
the wrong directory! I don't think the user could have done it -
they're not sophisticated enough to do a 'save as' and look through all
the options.

What I've done as a workaround has been to do a saveas and explicitly
say what the folder is. I also had to inhibit the message about
replacing a file with application.displayalerts = false before the
saveas and .. = true afterwards.

I think there's a bug with windows 7 and excel 2007 that causes this,
I'd like to know if anyone else has encountered this problem.

Bruce

<FWIW
Have you considered storing the path where the file should be saved in
CustomProperties of the workbook? That would allow you to read this
into your SaveAs proc and no worries about getting it written to the
correct location.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Check if the book is saved on a file system.
ActiveworkBook.Path can be used for the purpose.

Sub Main()
If ActiveWorkbook.Path = "" Then
MsgBox "Save the file before the process."
Exit Sub
Else
ActiveWorkbook.Save
'Do something here.
End If
End Sub


If this doesn't help you, the file may be in the 'Program Files'
folder and the PC is Windows Vista or 7,
the file path may be redirected to the VirtualStore folder.

Akihito Yamashiro brought next idea :
Check if the book is saved on a file system.
ActiveworkBook.Path can be used for the purpose.

Sub Main()
If ActiveWorkbook.Path = "" Then
MsgBox "Save the file before the process."
Exit Sub
Else
ActiveWorkbook.Save
'Do something here.
End If
End Sub


If this doesn't help you, the file may be in the 'Program Files'
folder and the PC is Windows Vista or 7,
the file path may be redirected to the VirtualStore folder.

How does this get the correct path? If files are currently saving to
incorrect folders then the 'assumed path' has to be removed from the
equation. Storing the correct path for a workbook as a CustomProperty
in the workbook file allows code to retrieve it without assumption.

While bruce2g states his users aren't savvy enough to use SaveAs to
save files to the wrong folder, doesn't meant that someone isn't
deliberately moving files from one place to another. Using the
CustomProperty approach means the correct path is imbedded in the xls
file, so no matter where it's opened from it can always get saved back
into the correct folder via code.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc