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

Excel macros

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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


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

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.