Trap Filename for teh Save As dialog

[astharaj]

Hi All,

Here's my situation - Please Help!!!!

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

I just something and some way I can know the filename and path where the user is saving the file on selecting No to the confiramtion message.

Any clues!!!!!!

Thanks,
Astha

[Kelly again]

Is there a way to trap the filename and path from the windows save dialog?

Yes, there is!

Good news.

In Excel, you can cause Excel to display the "Save As" dialog from within a macro. Excel will display the dialog and retrieve whatever the user entered for a "Save-as" name. But then it is up to you to actually perform the save in your macro code.

For example, try the following mini-macro in Excel as a demonstration:


Sub Control_Your_Own_SaveAs()

myname = Application.GetSaveAsFilename

MsgBox myname

End Sub


However, your actual code is running from Access, am I right?

So in Access it is probably a little different. Try looking in the help files for "Built-In Dialog" or "Built-In Dialogs"

There is also the issue of how to make all of this happen when the user clicks on something.

Does Access have event handlers similar to those in Excel??

Because in Excel, I would suggest putting some code in the BeforeSave event handler, you know? in excel the event handler looks like this:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

Try these links:

http://support.microsoft.com/?kbid=139723
Built In Dialogs in Access 2000 (http://www.google.com/search?q=%22access+2000%22+%22built+in+dialogs%22)


:rtfm: