Save only selected part of Excel-Sheet as CSV in VBA-CodePudding

I wrote a code to export my excel sheet as a CSV. Is it possible to only export a certain part of the excel sheet as CSV? I included a picture of the table that I am working with. I only want to export the data in the framed table (excluding the width/length/height)

This is my current code:

Sub exportSheet(sh As Worksheet, csvFilename As String)

Dim wbNew As Workbook
Set wbNew = Workbooks.Add
Dim folder As FileDialog
Dim xDir As String


Dim wsNew As Worksheet

With wbNew
    sh.Copy wbNew.Sheets(1)
    Set wsNew = wbNew.Sheets(1)
    Set folder = Application.FileDialog(msoFileDialogFolderPicker)
    If folder.Show <> -1 Then Exit Sub
     xDir = folder.SelectedItems(1)
    .SaveAs xDir & "/" & csvFilename, _
        FileFormat:=xlCSVMSDOS, CreateBackup:=False
    .Close False
End With

End Sub

Thank you for any help!

CodePudding user response：

I am assuming that you are using an Excel table in your worksheet. I would also give the table a proper name so that it is easier to work with (On the Ribbon > Table Design > Table Name) Name an Excel Table. Excel tables are known as ListObjects in VBA. You can create a ListObject variable in VBA and use it to copy the required data.

Dim lo As ListObject
Set lo = ThisWorkbook.Worksheets("<your sheet name>").ListObjects("<your table name>")

Then, instead of using

sh.Copy wbNew.Sheets(1)

you could use

lo.Range.Copy wbNew.Sheets(1).Range("A1")

This would only copy the data contained in the table to the new file. In case you only want the data, without the table headers, you can use the DataBodyRange property of the list object:

lo.DataBodyRange.Copy wbNew.Sheets(1).Range("A1")

You may need to pass an additional argument for the table name in your sub-routine. Refer to the Offical VBA Reference on ListObject for more details.

CodePudding user response：

if just wanting to export a certain section, it would be best to write your own CSV output routine, taking a range as input , and boolean for headers.

Public Sub ExportRangeAsCSV(thisSelection As Range, HeaderLine As String, outputFileName As String)


Dim ffNum As Long
Dim csvLine As String, cellVal As Variant, cellTxt As String
Dim i As Integer, j As Integer

csvLine = ""
ffNum = FreeFile

Open outputFileName For Output As #ffNum
If Not (HeaderLine = "") Then Print #ffNum, HeaderLine
For i = 1 To thisSelection.Rows.Count
    For j = 1 To thisSelection.Columns.Count
        
        cellVal = thisSelection(i, j).Value
        
        Select Case VarType(cellVal)
            Case VbVarType.vbDate
                cellTxt = VBA.Format(cellVal, "dd-mmm-yyyy")
            Case VbVarType.vbCurrency, VbVarType.vbDecimal, VbVarType.vbDouble, VbVarType.vbInteger, VbVarType.vbLong, VbVarType.vbSingle
                cellTxt = cellVal
            Case VbVarType.vbString, VbVarType.vbEmpty
                cellTxt = Chr(34) & cellVal & Chr(34)
            Case Else
                cellTxt = cellVal
        End Select
        csvLine = csvLine & cellTxt & ", "

    Next
    Print #ffNum, Left(csvLine, Len(csvLine) - 2)
    csvLine = ""
Next

Close #ffNum

End Sub

or use the method discussed elsehwere to copy your data as values to a new workbook, and use the builtin SaveAsCSV enter link description here