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

Resources