将特定的单页另存为.csv [英] Save Specific single sheet as .csv

问题描述

我有一个与此帖子非常相似的问题:将各个Excel工作表另存为CSV

I have a very similar question as this post: Save individual Excel sheets as CSV

我的问题不同，我只需要保存一张纸这是该帖的答案

My question differs in that I only need one sheet to be saved This is the answer from that post

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.SaveAs "C:\docs\" & ws.Name & ".csv", xlCSV
Next

此代码将每个工作表保存到一个特定的文件夹，并将文件命名为与工作表相同的名称.我看到2个问题:

This code saves each worksheet to a specific folder and names the file the same as the worksheet. 2 problems I see:

1-您当前正在使用的工作簿将成为代码保存的最后一个工作表.如果要继续使用工作簿，则必须打开原始文件.如果打开一个新工作簿并与正在处理的工作簿分开保存，效果会更好.

1- The workbook you were currently working on becomes the very last worksheet the code saved. If you want to keep working on your workbook you have to open the original file. It would work better if a new workbook was opened and saved separately from the one that is being worked on.

2-保存每个工作表.我只需要保存一个特定的工作表，即 Sheet2

2- It saves each worksheet. I only need to save one specific worksheet, i.e Sheet2

我找到了这个其他代码，但是我对VBA还是很陌生，只知道如何创建宏，然后复制并复制.将代码粘贴到其中.运行代码时出现错误.

I found this other code but I am VERY new to VBA and only know how to create a macro, copy & paste code into it. I get error when I run the code.

Sub test()

Application.DisplayAlerts = False

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close

Application.DisplayAlerts = True

End Sub

希望我能朝正确的方向前进.谢谢！

Hopefully I can get pushed in the right direction. Thanks!

推荐答案

1. strSourceSheet 是具有您要导出的工作表的名称/编号的工作表
2. strFullname 是带有csv路径的文件名.就这么简单.

1. strSourceSheet is the sheet which has the name/number of the sheet which you want to export
2. strFullname is the filename with path of the csv. As simple as that.

现在查看此代码

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, _
                      FileFormat:=xlCSV, _
                      CreateBackup:=True
ActiveWorkbook.Close

假设我要复制 Sheet2 ，然后将其写为

Let's say I want to copy Sheet2 then I would write it as

Dim strSourceSheet As String
Dim strFullname As String

'~~> Change the below two lines as per your requirements
strSourceSheet = "Sheet2"
strFullname = "C:\Temp\MyCsv.Csv"

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, _
                      FileFormat:=xlCSV, _
                      CreateBackup:=True
ActiveWorkbook.Close

您是否注意到我们如何在 ThisWorkbook 和 ActiveWorkbook 之间切换.当您复制工作表时，将创建一个新工作簿并激活它，因此要关闭它，我们将使用 ActiveWorkbook

Did you notice how we switch between ThisWorkbook and ActiveWorkbook. When you copy the sheet a new workbook is created and becomes active and hence to close that we use ActiveWorkbook

希望这会有所帮助.

这篇关于将特定的单页另存为.csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！