Results 1 to 8 of 8

Thread: Macro Programming- need to generate a formatted text file from excel environment.

  1. #1

    Post Macro Programming- need to generate a formatted text file from excel environment.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,

    I am building an application where in i need some help to develop macros!
    I am new to VB macros and I am clueless to how to even proceed with the programming part.
    I have tried a lot of forums online and I couldnt get what I want. I am new to forum too.
    I will be grateful if some one can give me any suggestions on inputs on my requirements.
    This is solely for my work related stuff. I am on new job and cannot even say a NO!
    I have attached my macro at end of the post and also the sample Excel file which I am trying to work on. I am also attaching the desired output image (just to be more clear about the output I need.)

    My Problem:

    I have an excel sheet with approx 1200 columns-using Excel 2007 (columns: A to APS).
    The headers for the coulmns are specified in the second row. I need a program which can read the
    entire file like all the contents(row-wise) and put it in a formatted way to a text file. The formatting should be :
    Each row in the text file should start with a "+" sign and the values read from the excel should be
    formatted and placed in 8 characters long. I do not want the header data here!!


    In my data in Excel sheet I also have a challenge of comparing few headers feilds.



    To be precise,
    • Fields/Columns in the Excel data are fixed set.
    • I have a field called "drive type". If the drive type is CDD then the data in the row should be read else can be skipped.
    • The content in the field named Description in the Excel sheet is added as a comment in the text file generated so should start with '+'
    • I have some nos in the headers, If the value for that nos is blank in the rows then they should be skipped.
    • The first occurrence of the data is considered as the trigger point. (That should be also mentioned in text file)
    • Values are valid if it is x or x.5 (where x is a whole no. Rest all the values which do not equal this are ignored)
    • The count of how many values are obtained is kept for each DOF and is outputted.
    I have tried to write a small macro which is reading columns and also saving them in text file. I am attching that macro here:

    Code:
     
    Sub book()
    Dim sName As String
    Dim rng As Range, cell As Range
    sName = ActiveSheet.Name
    sName = Application.GetSaveAsFilename( _
    InitialFileName:=sName & ".txt", _
    FileFilter:="Text Files (*.txt),*.txt")
    If sName = "" Then Exit Sub
    Open sName For Output As #1
    Set rng = Range(Range("A2:NM2"), _
    Cells(Rows.Count, 1).End(xlUp))
    For Each cell In rng
    Print #1, cell.Text
    Print #1, cell.Offset(0, 1).Text
    Print #1,
    Next
    Close #1
    End Sub
    I really hope someone helps me out in this Forum!! I shall be greatful to you.

    Thanks again,
    newvbee

    <<Attachements goes here>>
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	output_desired.JPG 
Views:	227 
Size:	76.5 KB 
ID:	79  
    Attached Files Attached Files
    Last edited by newvbee; 2011-05-26 at 08:35 PM.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Here is where I would start...

    Don't worry about exporting the data to a text file yet. Just build the worksheet into the format for the data you want. To start, record a macro that:
    • Sorts the data by column O
    • Use autofilter to filter for everything except CDD
    • Delete the visible rows
    • Unfilter the data
    • Add a formula to your next empty column that reads (in row 3) ="+"&R3
    • Copy it down
    • Copy it and pastespecial values (you can then use this later where you need it)
    I don't really follow your comment on the nos, so I'll stop there.

    The big key is to try and build your sheet of data that you want in the text file first. Record the macro to do it, and we can tweak that later. Once you have the data formatted/adjusted to what you need in the text file, then we can create the text file.

    HTH,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Thanks for the help!

    Hi HTH,

    Thanks a lot for the valuble suggestions. Will work on that and get back!!

    Thanks,
    newvbee

    Quote Originally Posted by Ken Puls View Post
    Here is where I would start...


    Don't worry about exporting the data to a text file yet. Just build the worksheet into the format for the data you want. To start, record a macro that:
    • Sorts the data by column O
    • Use autofilter to filter for everything except CDD
    • Delete the visible rows
    • Unfilter the data
    • Add a formula to your next empty column that reads (in row 3) ="+"&R3
    • Copy it down
    • Copy it and pastespecial values (you can then use this later where you need it)
    I don't really follow your comment on the nos, so I'll stop there.

    The big key is to try and build your sheet of data that you want in the text file first. Record the macro to do it, and we can tweak that later. Once you have the data formatted/adjusted to what you need in the text file, then we can create the text file.

    HTH,

  4. #4
    Hello HTH,
    As suggested I tried to add the autofilter but I am unable to make one of my conditions. Like to match the numbs in headers to either x or x.5 where x is any integer.
    Can you please suggest me how to go about this and also I didnt understand why to delete visible rows.
    I would greatly appreciate your help. I am sorry if the questions appear too trivial. I am very new to the whole world of Programming and this is my first assignment. I hope you understand my situtation.

    Thanks,
    Newvbee

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Actually, it's Ken. HTH is shorthand for "Hope this helps".

    Unless I misunderstood, you're trying to thin down your records to only include those with CDD in column O. So the purpose of the autofilter is to show all the records that are not equal to CDD, and delete them. By deleting only the visible rows, then we leave the CDD records alive.

    I'll try to take a further look at this tonight if I can carve out some time.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Quote Originally Posted by Ken Puls View Post
    Actually, it's Ken. HTH is shorthand for "Hope this helps".

    Unless I misunderstood, you're trying to thin down your records to only include those with CDD in column O. So the purpose of the autofilter is to show all the records that are not equal to CDD, and delete them. By deleting only the visible rows, then we leave the CDD records alive.

    I'll try to take a further look at this tonight if I can carve out some time.
    Haha!! Sorry it didn't flash to me and sorry about it! Well Ken thanks a lot for the help!! I did understand what you are aiming at!! But I want to find out how to compare values and pull out only ones needed. Like in the data sheet I need values from specific column, like the. Even if you could like let me know how to go about the looping stuff maybe I will be able to work on some stuff.

    Here are the columns which I am concerned about!

    Drive Type, Date ID, Description, DID and all numeric headers like-> 0.25 ,0.5 ,0.75, 1, 1.25, etc till EOF.
    Thanks,
    newvbee
    Last edited by newvbee; 2011-06-01 at 06:29 PM.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    I'm getting confused. When you're programming, you need to have a clear picture of the steps that you need to go through in order to program it. Your last explanation doesn't tie together with what you had originally.

    If you follow the process that I gave above, then you should be able to cut your recordset down quickly (efficiently). After you get the recordset narrowed, that's when you turn to looping as loops are slow. Best to do it on a smaller subset of data.

    I'm using Excel 2010 here, so it's a bit difficult for me to record the initial code, as the properties and methods for working with the autofiler have been updated. If you can post that part, then I can work with you to add a loop to check the rest of the stuff. What I've suggested doesn't kill off any columns of data... we look at that part a bit later.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8

    Hey Ken,
    Thanks for the suggestions I was able to finish my task with the steps provided.

    Newvbee

    Quote Originally Posted by Ken Puls View Post
    I'm getting confused. When you're programming, you need to have a clear picture of the steps that you need to go through in order to program it. Your last explanation doesn't tie together with what you had originally.

    If you follow the process that I gave above, then you should be able to cut your recordset down quickly (efficiently). After you get the recordset narrowed, that's when you turn to looping as loops are slow. Best to do it on a smaller subset of data.

    I'm using Excel 2010 here, so it's a bit difficult for me to record the initial code, as the properties and methods for working with the autofiler have been updated. If you can post that part, then I can work with you to add a loop to check the rest of the stuff. What I've suggested doesn't kill off any columns of data... we look at that part a bit later.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •