如何将Excel工作表中的表保存为VBA中的CSV并继续处理上一个文件

ftf50wuq 于 2023-09-27 发布在其他

关注(0)|答案(1)|浏览(134)

我试图从不同的工作表中导出表格，如果有一个修改，作为CSV数据。
我的代码从文件中导出所有表。如何使它只导出我正在执行宏的当前表？

Public Sub SaveWorksheetsAsCsv()
Dim xWs As Worksheet
Dim xDir As String
Dim folder As FileDialog
Set folder = Application.FileDialog(msoFileDialogFolderPicker)

If folder.Show <> -1 Then Exit Sub

xDir = folder.SelectedItems(1)

For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.SaveAs xDir & "\" & xWs.name, xlCSV
Next

End Sub

我想出了一个方法，但现在窗口打开为CSV文件。如何关闭csv文件并重新打开正在处理的工作表？

Public Sub SaveWorksheetsAsCsvUndercarriageDefinition()
Dim wbk As Workbook
Dim xWs As Worksheet
Dim xDir As String
Dim folder As FileDialog

Set wbk = Workbooks("Vba_Fehlerprüfung.xlsm")
Set xWs = wbk.Worksheets("Undercarriage Definition")
Set folder = 
Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)
'For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.SaveAs xDir & "\" & xWs.name, xlCSV
'Next
End Sub

csv

来源：https://stackoverflow.com/questions/73189276/how-to-save-table-in-excel-worksheet-as-csv-in-vba-and-continue-working-on-the-p

1条答案

按热度按时间

krcsximq1#

我的建议是使用下面的子程序来导出一个表。列表对象

Sub exportListobject(lo As ListObject, csvFilename As String)
    
    Dim wbNew As Workbook
    Set wbNew = Workbooks.Add
    
    Dim wsNew As Worksheet

    With wbNew
        Set wsNew = wbNew.Sheets(1)
        lo.Range.Copy
        wsNew.Range("A1").PasteSpecial Paste:=xlPasteAll
        .SaveAs Filename:=csvFilename, _
            FileFormat:=xlCSVMSDOS, CreateBackup:=False
        .Close False
    End With

End Sub

它会将列表对象复制到一个新的工作簿中，将其保存为csv文件并关闭它。包含列表对象的工作簿将不会被触及。
如果你想从你的工作簿中导出一个工作表，你可以使用类似的子

Sub exportSheet(sh As Worksheet, csvFilename As String)

    Dim wbNew As Workbook
    Set wbNew = Workbooks.Add
    
    Dim wsNew As Worksheet

    With wbNew
        sh.Copy wbNew.Sheets(1)
        Set wsNew = wbNew.Sheets(1)
        .SaveAs Filename:=csvFilename, _
            FileFormat:=xlCSVMSDOS, CreateBackup:=False
        .Close False
    End With

End Sub

赞(0）回复(0）举报 2023-09-27

我来回答

如何将Excel工作表中的表保存为VBA中的CSV并继续处理上一个文件

1条答案

相关问题

热门标签

最新问答