Excel:宏导出工作表为CSV文件,而不离开我当前的Excel工作表 [英] Excel: macro to export worksheet as CSV file without leaving my current Excel sheet
问题描述
这里有很多问题来创建一个宏来将工作表保存为CSV文件。所有答案都使用SaveAs,例如来自SuperUser的 this one 。他们基本上说创建一个像这样的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. I've adapted the code from here. I've cleaned it a little to make 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
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 False
Application.DisplayAlerts = True
End Sub
上面代码的问题:
- 我不知道
关闭False
。它看起来像说在退出时保存。 -
.Close
和DisplayAlerts = True
但是我不知道如何在VBA中做它 - 它只有当前文件名有4个字母,如.xlsm。不工作在.xls excel文件。对于3个字符的文件扩展名,您必须在设置MyFileName时将
- 5
更改为- 4
。 - 作为附属效果,您的剪贴板将替换为当前工作表内容。
- I don't know what
Close False
does. It looks like it says to save on exit. .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工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!