FileSaveAs prompt (9.0.2000.0224)

Topic

New Reply

I’ve written the following macro that extracts certain fields from a project to an Access database for further analysis. I would like to avoid having the Append Overwrite Cancel window appear and simply overwrite the existing database. Is there a variable I can pass to suppress this message (something similar to docmd.setwarnings false in Access)?

SelectTaskColumn Column:=”Name”
OutlineShowTasks OutlineNumber:=pjTaskOutlineShowLevel9
FileSaveAs Name:=””, _
FormatID:=”MSProject.MDB8″, _
Backup:=True, _
map:=”Deployment Conflicts”
Application.Quit SaveChanges:=pjDoNotSave

Thanks,

Vernon

Reply | Quote

Replies

… Off the top of my head, I don’t think there is a flag you can set to avoid the append, overwrite, cancel window in MS Project. (I will have to review this and get back to you…) To get around this in the past, if the filename is the same, I created a batch file that runs and deletes the file before using the FileSaveAs command, something like this in code from access…

I used a command button to open a form, say form1. Part of the code on the button is as follows:

RETVAL = Shell(“C:mpmde.BAT”, 1)

On the on open event of form1, the following code creates the batch file that deletes the existing file.

Set fs = CreateObject(“Scripting.FileSystemObject”)
Set A = fs.CreateTextFile(“c:mpmde.bat”, True)
A.WriteLine (“DEL C:MSP*.*”)
A.WriteLine (“EXIT”)
A.Close

Thus, each time the form is opened, the batch file is called and the file deleted. Thus when you use file save as, you dont get the Append Overwrite Cancel window since the file was deleted. This is crude but it works.

Note: When you run your code, does the file save with the correct name? Just curious?

HTH

Reply | Quote

I am creating a similar macro, except mine is only one line long.

FileSaveAs Name:=”” & ActiveProject.Title, UserID:=”xxxxxx”, DatabasePassWord:=”xxxxxxxx”, FormatID:=”MSProject.ODBC”

For some reason, sometimes Project likes to add .mpp to the end of the title. Also, with several people having access to the file, the title mysteriously gets changed. Magic I guess…

Anyways, it’s bad when the title changes because we only want one instance of each plan in the database. So, is there a way to make sure that the overwrite dialog does come up, or create a dialog to accept creating a new project?

Or would it be possible to create a write protected field on a new view, or some such thing, that I could use as the permanent file name. Thus not relying on the title staying the same.

Thanks,
Justin Kuehlthau

Reply | Quote

Justin,

I was reviewing your posts on saving the active project name on the other forums. It appears that you solved that.

Is there any consistency to what the title gets changed too? I suppose it may get saved to what it thinks the active file currently is. Do you have this problem when other applications are not running?

You could write some code to see if the active project name exists, and if so, ask to replace it. If yes, run the fileSaveAs command, if not, skip it.

Another option is to open the ms project file as exclusive to lock others out to avoid the name changes.

Just some ideas, however, I do not know if this helps.

I will post back if I come up with any ideas.

Reply | Quote

Gary,

There is no consistency to what the title gets changed to. About 4 people have access to each file, which I can do nothing about, and sometimes they like to make one of the words in the title have an s to make it plural, or many other minor things like that. Project also adds the .mpp once in a while.

I export about 25 projects to a database once a week. I’m trying to make it easier on myself, so I’m writing this macro.

Having it ask me to enter the name of the plan would work well, but in the end I would like to be able to run the macro and have it automatically do it. I am looking at project now, and I see under properties there are Custom properties. Perhaps I could create one one to store the Database save name, since I doubt anyone would figure out to go in there to muck up my macros.(Sometimes it seems like people try to mess things up… )

Also, sometime in the future I would like to be able to open a group of projects, click the macro, and have it save them to the database and close them in turn. Would this be something hard to implement?

Thanks,
Justin

Reply | Quote

One of the apps I wrote (with the help of others on this and other forums) allows the user to browse to the folder where the MS Project files are located and then double clicking on the file name opens the MS Project Project and imports data into access. (The app is in access.) Access then manipulates and re-connects the data for reporting purposes. This is not exactly what you are looking for but may give you some ideas.

In my example, I always save the file with the same name so I do not encounter the problems you are having.

As I think about this, if all of the projects are saved in the same directory, you could get all of the project names in the folder and execute against them if the names are changing or create a master list to compare against.

Which tables from MS Project are you importing? What database are you importing too?

I find it easier to use MS Access as the front end and for programming to access MS Project. Is this what you are doing?

Reply | Quote

Sorry for the late reply, but things got busy.

To answer your questions, I have no idea what tables are being imported. The version of Project we are using has been so customized that it is now a glorified spreadsheet. Unfortunately no one wants to know how to use the program correctly, so I have to live with it.

I’ve decided to just add my own little custom property, under File->Properties->Custom, called ONTIME. I don’t know how to call that custom property in my script though. I’ve been trying ActiveProject.CustomDocumentProperties.ONTIME, and a million variations of that, but to no success.

Thanks,
Justin

Reply | Quote

Justin,

I believe you have the correct object, however, did you add the index number of the Ontime property so the code knows which one to pull?

I also believe this object is read only.

Good luck.

Reply | Quote

I do not see anything about an index number, there is a box for “Link to Content.”

This object being read only is fine.

Justin

Reply | Quote

…. Sorry, I was looking at the wrong object….

Reply | Quote