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()

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

In the past I’ve looked at introductions to excel macros. If you’re a beginner I recommend that you take a look at:

–Excel Macros – What Are Excel macros
–-Excel Macros – How To Build Excel Macros
–Excel Macros – How To Build Excel Macros Part II

-Our Excel Beginner’s book

One of the more common things that I do with my work macros is the creation of files. So I’d have one big file where all of my data resides. Then, every day, I’ll need to either save to a separate file or send by email some of that data, sometimes in an excel file but even more commonly in a .csv format. There is very little difference between the 2 in any case.

Today, I thought I’d do a very small into to how that can be done. Automating this process reduces errors but also makes it much easier to do. This can be used to send data to a co-worker, a client, supplier, etc.

Suppose that you have a big spreadsheet with dozens of sheets and you need to save one specific range to a csv. How would you do it? Simple. You’d create a macro that looks a little like this:

******************************************
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
******************************************

As you can imagine, this would take the sheet “YUA”, copy the specific range and save it to my C drive.

Other steps I could then do would be saving the file using today’s date and adding a button to make it even easier. I’ll try to do that in my next post:)

Haha.. I was personally surprised. I’m very much into competitions, especially for anything sports-related. I’m also very interested in competitions for other activities such as chess, or even video games. But I had never heard of an “excel” competition… But it exists and has over $30,000 in prizes which is certainly not bad. I’d imagine it’s also a great place to showcase ability to get a job or a contract right? I can’t even imagine what type of candidates give it a try but it must be pretty fascinating:)

In my last post, I looked into using a DOS or CMD program to list all files in a given directory. That worked out well and it generated a .txt file that you should be able to use if you’re able to use the other functions that I’ve discussed on this blog. So let’s give it a start. First off, to answer a question, yes you can open .txt files in excel. You simply click on “file/open” and then select all files:

Then, you’ll be asked if you want to convert the information into columns. Sometimes, especially for some .csv files, that can work. But in this case I’ll just click on finish which will give me the result:

Depending on what I’ll be doing, I could need to combine this and other lists but it’s important to have the full path for each file. How? Simply by going into cell G8 and using:

=”L:/ETF Mmaking/Pete/Hist/” & F8

This is the result:

Then, I could simply write a macro that would go through each of the files with a loop for example to do search and replaces, add a logo or anything else:)