Excel VBA Exporting specific columns to CSV

Question

I'm fairly new to VBA programming, but still managed to make a small code which exports an Excel sheet to a CSV file. This must be as user friendly as possible, so I came up with this:

Sub export_first()

    Application.DisplayAlerts = False

    Dim wb As Workbook, InitFileName As String, fileSaveName As String

    InitFileName = ThisWorkbook.Path & "\Export nr1_" & Format(Date, "yyyymmdd")

    Sheets("MySheetNameToExport").Copy

    Set wb = ActiveWorkbook

    fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
    fileFilter:="Semicolon separated CSV (*.csv), *.csv")

    With wb
        If fileSaveName <> "False" Then

            .SaveAs fileSaveName, FileFormat:=xlCSV, Local:=True
            .Close
        Else
            .Close False
            Exit Sub
        End If
    End With

End Sub

This works just fine. It is added to a button, and when the button is pressed, the codes does all the job. Now the thing is, my coworkers came up with an idea, that we should use columns that checks some values for the users (lets call them C, D) but when they export the worksheet, these columns must be ignored and not exported. What I tried to do is to extend the Sheets line like:

Sheets("MySheetNameToExport").Range("A:B, E:G").Copy

But when I add this modified line to my macro and run the code, Excel crashes and exports the very first sheet which is just some welcome text and is totally unnecessary.

tlemaster · Accepted Answer · 2016-12-13 12:29:39Z

1

I suggest first hiding columns C and D and then copying visible cells only from your range.

Columns("C:D").EntireColumn.Hidden = True
Sheets("MySheetNameToExport").Range("A:G").SpecialCells(xlCellTypeVisible).Copy

answered Dec 13, 2016 at 12:29

tlemaster

8595 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Miller Over a year ago

Thank you, this is indeed a great way to do it, and I really like the idea! However if I implement your code and run the script, this still happens: the "select to save somewhere" box comes up, I hit okay, then the VBA project closes itself, all of the sheets get closed and only the main grey Excel and VBA window stays in front of me... And yeah almost forget: the first information sheet gets exported. Like only the very first sheet.

James Miller Over a year ago

Okay, an another expert from another forum pointed out what the problem was. The SaveAs method was the culprit.

Set NewSheet = Sheets.Add     Sheets("Work1").Range("X:Y").Copy Destination:=NewSheet.Range("A1")     NewSheet.Move     Set wb = ActiveWorkbook

This did the trick.

Collectives™ on Stack Overflow

Excel VBA Exporting specific columns to CSV

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related