将工作表导出为CSV文件

将工作表导出为CSV文件

本文介绍了Excel:将工作表导出为CSV文件,不离开当前的Excel工作表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

这里有很多问题来创建一个宏来将工作表保存为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工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-31 08:46