Opening SaveAs Diolog Box for Excel 1

[DylaBrion]

Hello

Can you please help with the following, I have the following code to save an excel workbook with a defined name to a defined file path. The code works fine. However the file is saved with no visible evidence of it saving unless the user goes back to the destination folder to check.

I would like to open the save as dialog box with the file path and name already shown in the dialog box so the user can click the save button on the dialog box and be assured that this has saved

I've been trying to find the right code but no luck

If anyone can help, I'd appreciate it. Many Thanks

Here is my code

Option Explicit
Sub SaveCRtoBotFolder()
Dim Title As String
Dim StartDate As String
Dim Name As String
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
Const csPath As String = "\\C\Test"



'create data for file name
StartDate = [C11]
StartDate = Format(StartDate, "Long Date")
Title = [I13]

'save workbook with code under user defined name
Application.ScreenUpdating = False    ' Prevents screen refreshing.
Name = StartDate & " - CR Works - " & Title & ".xlsm"
CurrentFile = ThisWorkbook.Name
ThisWorkbook.SaveAs Filename:=csPath & CurrentFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.ScreenUpdating = True

End Sub

 

[Andrzejek]

>the file is saved with no [blue]visible evidence of it saving [/blue]

...
ThisWorkbook.SaveAs Filename:=csPath & CurrentFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.ScreenUpdating = True
[blue]
MsgBox "The file " & CurrentFile & " have been saved to " & csPath & ".", _
    vbOKOnly Or vbInformation, "Enjoy."
[/blue]
End Sub

---- Andy

There is a great need for a sarcasm font.

 

[strongm]

Or if you really want to see a SaveAs dialog, replace

[blue]ThisWorkbook.SaveAs Filename:=csPath & CurrentFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled[/blue]

with

[blue]    With Application.FileDialog(msoFileDialogSaveAs)
        .InitialFileName = csPath & CurrentFile
        If .Show Then
            ThisWorkbook.SaveAs Filename:=.InitialFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        End If
    End With[/blue]

 

[Andrzejek]

I made my suggestion based on:

save an excel workbook with a defined name to a defined file path

strongm's code will allow user to save the file to any location with any name (right?), defaulting it to pre-defined path and file name.


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

Hence my point "if you really want to see a SaveAs dialog

 

[DylaBrion]

Hi All

Thanks for your help on this

I have updated the code as suggested and now get a 'Variable not defined message on the following line

With Application.FileDialog(msoFileDialogSaveAs)

I'm not sure what to define the variable as, can you please help

Thanks

Option Explicit
Sub SaveCRtoBotFolder()
Dim Title As String
Dim StartDate As String
Dim Name As String
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
Const csPath As String = "\\maple\data\Toronto\wrkgrp\wrkgrp33\DCIS BIA\Service Now Change Ticket\Input\"



'create data for file name
StartDate = [C11]
StartDate = Format(StartDate, "Long Date")
Title = [I13]

'save workbook with code under user defined name
Application.ScreenUpdating = False    ' Prevents screen refreshing.
Name = StartDate & " - CR Works - " & Title & ".xlsm"
CurrentFile = ThisWorkbook.Name

With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = csPath & CurrentFile
If .Show Then
ThisWorkbook.SaveAs Filename:=.InitialFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End If
End With

Application.ScreenUpdating = True

End Sub

 

[strongm]

> 'Variable not defined'

Odd. You are running this from within Exce,l yes? So msoFileDialogSaveAs should already be available as a global constant

It is defined as

Const msoFileDialogSaveAs = 2

 

[DylaBrion]

strongm

Many thanks

I have declared this as a Constant and it now works

 

[Andrzejek]

So strongm's post was helpful. Click on Great Post! link in his post to award him a star.


---- Andy

There is a great need for a sarcasm font.