Macro programming- how to generate text file with pre formatting.

[new2vbee]

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 :

1. 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.
• The headers are in second row.- (They are made bold in the attached file-to identify it easiliy)
• 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 like it should begin 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 entry 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 Date id. It is written in the text file with the DID & the corresponding obtained values. (this is done for each Date id)

MAIN Stuff:

I need the text file to be generated though excel itself. Like to have a button or something!


I have reserached about that and have found many things can be done using UI editor and a code in XML can help in customizing Window's ribbon! Please do giuide me if I am wrong.
Till now:


I have tried to write a small macro which is reading columns and also saving them in text file. I am attaching 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,
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>>