CODEWITHSUNDEEP
excelvba
purplefolder
purplefolder

Reputation: 57

msoFileDialogSaveAs not writing file Excel VBA

Using the following code to copy some data on a master sheet, then add a new workbook, then paste the data. I then need to prompt using the msoFileDialogSaveAs because I need the user to be able to select different file types each time.

The problem is that when the box comes up to save the file, I can type a name then hit save, but it doesn't actually write the file. 

Public Sub ArchiveSheet()
Dim NewBook As Workbook
Dim CopyRange As Range
Set CopyRange = ActiveSheet.UsedRange
Dim lngCount As Long
CopyRange.Cells.Copy
Set NewBook = Workbooks.Add
Range("A1").PasteSpecial Paste:=xlPasteValues
    With NewBook
        .Title = "Archive"

    End With

     With Application.FileDialog(msoFileDialogSaveAs)
        .Show
     End With
Application.CutCopyMode = False

End Sub

Upvotes: 1

Views: 2105

Answers (2)

user19711266
user19711266

Reputation: 1

I know this is old but I had this issue today, have not seen this answer anywhere... And the solution was very simply to add the .Execute line, as below. Now the user hits Save (or Enter) and the selection executes.

 With Application.FileDialog(msoFileDialogSaveAs)
    .Show
    .Execute 
 End With

My first post here, sorry about my obvious lack of skills. I am not an advanced user. Thanks.

Upvotes: 0

Ralph
Ralph

Reputation: 9434

You are currently asking to user to indicate where he/she wants to save the file. But you are not using it. You need to save the return string like so:

dim strFileSelected as String
strFileSelected = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Save Excel file...")

Afterwards you can check if the user actually gave you a path and file name to save it to:

If strFileSelected = "False" Then
    MsgBox "You have cancelled"
else
    MsgBox "Saving file here:" & chr(10) & strFileSelected 
    ThisWorkbook.SaveAs Filename:=strFileSelected, FileFormat:=xlWorkbookNormal
End If

Note that you cannot save all Excel files using any kind of file extension. Example: if you have an open XML format Excel file open and try to save it using the .xls extension then you'll probably get an error message and you'll loose any kind of VBA code attached to the file (if you ignore the error message).

In short: you might want to elaborate on the above solution in order to make sure that the file format matches the selected extension using Debug.Print ThisWorkbook.FileFormat.

Upvotes: 1

Related Questions