vba - 将 XLSX 工作表(多个)导出为带有嵌套 if 语句的单独 CSV 表

Question

我正在尝试将 15 个工作表从 excel 工作簿导出到 CSV 文件。该工作簿总共包含 18 张纸。前三张表不需要导出，因为它们包含原始数据、指令和计算。

生成的 CSV 文件用于导入 Microsoft Dynamics AX。在另存为 csv 之前，我需要清除任何包含“~”的 cel。但只能从 15 张用于导出。

我在这个论坛上发现了多个允许我成功创建 csv 的线程。

我的 VBA 最终看起来像这样:(将几个代码集组合在一起)

Sub SaveWorksheetsAsCsv()
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
SaveToDirectory = "C:\Temp\Prices\CSV\"
For Each WS In ThisWorkbook.Worksheets
If WS.Name <> "Instructions" And WS.Name <> "Parameters" And WS.Name <> "BI Data & Worksheet" Then

    Sheets(WS.Name).Copy
    For Each Cell In [b:b]
    If Cell.Value = "~" Then Cell.ClearContents
    'put any value you want here
    Next Cell
    Sheets(WS.Name).Copy
    For Each Cell In [A:A]
    If Cell.Value = "~" Then Cell.ClearContents
    'put any value you want here
    Next Cell


    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & WS.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=False
    ThisWorkbook.Activate
End If
Next
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.
End Sub

我遇到的一个问题是，虽然导出运行良好，并且 CSV 看起来很棒，但所有临时“复制”工作簿不再由 ActiveWorkbook.Close SaveChanges:=False 关闭。陈述。

我确信这与我的嵌套 IF 语句有关，但无法弄清楚我做错了什么。

Answer 1

完成后您可以手动关闭它们。

    Dim wb As Workbook

    Application.DisplayAlerts = False

    'Loop though each workbook
    For Each wb In Application.Workbooks

        'Activate the workbook
        wb.Activate
        if wb.name = "something you want to close" then
            wb.Close
        end if

    Next wb

    Application.DisplayAlerts = True

编辑:有关原始问题的更多信息。看评论

For Each WS In ThisWorkbook.Worksheets
If WS.Name <> "Instructions" And WS.Name <> "Parameters" And WS.Name <> "BI Data & Worksheet" Then

    'By doing this copy, Excel is opening an additional workbook.
    'Let's call it "Book1"
    'Book1 is now active
    Sheets(WS.Name).Copy
    For Each Cell In [b:b]
    If Cell.Value = "~" Then Cell.ClearContents
        'put any value you want here
    Next Cell

    'By doing this copy, Excel is opening an additional workbook.
    'Let's call it "Book2".
    'Book2 is now active. Book1 is no longer active.
    Sheets(WS.Name).Copy
    For Each Cell In [A:A]
    If Cell.Value = "~" Then Cell.ClearContents
         'put any value you want here
    Next Cell

    'This is working on the active workbook "Book2"
    'Book1 is no longer active so it will not get saved or closed.
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & WS.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=False
    ThisWorkbook.Activate
End If
Next

如果你只复制一次，我认为它会按照你想要的方式工作。

如果要将工作表复制到同一个工作簿中，则必须使用 After:=Sheets("SheetName") 参数。这样您就不必担心合上书本了。

Sheets(WS.name).Copy After:=Sheets("SomeSheetName") 
ActiveSheet.Name = "SomeNewSheetName"

希望有帮助。