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.
