Clever Geek Handbook

Excel 2010 VBA: How to export current worksheet asking user where to save?

I have one .xlsm

with a large customizable toolbar full of custom tools for working with common situations.

The problem is that I often need to save the result and then send to other users, but I don't want to save the macros / custom toolbars to the saved workbook.

So I'm trying like this: add another workbook, copy / paste the actual worksheet into the new workbook, then I would like to force the save to open as.

'copy entire sheet
Cells.Copy
Range("B2").Select 'just to leave only one cell selected when I return 

'Paste into a new workbook sheet 1
Workbooks.Add
Cells.Select
Application.DisplayAlerts = False
ActiveSheet.Paste
Application.DisplayAlerts = True
ActiveSheet.Range("B2").Select 'select only one cell
With Application.FileDialog(msoFileDialogSaveAs)
    .Show
End With

The problem is that after I write the file name and select the destination folder and click "Save" , I didn't save anything! The file was not created at all.

EDIT 1

I guess I don't understand my intentions. I have a .xlsm which I am using to import and manage date from other sources. I got a sheet with all the data I needed (and it doesn't contain formulas). I need to create a new workbook, copy / paste a sheet into this new file, and then force the save as. At this point, I manually select the destination folder, enter the file name and select the file format and click Save. I need to be able to select these things at runtime

Actually the problem is that Excel doesn't actually save the new file and I don't know why.

Why?

+3


source to share


2 answers


Give it away, 1 more time ...



      Sub SaveASDiBox()
    Dim FlSv As Variant
    Dim MyFile As String
    Dim sh As Worksheet

    Set sh = Sheets("Sheet1")
    sh.Copy
    MyFile = "YourFileName.xlsx"
    FlSv = Application.GetSaveAsFilename(MyFile, fileFilter:="Excel Files (*.xlsx), *.xlsx)", Title:="Enter your file name")

    If FlSv = False Then Exit Sub

    MyFile = FlSv

    With ActiveWorkbook
        .SaveAs (MyFile), FileFormat:=51, CreateBackup:=False
        .Close False
    End With

End Sub
+6


source


The class FileDialog

allows the user to choose the filename, it doesn't actually save.

Once called, FileDialog.Show

you can access the property FileDialog.SelectedItems

to get the name (s) of the files selected by the user.



Then call Workbook.SaveAs

to save the file.

+1


source






More articles:


All Articles