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

上面代码的问题：

1. 我不知道关闭False 。它看起来像说在退出时保存。


2. .Close 和 DisplayAlerts = True 但是我不知道如何在VBA中做它


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


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

1. I don't know what Close False does. It looks like it says to save on exit.
2. .Close and DisplayAlerts=True should be in a finally clause, but I don't know how to do it in VBA
3. 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.
4. 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屋！