How can I suggest a file name? 2

[uberpudge]

My problem involves VBA for Excel 97.
I have code that creates a new Workbook object.
My code doesn't save the workbook and I would vastly prefer to leave that option up to the user.
What I don't like (and ergo, would like to change) is that if the user goes to "File -> Save" or "File -> Save As", Excel automatically recommends "Book1.xls" as the file name.
I would like to be able to suggest a file name in the "Save" dialog box for Excel 97 based on the contents of the file.

Does anyone know of a way to do this?
Any help here would be great.

Thank you for your time,
Pete

 

[Chance1234]

you could create your own form and have a textbox on the ofrm with a default name on, then have a button with code to save the workbook under the name that is in the text box

http://WWW.5YLAC.S5.COM

 

[uberpudge]

I could do that without the text box.

My problem is that I do not want to save the workbook.
I want the user to decide that.

All I want to do is "suggest" a file name by having a custom string appear in the "File Name" field on the "Save" dialog box if the user decides to save the workbook.

Thanks anyway,
Pete

 

[SkipVought]

Hi,

Here's a way...

    fileSaveName = Application.GetSaveAsFilename( _
        initialFilename:="MyFileSuggestion.xls", _
        fileFilter:="Excel Files (*.xls), *.xls")
    If fileSaveName <> False Then
        'here is where you would save your file
        ActiveWorkbook.SaveAs fileSaveName
    End If

Hope this helps -- check out help on this and saveas Skip,
metzgsk@voughtaircraft.com

 

[uberpudge]

Hey Skip,

Thanks for the input. The following code seems to work from the "ThisWorkbook" object of the new workbook:
------------------------------------------------------------
Option Explicit

Public booSaveInProgress As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim varFileName As Variant

If booSaveInProgress Then
Exit Sub
End If

If Strings.InStr(1, ActiveWorkbook.Name, &quot;Book&quot <> 0 Then
varFileName = Application.GetSaveAsFilename(&quot;Suggested Name.xls&quot;, &quot;Microsoft Excel Workbooks (*.xls), *.xls&quot

If Information.VarType(varFileName) = vbString Then
booSaveInProgress = True
ActiveWorkbook.SaveAs varFileName
SaveAsUI = False
Cancel = True
ActiveWorkbook.Saved = True
ElseIf Information.VarType(varFileName) = vbBoolean Then
Cancel = True
End If
End If
End Sub
------------------------------------------------------------

The hard part was figuring out how to attach it to the newly created workbook through code.

I have but one little quirk left to squish.
If I close the workbook before saving changes, Excel prompts me wanting to save &quot;Book1&quot;.
I click &quot;Yes&quot; and the above routine kicks in. I successfully save the file (with the suggested name)...

...Then Excel prompts me again, this time wanting to save &quot;Suggested File Name&quot;.

This only happens when I try to close and save the workbook.

Does anyone know how to stop Excel from prompting twice (while still leaving Events and DisplayAlerts enabled)?

Thank you all for your continued help.
Pete

 

[rmikesmith]

uberpudge,

Try the following. I think this will provide the cleanest approach to doing what you want; namely, after a workbook is created, the user decides whether to save the workbook and if so, a custom SaveAs dialog opens showing your suggested filename. This approach does not require attaching any code to the created workbook. Rather, it uses a Class module to access Application-wide events, in this case, the WorkbookBeforeSave event. Here are the steps:

1. Insert a Class Module
2. Type the following declaration:

Public WithEvents App as Application

3. Click on the left dropdown box and select App.
4. Click on the right dropdown and select WorkbookBeforeSave. Enter this code:

Private Sub App_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

  On Error Resume Next
  If Wb Is ThisWorkbook Then Exit Sub 'Exempts your app
  If Wb.Path <> &quot;&quot; Then Exit Sub  'Only invoke custom save for new workbook
  Cancel = True
  If FName = App.GetSaveAsFilename(&quot;SuggestedFilename&quot;, &quot;Excel Workbook (*.xls), *.xls&quot;, 1, &quot;Save As&quot;) = &quot;False&quot; Then Exit Sub
  App.EnableEvents = False  'Needed because next statement fires the WorkbookBeforeSave event again.
  ActiveWorkbook.SaveAs FName, xlWorkbookNormal
  App.EnableEvents = True
End Sub

5. In a new or existing code module insert the following procedure:

Sub Ini()
  Set AppObject.App = Application
End Sub

6. Place a call to Ini in the Workbook_Open event procedure of ThisWorkbook (your appliction)

Notes: You can replace &quot;SuggestedFilename&quot; with a code-generated value. Use a Publically declared variable and assign it a value after your code creates the workbook. Your user can select File->Save, File->SaveAs or click on the Save toolbar button and your custom SaveAs dialog will be invoked. Also, if the user tries to close the workbook this will generate the standard Excel message asking to save. If 'Yes' is selected, the custom SaveAs will be invoked.

Hope this helps.

M. Smith

 

[rmikesmith]

uberpudge,

One correction. The following line:

If

FName =

App.GetSaveAsFilename("SuggestedFilename", "Excel Workbook (*.xls), *.xls", 1, "Save As") = "False" Then Exit Sub

should be

If App.GetSaveAsFilename("SuggestedFilename", "Excel Workbook (*.xls), *.xls", 1, "Save As") = "False" Then Exit Sub

Sorry for any confusion.

 

[uberpudge]

Okay... I just got in to work and checked the thread.
Having copied and pasted the code from rmikesmith into a new class module (as described above) and the Ini() call into another existing module (as prescribed above), I closed the workbook and reopened it (to run the Ini() call.)

I get the following error:
Compile Error:
Variable not defined

which highlights the &quot;AppObject&quot; in:
Set AppObject.App = Application

I have renamed the class module to &quot;AppObject&quot;, and made sure the WithEvents declaration in &quot;AppObject&quot; is Public.

I still get the error.

Does anyone know what is going on?

Thank you all for your continued help.
Pete

 

[rmikesmith]

Pete,

I inadvertently left out the declaration

Public AppObject As New AppClass

which should be placed at the beginning of your existing code module. Rename the Class back to AppClass also. Sorry for the difficulty.


Regards,
M. Smith

 

[uberpudge]

Hey Mike!

Thank you for the idea and the code, but...

I put &quot;Public AppObject As New AppClass&quot; in the declarations section (right below &quot;Option Explicit&quot of the existing code module and I renamed the class module to &quot;AppClass&quot;.

I'm still getting a compiler error.
This time it's:
User-defined types and fixed length strings not allowed as the type of a public member of an object module; private object modules not allowed as the type of a public member of a public object module.

Any ideas?

Thank you all for your continued help.
Pete

 

[rmikesmith]

Pete,

Sounds like something in the declarations are crossed up, perhaps. If you wouldn't mind posting your email address, I'll send you my workbook with all the code, that I have tested successfully.


Regards,
Mike

 

[uberpudge]

Try &quot;peter.hopkins@convergys.com&quot;

Thank you all for your continued help.
Pete

 

[uberpudge]

Hey Mike!

Tried your workbook, but it wasn't actually saving the file.
When I step through it, FName's value never changes from null.

The class module works great for trapping the event, but I still get that annoying double-prompt to save the file when I click the document close button, or select close from the file menu.

Any other ideas?

Thank you all for your continued help.
Pete

 

[uberpudge]

Eureka!
Thank you both Skip and RMikeSmith for pointing me in the direction I needed!

The class module now looks like this:
------------------------------------------------------------
Option Explicit

Public WithEvents App As Application
Public booSaveInProgress As Boolean

Private Sub App_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
Dim booDocSaved As Boolean

booDocSaved = ActiveWorkbook.Saved
ActiveWorkbook.Saved = True

If Not booDocSaved Then
Select Case Interaction.MsgBox(&quot;Would you like to save &quot; + Strings.Chr(34) + Extract.strSuggestedFileName + Strings.Chr(34) + &quot; before closing the file?&quot;, vbYesNoCancel, &quot;Save file?&quot
Case Is = VbMsgBoxResult.vbYes
booSaveInProgress = SaveSuggestedFile()
Case Is = VbMsgBoxResult.vbCancel
Cancel = True
ActiveWorkbook.Saved = booDocSaved
End Select
End If
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
If booSaveInProgress Then
Exit Sub
End If

If Strings.InStr(1, ActiveWorkbook.Name, &quot;Book&quot <> 0 Then
SaveAsUI = False
Cancel = SaveSuggestedFile
End If
End Sub

Private Function SaveSuggestedFile()
Dim varFileName As Variant

If Strings.InStr(1, ActiveWorkbook.Name, &quot;Book&quot <> 0 Then
varFileName = Application.GetSaveAsFilename(Extract.strSuggestedFileName, &quot;Microsoft Excel Workbooks (*.xls), *.xls&quot

If Information.VarType(varFileName) = vbString Then
booSaveInProgress = True
ActiveWorkbook.SaveAs varFileName
ActiveWorkbook.Saved = True
End If
End If

booSaveInProgress = False
SaveSuggestedFile = True
End Function
------------------------------------------------------------

I caught on to intercepting the App_WorkbookBeforeClose event and replacing it with my own.

The sheet now works flawlessly! Thank you all very much.

Oh... I wasn't clear in my earlier posts. The second compiler error was caused by the line:
Public AppObject As New AppClass

in the existing code module. Changing it to:
Private AppObject As New AppClass

got rid of the error.

Thanks again,
Pete