supress confiramtion for overwriting the file - URGENT

[astharaj]

Hi,

I have an application to generate Excel report from access. i allow user to save the file. If the user selects an existing file -> windows throws a confirmation saying " Do you want to overwrite the file"

Is there a way to supress this confirmation and overwrite te selected file by default. Can it be done in Excel or access macro?

Thanks
Astha

[TonyT]

That prompt is there for good reason, it prevents accidental altering of a file. Your options to save to a different directory or to same directory with a different name.

It relies upon a basic physics law that no 2 objects can occupy the same space at the same time.

[astharaj]

Thanks for your reply.

I agree that its for a good reason - but I just want to supress that for this particular application - Is it even possible?

Thanks again.
Astha

[astharaj]

Can I supress this confiramtion and create my own custom confirmation - where I can trap 'No' case.

I I can - I want to provide my 'Save As' dialog and be able to know the path and file name being used to save. I need those for my application.

Is it possible to trap the 'No' case in this confirmation?

Thanks,
Astha

[cyberskye]

No offence, but this is a really bad idea.
There may be ways to customize the alert message displayed, but I cannot imagine that tehre would be too much info on this subject out there - again, there is very little advantage to this and it probably could, in fact, be turned into an expoit. Opens the door to overwriting files without user intervention...

I don't mean to be discouraging, but really think about why you want to do this.

Edit - there may be legal implications in some places around recording detailed information (filesystem layouts - where files are stored) on other peoples' machines without permission.

[Easto]

I'm not really sure either why you have such a indepth Access database but if you insist....

Why not first delete the original file and then save your new one. It will no longer be overwriting the old once since you just deleted it.

[TonyT]

Why not first delete the original file and then save your new one. It will no longer be overwriting the old once since you just deleted it.

You cannot delete a file that is in use. Attempting to do that will launch a different prompt from Windows.

[astharaj]

Thank you guys for all the information. I agree with everything you guys said - but here's what I really want - there could be some other ways to do it that you can suggest.

I allow user to save the file for the first time using a custom dialog class and retrieve the file name and path for my use.

Actually I am using TransferSpreadSheet method to import data from Excel to Access - and that function requires the excel file name with full path. I have an excel template - which is called from access form and is populated with some DB data - I force the user to save this excel file before exiting access - that way I get the file name and path of the saved excel - which I use later in the macro. I have a button/ macro in the excel which uses the TransferSpreadSheet method to transfer back any changes made in the excel file. Second time if the user launch the excel template from access and selects the same name in my save as dialog - he will get the data from DB and then the confirmation window will pop up.

If the user decides to overwrite the file (using Yes on the prompt) then I have no issues - because I am using the file name and path in my later code.
BUT - if he selects NO and decides to save it at another location or with a different name - my code will fail (as it looks for the old filename and path)

Is there a way to know the new file name and path from the save dialog box of windows? or any other method where I can trap the filename and path?

Any suggestion will be highly appreciated. I am really stuck here and need to deliver the application to client by this week. PLEASE HELP!!!

Thanks again.
Astha

[cyberskye]

I still don't understand what you're trying to do (business-wise) - what is the client actually doing with this app? Is this a document management system? Is Access/Ecel only used to authenticate/track users?

I guess my question - that you still haven't answered - is "why"? Without that, how could anyone recommend a better, different "how"?

Edit - will this be accessed/run locally by the client?

[astharaj]

Hi Cyberskye,

Thanks for your response. I am not sure how to explain what exactly I need - but let me try again.

Here's "Why" -

1) My client has an application in access with a backend SQL Server database. Access has linked tables from SQL Server. There is a table for users.
2) My system is an add-on in the application which will generate an excel report with all the information of selected users.
3) Someone on clients end will modify the compensation information in the excel sheet - and the system (by clicking on a button) will save the modified data from excel back into SQL Server via access
4) This how my system works:
* I have a form in access, which allows selecting the criteria for the list of users to be present in the report.
* Once the user select the criteria and clicks on the "Generate" button -> a query is formed; an excel template (already created and saved on the hard disk with column headers, button and macros) is opened; the rows are populated using query result
* This is where I force the user to save this excel sheet somewhere with some name (using a custom dialog class which allows me to trap the file name entered and path browsed to save)
* I also pass this full filename (with path) to the excel as a cell.value
* Once the excel sheet is created, populated, and saved - user can modify the compensation information.
* I have a button on the excel sheet - which when clicked -> runs the following method
OpenFile = Application.Cells(1, 20).Value

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tempPhysicianAllotment", OpenFile, True, "R7:U15"

*This inserts into the specified table and then updates the required users table.

All these work properly.

Here’s my situation:

Suppose the user again launches the excel report from access form -> he’ll be asked to save the file. Suppose he selects an existing file to save at that point of time; then the system will create, populate the excel sheet. This is where application will give the confirmation message to overwrite or save as another file. NOTE: the Application.Cells(1, 20).Value -> that I am using in my code, is already populated with the value user initially selected while saving the file from access (e.g. C:folder\File1.xls)

If the user, at this points selects to create another file and not overwrite -> then the Application.Cells(1, 20).Value won’t change (as I don’t know the new path and filename). Thus, the button on the excel sheet will not work correctly as it wont have the right excel file to look for.

Here’s my question:

Is there a way to either force the user to overwrite the file if he selects the same name initially? OR
Is there a way to provide my own confirmation so that I can trap the new filename and path? OR
Is there a way to trap the filename and path from the windows save dialog? OR
Anything else that I can do to have the right filename and path in Application.Cells(1, 20).Value

Hope now I am clear with “Why” – Let me know if I am not

Thanks very much,
Astha

[cyberskye]

Wow.

I would have built a web applicaiton with a database backend to handle this. You could have the forms in realtime - and then do whatever data-massage you needed on the sql side. That last piece is the weakness of your client's architecture.

Excel>Access>SQL transformations - that's a lot of steps and dependencies. Not sure how to overcome the issue you are describing. The ability for an app to overwrite like that without user intervention could be used nicely by virus authors. What woudl prevent it from saving the xls as a system file - how would you prevent users from overwriting a coworkers stuff accidentally?

[astharaj]

Yeah, the designs seems wierd - but there's hardly anything I can do to change that.

Okay, I agree, overwriting without user intervension is not an option - but is there a way to trap the "No" part of the confirmation and pop up my own save as dialog? Or, is there a way to know where the user has saved the file and with what name? Or any other way I can know the new filename/path

I have to find a workaround for this - but am not sure how!!!!

Anyways, Thanks very much for all your help.

Astha

[cyberskye]

Hi Astha - I understand you have to work with what you have to work with.

I don't have that indepth knowledge of windows API or Office APIs to help here. Hopefully someone will stop by who can help.

Good luck,

Skye

[astharaj]

I hope that too - Somebody Please help!!!

Thanks for all your help - Skye

Astha

[cyberskye]

Maybe posting under with a new subject since you are changing you approach? Might post in the sw forum as well.

[Kelly again]

I believe that Visual Basic allows for something like:

Application.DisplayAlerts = False

I have no idea if that will help in this situation or not. Try the following link for more information:

Read More Information (http://www.google.com/search?q=%22Application.DisplayAlerts%22)

Good luck!

I wish had more to say on this topic....

[astharaj]

Thank you - the ActiveWorkbook.FullName did the trick and now there's no need to supress the alert.

Thanks,
Astha