SOLVED

How to convert and save particular columns of a .xlsm file to .csv format

Copper Contributor

Hello microsoft excel comunity,

 

I want to create a vba button to save first four columns of the active sheet(the sheet on which the button is created) of a excel file which is in .xlsm format to .csv format. How can I do ??

 

Thank you 

Umang 

7 Replies

@UG_1180 

Sub Export4()
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wbkTarget As Workbook
    Dim wshTarget As Worksheet
    Set wbkSource = ActiveWorkbook
    Set wshSource = ActiveSheet
    Set wbkTarget = Workbooks.Add(Template:=xlWBATWorksheet)
    Set wshTarget = wbkTarget.Worksheets(1)
    wshSource.Range("A:D").Copy
    wshTarget.Range("A1").PasteSpecial Paste:=xlPasteValues
    wbkTarget.SaveAs Filename:=wbkSource.Path & Application.PathSeparator & "Export.csv", FileFormat:=xlCSV
    wbkTarget.Close
End Sub

@Hans Vogelaar 

Thank you so much for the reply.

 

File export into .csv is working good for me with this code but two issues-:

1) Column 1 in my excel contains time and date in yyyy-mm-dd hh:mm:ss format but when I export, it converts to a number format. How can I keep the same formatting??

2) I want to change the name of the exported file (.csv file) to include time and date at which it is being saved so that every time the file is not saved with the same name and it is easy to manage the saved files with different names. Will it be possible??

 

Thank you.

Umang 

best response confirmed by UG_1180 (Copper Contributor)
Solution

@UG_1180 

See if you can adapt this to suit your needs:

Sub Export4()
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wbkTarget As Workbook
    Dim wshTarget As Worksheet
    Set wbkSource = ActiveWorkbook
    Set wshSource = ActiveSheet
    Set wbkTarget = Workbooks.Add(Template:=xlWBATWorksheet)
    Set wshTarget = wbkTarget.Worksheets(1)
    wshSource.Range("A:D").Copy
    wshTarget.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    wbkTarget.SaveAs Filename:=wbkSource.Path & Application.PathSeparator & _
        "Export" & Format(Now, "_yyyy_mm_dd_hh_nn_ss") & ".csv", FileFormat:=xlCSV
    wbkTarget.Close
End Sub
Hello Hans,
This works perfectly!!!! :)

Thanks a lot.

@Hans Vogelaar 

Hello Hans,
I just need a small modification in the code so that it does not copy the last row of the first column while exporting it to the .csv file.

 

Can you please help as I tried myself but its not working out properly.

Thank You

@UG_1180 

Does this do what you want?

Sub Export4()
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wbkTarget As Workbook
    Dim wshTarget As Worksheet
    Set wbkSource = ActiveWorkbook
    Set wshSource = ActiveSheet
    Set wbkTarget = Workbooks.Add(Template:=xlWBATWorksheet)
    Set wshTarget = wbkTarget.Worksheets(1)
    wshSource.Range("A:D").Copy
    wshTarget.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    wshTarget.Range("A" & wshTarget.Rows.Count).End(xlUp).ClearContents
    wbkTarget.SaveAs Filename:=wbkSource.Path & Application.PathSeparator & _
        "Export" & Format(Now, "_yyyy_mm_dd_hh_nn_ss") & ".csv", FileFormat:=xlCSV
    wbkTarget.Close
End Sub
1 best response

Accepted Solutions
best response confirmed by UG_1180 (Copper Contributor)
Solution

@UG_1180 

See if you can adapt this to suit your needs:

Sub Export4()
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wbkTarget As Workbook
    Dim wshTarget As Worksheet
    Set wbkSource = ActiveWorkbook
    Set wshSource = ActiveSheet
    Set wbkTarget = Workbooks.Add(Template:=xlWBATWorksheet)
    Set wshTarget = wbkTarget.Worksheets(1)
    wshSource.Range("A:D").Copy
    wshTarget.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    wbkTarget.SaveAs Filename:=wbkSource.Path & Application.PathSeparator & _
        "Export" & Format(Now, "_yyyy_mm_dd_hh_nn_ss") & ".csv", FileFormat:=xlCSV
    wbkTarget.Close
End Sub

View solution in original post