Results 1 to 8 of 8

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

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

    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:

    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,
    Close #1
    End Sub
    I really hope someone helps me out in this Forum!! I shall be greatful to you.

    Thanks again,

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

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

Posting Permissions

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