Creating A CSV Output With An Excel Macro (Part 2)

Excel macros

In yesterday’s post, I created a very simple macro that would take a range of data on a given sheet and then save it as a csv on my computer. It’s a simple task but one that we often end up doing manually for no reason. If you missed that post, find it here:

Creating A CSV Output With An Excel Macro (Part 1)

Today, I wanted to add 2 very simple features:

#1-add today’s date in the name of the file

If you’re saving specific data, chances are that you’ll want or need to keep it all in a folder and be able to go back in time to see what the data was 1 or 2 days ago. Simple enough right? And yes, it’s very easy to do even in excel. How? Look at the original code that we used:

******************************************
Sub Macro1()
Sheets(“YUA”).Select
Range(“A4:H7”).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir “C:\Users\cantinpe\Desktop”
ActiveWorkbook.SaveAs Filename:=”C:\Book2.csv”, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

******************************************

To do this, I’ll simply add a few more lines:

******************************************
Sub Macro1()

date1 = Date
date1 = Format(date1, “YYYYMMDD”)

Sheets(“YUA”).Select
Range(“A4:H7”).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir “C:\Users\cantinpe\Desktop”
ActiveWorkbook.SaveAs Filename:=”L:\DOC_” & date1 & “.csv“, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

******************************************

This will help me save a file using the date in the name.

#2-add a button to easily run the macro

I’ve done this in a few past files but it’s a very recurring question so let’s do this. First I’ll go to the developer part of my Excel:

Then, I’d simply go to design mode and click insert, add a macro:

And assign that macro:

Simple enough?:) I hope so

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************