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?
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
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.