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, but here is the best answer, because it solves some problems:

1. 它导出当前工作表，而不仅仅是名为Sheet1"的硬编码工作表；
2. 它导出到一个名为当前工作表的文件
3. 它尊重区域设置分隔符.
4. 您继续编辑您的 xlsx 文件.

为了解决这些问题，并满足我的所有要求，我改编了此处的代码.我已经对其进行了一些清理以使其更具可读性.

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)
    With TempWB.Sheets(1).Range("A1")
      .PasteSpecial xlPasteValues
      .PasteSpecial xlPasteFormats
    End With        

    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. .Close 和 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 old files. For file extensions of 3 chars, you must change the - 5 to - 4 when setting MyFileName in the code above.
3. As a collateral effect, your clipboard will be substituted with current sheet contents.

将 Local:=True 与我的语言环境 CSV 分隔符一起保存.

put Local:=True to save with my locale CSV delimiter.

这篇关于Excel:宏将工作表导出为 CSV 文件，而无需离开我当前的 Excel 工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！