问题描述
这里有很多问题来创建一个宏来将工作表保存为CSV文件。所有答案都使用SaveAs,例如来自SuperUser的。他们基本上就是要创建一个这样的VBA函数:
There are a lot of questions here to create a macro to save a worksheet as a CSV file. All the answers use the SaveAs, like this one from SuperUser. They basically say to create a VBA function like this:
Sub SaveAsCSV()
ActiveWorkbook.SaveAs FileFormat:=clCSV, CreateBackup:=False
End Sub
这是一个很好的答案,但我想执行导出,而不是另存为。当SaveAs被执行时,会导致两个烦恼:
This is a great answer, but I want to do an export instead of Save As. When the SaveAs is executed it causes me two annoyances:
- 我目前的工作文件变成CSV文件。我想继续在原来的.xlsm文件中工作,但是将当前工作表的内容导出到具有相同名称的CSV文件。
- 出现一个对话框,要求我确认我想重写CSV文件。
是否可以将当前工作表导出为文件,但要继续工作在我的原始文件?
Is it possible to just export the current worksheet as a file, but to continue working in my original file?
推荐答案
几乎我想要@Ralph。您的代码有一些问题:
Almost what I wanted @Ralph. Your code has some problems:
- 它仅导出名为Sheet1的硬编码表;
- 它总是导出到相同的临时文件,覆盖它;
- 它会忽略区域设置分隔符。
为了解决这些问题,并满足我的所有要求,我修改了。我已经清理了一点,使其更易于阅读。
To solve these problems, and meet all my requirements, I've adapted the code from here. I've cleaned it a little to make it more readable.
Option Explicit
Sub ExportAsCSV()
Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook
Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy
Set TempWB = Application.Workbooks.Add(1)
TempWB.Sheets(1).Range("A1").PasteSpecial xlPasteValues
Dim Change below to "- 4" to become compatible with .xls files
MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"
Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub
有还有一些小事情上面的代码你应该注意到:
There are still some small thing with the code above that you should notice:
-
。关闭
和DisplayAlerts = True
应该在一个finally子句中,但我不知道如何在VBA中执行 - 它只是工作如果当前文件名有4个字母,例如.xlsm。不能在.xls excel文件中工作。对于3个字符的文件扩展名,您必须在设置MyFileName时将
- 5
更改为- 4
。 - 作为附带效果,您的剪贴板将替换为当前工作表内容。
.Close
andDisplayAlerts=True
should be in a finally clause, but I don't know how to do it in VBA- It works just if the current filename has 4 letters, like .xlsm. Wouldn't work in .xls excel files. For file extensions of 3 chars, you must change the
- 5
to- 4
when setting MyFileName. - As a collateral effect, your clipboard will be substituted with current sheet contents.
编辑:put Local:= True
使用我的区域设置CSV分隔符保存。
put Local:=True
to save with my locale CSV delimiter.
这篇关于Excel:将工作表导出为CSV文件,不离开当前的Excel工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!