Working with text for a report

[rodtt]

Good afternoon!

I'm making a payment calculator with excel and I need it to make a report. The thing is, I need excel to insert some information inside a relatively big text, and I don't know how I should work. Should I leave cells empty for those informations or should I use a macro to find specific places inside the text to insert those data.

I tried the first option, but the report doesn't look nice, it ends with some empty spaces that I want to avoid.

I'm basicaly looking for an answer on which of the two is the best way to proceed and some kind of sample code for the second option, if it's the case.

Thanks!

[snb]

I'd prefer Word for reporting.
You can use mergefields or docvariables to insert Excel information.

[rodtt]

I'd prefer Word for reporting.
You can use mergefields or docvariables to insert Excel information.

I see.
I'll research a bit on the topic and then I'll probably come back here for some advice, but I want to ask another question, just to make sure:
Can I also directly send a especific spreadsheet to this report through mergefields or docvariables? (I bet you can, just making sure)

[Paul_Hossler]

If you do want to stay in Excel, you could use placeholders in the 'boilerplate' and replace them with real data (UserForm, InputBox, Worksheet cells)

Sort of like Word's Mail Merge




Dear ##NAME## Please send me ##MONEY## for the ##SERVICE## provided on ##WHEN## to ##NAME##

Thanks very much ##NAME## Signed, ##WHOAMI##


##SERVICE## Provider


Option Explicit
Sub UpdateData()
Call MyReplace(ActiveSheet.Columns(1), "name", "John A. Smith")
Call MyReplace(ActiveSheet.Columns(1), "money", "$1,000.00")
Call MyReplace(ActiveSheet.Columns(1), "service", "Excel Consulting")
Call MyReplace(ActiveSheet.Columns(1), "when", "7/4/2013")
Call MyReplace(ActiveSheet.Columns(1), "whoami", "VBA Forum")
End Sub

Private Sub MyReplace(ReplaceInThis As Range, PlaceHolder As String, RealText As String)
Call ReplaceInThis.Replace("##" & PlaceHolder & "##", RealText, xlPart)

End Sub


Paul

[snb]

Can I also directly send a especific spreadsheet to this report
Yes, using some other fields (e.g. 'IncludeText')

[rodtt]

I managed to solve the issue using merge fields and it was quite straightforward. I added a button on excel to open the linked word document, but I'm having some problems with it losing link on opening. I have to make the procedure "Mailings>select recipients" on word everytime.

Another problem I'm having is on saving this spredsheet archive on my work computer. I'm doing most of this at home and when on work I'm having some error messages and the excel file doesn't save. Don't know if it's some sort of incompatibility with excel versions, but it started after I messed with merge fields on word.

After solving those issues, i'll have just two more things to do to complete my work: Inserting a excel sheet in the middle of word report and automating printing when opening word report.

Thanks to everybody for all sugestions until now!