Custom Report from xls data to txt file.( Mail Merge )

[dingdang]

Hi,


I have xls data file from which i want to create report just like mail merge in word in txt file template.


can any one provide macro to generate report.


text file should auto save with file name "report & date" into in same folder where xls file exist.


attached sample file for your ref.


https://dl.dropbox.com/u/66400357/custome%20report.xlsx ( data file )

https://dl.dropbox.com/u/66400357/Report.txt ( required report in txt file )

 

[SirJB7]

Hi, dingdang!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Custom%20Report%20from%20xls%20data%20to%20txt%20file.%28%20Mail%20Merge%20%29%20-%20custome%20report%20%28for%20dindang%20at%20chandoo.org%29.xlsx


Sheet Sheet2 has a group of 16 rows by 7 columns report format, which in the example is copied up to line 496 (31 records), where visibility is handled by conditional formatting.


Just copy further groups of 16x7 for adjusting to your actual sheet Sheet1 entries.


Regards!

 

[dingdang]

Sir,


Greee8...Thanks a lot.


can u plsss explain how u done this just for my knowldege & learning. have check conditional formating but don't understand how letter format copy with data from sheet1.

 

[SirJB7]

Hi, dingdang!


Glad you solved it. I proceeded as this:

a) copied the layout or your text file in a worksheet, with underlining, formatting cells, everything: it occupied 16 rows by 7 columns

b) defined a dynamic named range (DataTable)

c) in the yellow shaded cells (which you may set as with no background) I used the same formula:

=SI.ERROR(INDICE(DataTable;ENTERO((FILA()+15)/16);1);"") -----> in english: =IFERROR(INDEX(DataTable,INT((ROW()+15)/16),1),"")

changing only the 1 for the related value of each data column

d) copied the 16x7 block many times down, just for testing purposes

e) created a conditional formatting formula for hiding displayed values and report structure (font color white, no background, no borders) when the order (index no.) of the 16x7 block was greater than the entries in first sheet.


An enhanced version should have VBA code (macro) that for each entry in sheet one creates a 16x7 block in sheet two. But I leave this to you For now, the suggestion seems to be suitable for your issue, isn't it?


Hope it helps.


Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.


Regards!

 

[dingdang]

Sir,


Thanks for the details, If possible can u pls provide VBA code for each entry in sheet one creates a 16x7 block in sheet2 the reason is that i have approx 850 to 1300 record in sheet1 and for that i have to copy 16x7 set which i think very time consuming process.


Pls help.

 

[Debraj]

Hi DingDang,


Please download the attache file.

In the file, Sheet1, Increase the data as much as needed. If you wish to change the output format, you can also change in the K1 to Q16 Area, except the area which need to fill


https://dl.dropbox.com/u/78831150/Excel/Custom%20Report%20from%20xls%20data%20to%20txt%20file.%28%20Mail%20Merge%20%29%20-%20custome%20report%20%28for%20dindang%20at%20chandoo.org%29.xlsm


click on the button and it will gives you result on next sheet.

Please let us know if it works for you..


Regards,

Deb

 

[dingdang]

Siiir,


Damn Good.Perfect as required output ...Thanks a lot

 

[SirJB7]

Hi, dingdang!

Arriving at dessert time and seeing you've been fed by Debraj Roy, so...

Glad you solved it. Thanks for your feedback and for your kind words. Credit to Debraj Roy too and welcome back whenever needed or wanted.

Regards!

PS: Today it's Sunday, still, it has half an hour more (GMT-3), so this lazy guy thanks for not having to write any code

 

[SirJB7]

@Debraj Roy

Hi!

I'd dare to suggest you to avoid the 64K method for finding last rows in worksheets as it has proven to don't be bullet proof. In this case there were a maximum of 1300 but... and there's always a but, as b(ut)ob(ut)hc says...

So work a little more, man! You're not going to tell me about your hand and pen and beer, are you?

Regards!

 

[SirJB7]

@Debraj Roy

Hi!

I'd dare to suggest you to avoid the 64K method for finding last rows in worksheets as it has proven to don't be bullet proof. In this case there were a maximum of 1300 but... and there's always a but, as b(ut)ob(ut)hc says...

So work a little more, man! You're not going to tell me about your hand and pen and beer, are you?

Regards!

 

[Debraj]

@ SirJb7,

Thank you for the suggestion, I will take care..

Regards,

Deb

PS:

I already replied for hand, pen and beer..

http://chandoo.org/forums/topic/finding-a-duplicate-in-multiple-columns-with-varying-responses#post-37827