Oct 11 2021 09:03 AM
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
Oct 11 2021 09:16 AM
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
Oct 11 2021 05:22 PM
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
Oct 12 2021 02:01 AM
SolutionSee 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
Oct 12 2021 06:04 AM
Oct 19 2021 08:58 AM
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
Oct 19 2021 10:48 AM
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
Oct 19 2021 04:57 PM
Yes perfect!! Thank You so much!:)
Oct 12 2021 02:01 AM
SolutionSee 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