How to create a manipulated CSV file from XLS using VBA

Question

I have an excel file that I need to convert into a CSV. This part is fine, I wrote this code to do so:

Private Sub createCSV()
Dim path As String
Dim ws As Excel.Worksheet

path = "\\networkshare\mydir"

For Each ws In Application.ActiveWorkbook.Worksheets
    ActiveSheet.Range("1:2").EntireRow.Delete       'Delete the first two rows
    ws.SaveAs path & ws.Name, xlCSV
Next


End Sub

However,

I need to only save specific columns to CSV in a certain order.

Column A,B of excel worksheet to Column D,E of CSV and Column C,D of worksheet as column A,B of CSV,etc

Is there a way to do this?

Move data from column: {A, B} to E and then delete empty columns: {A, B} ;) — Maciej Los
– Maciej Los, Commented Oct 13, 2020 at 11:46
You could create a new sheet, copy your data to that sheet and save the new sheet, or your could loop the data and create your own CSV using the FileSystemObject. — Kevin
– Kevin, Commented Oct 13, 2020 at 11:50

FaneDuru · Accepted Answer · 2020-10-13 12:11:39Z

1

Try the next code, please (take care of the last "" in path string):

Private Sub createCSV()
 Dim ws As Worksheet, path As String, arr As Variant, lastRow As Long

 path = "\\networkshare\mydir\" 'take care of the last "\"!!!

 For Each ws In ActiveWorkbook.Worksheets
    ws.Range("1:2").EntireRow.Delete                   'Delete the first two rows
    lastRow = ws.Range("A" & rows.count).End(xlUp).row 'last row of the sheet
    arr = ws.Range("A1:D" & lastRow).Value             'put the range in an array
    'switch the array columns
    arr = Application.Index(arr, Evaluate("row(1:" & UBound(arr) & ")"), Array(3, 4, 1, 2))
    ws.Range("A1").Resize(UBound(arr), UBound(arr, 2)).Value = arr 'drop the adapted array back in the sheet
    ws.SaveAs path & ws.Name & ".csv", xlCSV          'save the sheet as csv
 Next
End Sub

edited Oct 13, 2020 at 12:11

answered Oct 13, 2020 at 12:05

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

How to create a manipulated CSV file from XLS using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related