Problem with first save of Excel workbook

[softwareguy74]

It seems that the Save method of the Excel app object behaves differently than Word. What happens in Word, if you issue the Save method, and it is the first time the document has been saved, a SaveAs dialog appears, allowing the end user to select the file name and path.

However, this is not the case in Excel for some reason. When you issue the Save method, it never displays a SaveAs dialog, and assumes you want to save it as the current default name (Book1.xls) in the current path. Yet when clicking the Save button in Excel, it DOES display the SaveAs dialog if this is the first time you're saving.

So why is there an inconsistency here? I could find no method in the Excel object to display the SaveAs dialog. I know I can display my own dialog by using the SaveFileDialog class, but the bigger issue is, how do I know this is the first time the user is saving and therefore show the SaveAsDialog?

Very weird inconsistency if you ask me...

[Siddharth Rout]

It has a file save as method...

Using a dialog box

Application.Dialogs(xlDialogSaveAs).Show

or directly issuing the command

ActiveWorkbook.SaveAs Filename:= "C:\Book1.xls"

Regarding the comment

It seems that the Save method of the Excel app object behaves differently than Word.

I never faced this problem... If the file is new and if you press say Ctrl+S or File~~>Save then it will pop a File SaveAs Dialog. However I will recheck that when I go home as in my office I have Office 2007.

[softwareguy74]

I don't think you understood completely what I was saying.

Yes, when using Excel directly, it DOES pop open a SaveAs dialog if it is the first time you have saved that workbook.

However, this is NOT the case when using the API. When using the API (VSTO), and you initiate the Save method, and it is the first time the file has been saved, it DOES NOT show the SaveAs dialog as you would expect. Rather, it just attempts to use the default name assigned, such as Book1.xls and complains that the file already exists.

[Siddharth Rout]

Can I see the code that you are using? Let me test it for you.

[anhn]

Yes, in Excel with wb.Save method, if wb was never saved before then it will be saved to the current folder (with the default filename as displayed in Title when it was created) without asking filename and folder for SaveAs.

The trick is: If a workbook was never Saved before then its Path is blank. You can easily to check that.

This code is within Excel:

Sub SaveWB()
    Dim wb As Workbook
    Dim sFName As String
    
    Set wb = ActiveWorkbook '-- or any workbook
    If Len(wb.Path) Then
        wb.Save
    Else '-- wb was never saved before
        sFName = Application.GetSaveAsFilename()
        If sFName <> "False" Then
            wb.SaveAs sFName
        End If
    End If
End Sub

Automation code (from outside of Excel)

'-- xlApp has been previously created as "Excel.Application"
Sub SaveWB2()
    Dim wb As Excel.Workbook
    Dim sFName As String
    
    Set wb = xlApp.ActiveWorkbook
    If Len(wb.Path) Then
        wb.Save
    Else '-- wb was never saved before
        sFName = xlApp.GetSaveAsFilename()
        If sFName <> "False" Then
            wb.SaveAs sFName
        End If
    End If
End Sub

[softwareguy74]

Thanks anhn! That did the trick.

Now, what is the equivalent to GetSaveAsFilename in PowerPoint as it doesn't seem to be available?

[Siddharth Rout]

Application.FileDialog(msoFileDialogSaveAs).Show

[softwareguy74]

Application.FileDialog(msoFileDialogSaveAs).Show

Thanks but for some reason, it is not actually saving the file when using this.

[Siddharth Rout]

Sorry ... went out with my team for dinner...

Try this...

Sub Sample()
    Dim dlgSaveAs As FileDialog

    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
    
    With dlgSaveAs
        '~~> If user presses Save
        If .Show = -1 Then .Execute
    End With
End Sub