How to save table in Excelworksheet as CSV in vba and continue working on the previous file-CodePudding

Hey I am quite new to VBA and I am currently trying to export tables from different sheets, if there is an alternation made to it, as CSV data. Currently my code exports all tables from my file. How can I make it export only the current table that I am executing the makro on? Thank you for your help!

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

I figured out a way to do it but now the window opens as a CSV file. How do i close the csv file and reopen the worksheet I was working on?

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

CodePudding user response：

My suggestion would be to use the following sub in order to export a table resp. an listobject

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

It will copy the listobject in question into a new workbook, save it as an csv file and close it. The workbook which contains the listobject will not be touched.

If you want to export a single sheet from your workbook you can use a similar sub

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