Excel：将工作表导出为CSV文件，不离开当前的Excel工作表 [英] Excel: macro to export worksheet as CSV file without leaving my current Excel sheet

问题描述

这里有很多问题来创建一个宏来将工作表保存为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:

1. 它仅导出名为Sheet1的硬编码表;


2. 它总是导出到相同的临时文件，覆盖它;


3. 它会忽略区域设置分隔符。

为了解决这些问题，并满足我的所有要求，我修改了代码。我已经清理了一点，使其更易于阅读。

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:

1. 。关闭和 DisplayAlerts = True 应该在一个finally子句中，但我不知道如何在VBA中执行


2. 它只是工作如果当前文件名有4个字母，例如.xlsm。不能在.xls excel文件中工作。对于3个字符的文件扩展名，您必须在设置MyFileName时将 - 5 更改为 - 4 。


3. 作为附带效果，您的剪贴板将替换为当前工作表内容。

1. .Close and DisplayAlerts=True should be in a finally clause, but I don't know how to do it in VBA
2. 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.
3. 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屋！