Forum Discussion

UG_1180's avatar
UG_1180
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 .csv format. How can I do ??

 

Thank you 

Umang 

Macros and VBA
  • HansVogelaar's avatar
    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

7 Replies

  • HansVogelaar's avatar
    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's avatar
      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's avatar
        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

Resources