Forum Discussion

Copper Contributor

Oct 11, 2021

Solved

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

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 ...

Macros and VBA

HansVogelaar

Oct 12, 2021

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

HansVogelaar

MVP

Oct 11, 2021

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

UG_1180

Copper Contributor

Oct 12, 2021

HansVogelaar

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

HansVogelaar

MVP

Oct 12, 2021

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

UG_1180

Copper Contributor

Oct 19, 2021

HansVogelaar

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

HansVogelaar

MVP

Oct 19, 2021

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

UG_1180
Copper Contributor
Oct 12, 2021
Hello Hans,
This works perfectly!!!! 🙂

Thanks a lot.