Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default ActiveWorkbook.Save saves to wrong directory

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default ActiveWorkbook.Save saves to wrong directory


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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default ActiveWorkbook.Save saves to wrong directory


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: ---
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 226
Default ActiveWorkbook.Save saves to wrong directory

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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default ActiveWorkbook.Save saves to wrong directory

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.


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 226
Default ActiveWorkbook.Save saves to wrong directory

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


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default ActiveWorkbook.Save saves to wrong directory

Hi, Gary.
The same can be said of your idea.
How does the CustomProperty give the right path?
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 226
Default ActiveWorkbook.Save saves to wrong directory

Akihito Yamashiro laid this down on his screen :
Hi, Gary.
The same can be said of your idea.
How does the CustomProperty give the right path?


Hi Akihito,
This property would be normally set (manually) when the workbook is
created. The practise of using the CustomProperties is common where
workbooks belong to a specific addin and we want to store information
that identifies the workbook to the addin. In this case, the addin
could retrieve the CustomProperty where the author stored the path to
where the workbook is to be saved. This, of course, would be a string
value that may (or may not) include a preferred SaveAs filename.

An example of where this might apply is a XLT that gets opened
periodically by an addin for entering period data and then gets saved
to a specific folder. In this case the stored path would not include a
filename since it's likely the period date would be used in the
filename.

Another benefit of using this concept is that no file dialogs are
needed, and there's no chance for user input (erroneous or not) into
the process.

Otherwise, this info could (but not preferred) be stored in
workbook-level defined names. This could be a problem when copying
sheets between workbooks. Always using sheet-level names and
CustomProperties obviates any conflict issues rising from using
workbook-level names.

--
Garry

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


  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default ActiveWorkbook.Save saves to wrong directory

Hi Garry.

According to your idea, the property of the files saved in the wrong
folders has to be MANUALLY set.
Is there any difference between adding the custum property to each
files manually
and resaving each files to the correct folder manually?

Which is better depends on the situation.
But , since Jerry and Bruce2G has not come back again,
we cannot know for further information.
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 226
Default ActiveWorkbook.Save saves to wrong directory

Akihito Yamashiro explained on 6/23/2010 :
Hi Garry.

According to your idea, the property of the files saved in the wrong
folders has to be MANUALLY set.
Is there any difference between adding the custum property to each
files manually
and resaving each files to the correct folder manually?

Which is better depends on the situation.
But , since Jerry and Bruce2G has not come back again,
we cannot know for further information.


If I understand the OP's scenario correctly, I suspect the problem lies
in that the VBA project is used in a corporate setting AND the
workbooks being saved are pre-existing. In this case my idea works in
that the file save process can be handled entirely by code without
intervention by corporate users of the workbook. This assumes that the
person in charge of the VBA project is not the typical user, and so
precludes that the desired scenario is to have as little user
intervention toward the management of the project (or its files and
their storage locations) as possible.

In summary, you could pre-design a workbook for a particlar purpose and
set some workbook-level 'program' controls in its CustomProperties.
When users of the VBA project open the workbook (presumably via a
dedicated proc rather than the Open dialog), they can do whatever
duties they have to do and save it (presumably via another dedicated
proc). Job done!
</

Where a template workbook with multiple sheets is used, and each sheet
gets saved to a separate file, the path can be stored in a sheet-level
defined name OR in a dedicated cell somewhere on the sheet.

Also, multiple file save paths could be stored in an external file
(like txt) and retrieved at runtime for processing without user
intervention in the location where to save the file.

Of course, all of these suggestions preclude that the project and all
of its files are pre-designed before users get access. The OP suggests
that the problem with the save to location is in Excel2007 when new
workbooks are being saved for the first time. I'm suggesting use of a
template for creating the new workbook so that the save to location is
already there for the VBA project to process. If it's not there, then
the project has to have some other way of knowing what the save to path
is, and either write this to the CustomProperties so it's there for
future use or just save to that location via a dedicated proc. It would
be a good idea to either disable or 'hook' Excel's UI save methods
while the project runs so that only the project's save routine is used
regardless off how it's initiated.

Optionally, you can always use VBA's ChDir() to set the default path if
you want to preset where the SaveAs dialog opens to when they save via
the UI or your project uses VBA's Save method without implementing any
of this pre-designed stuff. In either case the user has control as to
where the file is saved. The OP states that files are being save to the
wrong place, which indicates to me that having some control over the
save process is needed or should be handled without user intervention.


--
Garry

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can i save activeworkbook to binary data before save ? badtoto Excel Programming 0 April 14th 08 02:32 AM
VLOOKUP - Saves entire directory structure Armatidge Excel Worksheet Functions 0 February 20th 07 02:53 AM
Excel saves to wrong folder T Dragos Excel Discussion (Misc queries) 5 December 26th 06 05:11 PM
Input Box value saves wrong WorkBook file & AUTO Sheet Numbering (1,2,3,4...) Corey Excel Programming 1 June 28th 06 04:55 AM
Routine that saves a file. If more than 7 in directory, deletes the oldest. wayliff[_5_] Excel Programming 3 December 27th 05 04:16 PM


All times are GMT +1. The time now is 06:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"