I've a .xlsm
with a big customized toolbar full of custom tools for handling common situations at work.
我有一个带有大型自定义工具栏的.xlsm,其中包含用于处理工作中常见情况的自定义工具。
The problem is that I need often to save the result and then send to other users, but I don't want to preserve macros / customized toolbars into saved Workbook.
问题是我经常需要保存结果然后发送给其他用户,但我不想将宏/自定义工具栏保存到已保存的工作簿中。
So I'm trying this way: add another workbook, copy/paste the actual worksheet into the new workbook, then I'd like to force opening of save 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 file name and choose destination folder and press 'Save', I got nothing saved ! No file created at all.
问题是,在我写完文件名并选择目标文件夹并按“保存”后,我什么都没保存!根本没有创建文件。
EDIT 1
编辑1
Probably I'm not clear in my intention. I've a .xlsm that I use to import and manipulate date from other sources. I got a sheet with all data I need (and this contains no formulas). I need to create a new workbook, copy/paste the sheet into this new file, then force opening of save as. At this point I manually select destination folder, enter file name and choose a file format, and press Save. I need to be able to choose these things at runtime
可能我不清楚我的意图。我有一个.xlsm用于从其他来源导入和操作日期。我得到了一张包含我需要的所有数据的表(这里没有公式)。我需要创建一个新的工作簿,将工作表复制/粘贴到这个新文件中,然后强制打开另存为。此时,我手动选择目标文件夹,输入文件名并选择文件格式,然后按“保存”。我需要能够在运行时选择这些东西
Actually the problem is that Excel doesn't really save the new file and I don't know why.
实际上问题是Excel并没有真正保存新文件,我不知道为什么。
Why?
为什么?
2 个解决方案
#1
6
Give this a go, 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
#2
1
The FileDialog
class allows the user to select a filename, it doesn't actually do the saving.
FileDialog类允许用户选择文件名,它实际上不进行保存。
After calling FileDialog.Show
you can access the FileDialog.SelectedItems
property to get the name(s) of the file(s) selected by the user.
调用FileDialog.Show后,您可以访问FileDialog.SelectedItems属性以获取用户选择的文件的名称。
Then call Workbook.SaveAs
to save the file.
然后调用Workbook.SaveAs来保存文件。
#1
6
Give this a go, 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
#2
1
The FileDialog
class allows the user to select a filename, it doesn't actually do the saving.
FileDialog类允许用户选择文件名,它实际上不进行保存。
After calling FileDialog.Show
you can access the FileDialog.SelectedItems
property to get the name(s) of the file(s) selected by the user.
调用FileDialog.Show后,您可以访问FileDialog.SelectedItems属性以获取用户选择的文件的名称。
Then call Workbook.SaveAs
to save the file.
然后调用Workbook.SaveAs来保存文件。