Macro to generate a txt or csv file from Excel

Hi, could someone help me to write a macro to create text file from excel...?

here is my situation...

Data selection:
i have data filled from column A to column AG.
i need to export only certain columns of data from excel to a text format.
the data range is columns B, W, Z, AD, AE and AF.
there are data in several rows but the end row should be if column B becomes empty (no value in it).

Output
text file saved to the desktop.

file name = "TRACKING" + date "yyyymmdd"
(e.g.) TRACKING20130911.txt
LVL 1
greghollAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

greghollAuthor Commented:
P.S.

Actually, csv would be preferable, so the filename is the example would come out as TRACKING20130911.csv

THANKS!!
0
ButlerTechnologyCommented:
The following code with a few modification should get you going in the right directions.
Sub WriteCSV()
Dim FileLocation As String
Dim FileName As String
Dim csvLine As String
  
  FileLocation = "c:\temp\"
  FileName = "Tracking" & Format(Now(), "YYYYMMDD") & ".csv"
  Set FSO = CreateObject("Scripting.FileSystemObject")

  Set oFile = FSO.CreateTextFile(FileLocation & FileName)
  Range("B2").Select

Do
  ' This line should be use to add all of your columns with off-sets
  csvLine = ActiveCell.Value & ", " & ActiveCell.Offset(0, 2).Value & ", " & ActiveCell.Offset(0, 3).Value
  oFile.WriteLine csvLine
  ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell.Value)
oFile.Close
End Sub

Open in new window


You should change where you want the files to be located.  The csvLine should be expanded to cover all of the columns that you require.  Remember that the off-set is from Column B -- not Column A.
0
ButlerTechnologyCommented:
My CSVLine includes the following columns: B, D (2), E(3).

Tom
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ButlerTechnologyCommented:
This one should work with the only modification being where you want the file to be create.  You should adjust the FileLocation = "C:\temp" to a new location.  Also, if you are just doing a quick test and that folder doesn't exist -- you will get an error message.
Sub WriteCSV()
Dim FileLocation As String
Dim FileName As String
Dim csvLine As String
  
  FileLocation = "c:\temp\"
  FileName = "Tracking" & Format(Now(), "YYYYMMDD") & ".csv"
  Set FSO = CreateObject("Scripting.FileSystemObject")

  Set oFile = FSO.CreateTextFile(FileLocation & FileName)
  Range("A2").Select

Do
  csvLine = ActiveCell.Range("B1").Value & ", " & ActiveCell.Range("W1").Value & ", " & ActiveCell.Range("Z1").Value & ", " & ActiveCell.Range("AD1").Value & ", " & ActiveCell.Range("AE1").Value & ", " & ActiveCell.Range("AF1").Value
  oFile.WriteLine csvLine
  ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell.Range("B1"))
oFile.Close
End Sub

Open in new window


It may seem a little odd that the code use cells B1, W1, ... etc.  This is because they are relative to the line of the active cell.   This makes the code a little more readable and easier to code.  I find it challenging working with offsets.

Tom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ButlerTechnologyCommented:
Does the above code suggestions work?
0
greghollAuthor Commented:
I'm back. Sorry; long story. Anyway...

Where exactly do I go in Excel 2010 to input this code and build the macro? Thx
0
ButlerTechnologyCommented:
I would recommend first recording a simple macro.  This will create the Personal Macro Workbook which is very valuable when working with macros as it is always available.  You may have to turn on the developer tab in the ribbon.  I am using Excel 2010 for my instructions.

Enable Developer Ribbon
Select File | Options
Select Customize Ribbon
Check Developer Option (List is on the right hand side)

Record Simple Macro
Select The Developer Ribbon
Select Record Macro
Select Personal Macro Workbook for where to stored the macro
Select OK
Type Your Name is a cell and Push enter
Select Stop Recording (Developer Tab

Adding Code
Open the VBA Editor -- ALt-F11
Expand on the Personal.xlsb worksheet
Expand on Modules
Open any Module and copy the code

You can execute the code by pushing F5 or by Selecting Macros (Developer Ribbon).  You should make sure that he worksheet that you want the code to work against is the active sheet.  Another important note that undo does not work with Macros.

Tom
0
greghollAuthor Commented:
Thanks to all!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.