Creating A CSV Output With An Excel Macro

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

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

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


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

One Feedback on "Creating A CSV Output With An Excel Macro"

ask

Hello, after reading this awesome post i am too happy to share my experience here with
colleagues.



Comments

Please Leave a Comment!





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