This is a guest post written by Paramdeep from Pristine. Chandoo.org runs Financial Modeling School program in partnership with Pristine Careers. Visit Financial Modeling School to join our training program.
Greetings!
It’s been long time since we interacted on Chandoo.org. Actually I was very busy teaching the 105 awesome students for financial modeling in Excel. We all worked together to create some easy and some complex financial models. I found the journey to be quite exciting and enriching (From the feedback that I got, my students too didn’t find it bad either 😉 )
During the interaction, I found that a lot of students were looking for financial modeling around the project financing as well. So we thought why not introduce financial modeling for project finance.
In this post I will speak about some of the key aspects of a project finance model and why it can be different from modeling a normal company.
What is so Special About Project Finance?
An organization is an on-going entity – the basic assumption being that it would continue business for time immemorial. Project finance is different. It exists for a limited duration (though that duration is usually long – 20 to 30 Years!) and the project is structured in a special purpose vehicle (SPV). The SPV exists for a specific period of time and after the purpose of the project is solved, the SPV is dissolved. That means that the horizon of analysis is NOT time immemorial but a known time frame for which the SPV is formed.
The other typical difference is that these are long gestation projects, with almost no cash flow in the beginning and a lot of parties involved. This basic difference in the structure creates a lot of issues, Principal – Agency problem because of large number of parties, everybody trying to maximize their returns in a short time, propensity to wait decreases significantly and the initial project gestation period is critical to success of project.
Summarizing, Project Finance is the financing of
- Often long-term, industrial projects
- Increasingly those which provide public services or infrastructure
- Based upon complex financial and contractual structures commonly involving many legal entities
Two main types of Project Financing
- Greenfield – a fresh start
- Brownfield – expansion of an existing project
Key Aspects of Project Finance
I will highlight some of the other key aspects of Project finance.
- Separate Entity (Parent) and SPV Status – Risk of the transaction is generally measured by the creditworthiness of the project itself rather than that of its owners (Sponsors).
- Project Finance debt is often termed as “non-recourse”– That means the financial institutions cannot go to the parent level to get their money back. The money has to be generated at the SPV level. Typically these loans are secured by the project assets and the core project contracts.
- Timing of Cash Flow: The cash flows from the project comes only after the project is fully complete (takes more than a single financial year for completion) and are usually the sole means of repayment of the borrowed funds
- Multiple parties involved
- Sponsors
- Contractors
- Suppliers
- Governments
- Global financiers
- Long Gestation: From inception of an idea to Financial Close, a Project Finance deal can take years to negotiate
- Identifying Risks: The success of the project depends a lot on identifying risks, allocating them appropriately and ensuring that the responsible parties are adequately incentivized to manage their risks efficiently
- Construction time, costs & specification
- Operational cost, reliability
- Supply reliability, quality, cost
- Off-take volume, price
- Political environment, war, local hostility, currency in-convertibility
- Socio-environmental responsibilities
Modeling a Key Risk – Delay in Project Implementation
Because of the structure of the project one of the key risks in the project is the risk of delay. Delay in project can significantly reduce the IRR (what is it?) of the project and completely take it off track! There are multiple ways in which projects can be delayed and each would have its own repercussions on the return. These can be (The list is not exhaustive):
- There is an implementation risk (Delay because you could not execute the project in time)
- Delay in start of project (Could be because of regulatory approvals – Regulatory Risk)
- Delay in project because no funds were available (or delay in arranging the funds)
- Delay in collection of revenues (You built at the right time, but could not sell – Selling risk)
Typically each of these delays would have different affect on the cash flows. For example, if you are implementing a real estate residential project and you are not able to sell the homes – you have incurred all your cash outflows and your inflows are delayed. This would spell doom for the returns. On the other hand, if there is a delay in staring the implementation because of non acquisition of land, typically your cash outflow is not there so the affect would not be that large.
Why are delays more relevant for certain kind of investments?
Typically for long gestation projects – Roads, Real Estate, Power, Telecom, etc. there is a huge upfront investment. Even if there is a small delay in getting back the cash flows, it makes your project financials look very bad.
The Case of ABC Housing Co.
ABC housing company is planning to start the project on 1 Jan 2002 and is expecting to complete the construction over a period of 3 years. The construction cost is expected to be USD 1000 Mn. It is expecting to hold the property for a period of 5 years, in which it would get a lease rental of USD 100 Mn each year. After 5 years, it would sell the property at a value of USD 2000 Mn.
Modeling delays in Excel – Playing around with the date function
If you are planning to incorporate delays in your model, date function comes in very handy. For example, if you are expecting the construction period of 1 year, you can just put this as a parameter in your date (for years) and change his to see, if the construction period increases, what would be the effect on the project IRR.
The simple concept I have used here is to make the counter as a counter from the start date. The EOMONTH function calculates the end of month date of the particular month for which the date is given.
Now, I want to see if this is a construction year of not, I can just compare the number of years!
Some assumptions to start with
I am assuming that once the project starts, the cost incurred during the construction period would be uniform. So if the construction is completed over a period of 3 years, each year 33% of the cost is incurred and if it is completed over 5 years, then 20% of the cost is incurred each year.
To model this in excel, I compare the current year with the time period when the construction started and allocate the cost.
Similarly for Leasing I assume that it gets leased out one year after the construction period and after the leasing is complete, ABC is able to sell the property at the desired price
Calculating the Cash
There are two ways in which you generate cash – by lease and sale of property. The cash is consumed by your Capital expenditure. Assuming there are no taxes, cash generated is just a combination of all these.
Calculating the Return
As discussed last time, Internal Rate of Return (IRR) is an important aspect that all investors look at. Once we have the cash, we can simply ask excel to calculate the IRR for us. But in this case, since we have the exact year of cash with us, we will not use simple IRR function, but the XIRR function in Excel. This would take into account that all cash flows might not be at the year end.
Scenario Analysis
Now that we have the model with us, we can change the construction period and see its effect on the IRR. If we want to do a complete scenario analysis, we can used Data Tables in Excel to generate a complete scenario analaysis.
Related Tip: Learn how to work with Data Tables & Monte-carlo Simulations in Excel
To create the data table, the structure of the table should look like:
Where 1, 2, 3 .. etc are the years of construction and the cell is linked to the IRR that we had calculated earlier.
Then select the data tables option within the data tab > What if Analaysis
Select the changing cell as the years of construction (In this case the change is in a column)
You can see that there is a significant change in the IRR of the project with a change in construction period.
So if you construction period is delayed from 3 years to 5 years, your IRR would change from 15.6% to 13.1% (and hence the investor interest would simply die out!!).
Templates to download
I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers!
You can download the same from here [mirror].
You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this filled template [mirror] and check, if the information you recorded, matches mine or not! 🙂
I am just doing that for the single sheet model and recommend that you do the same for multi-sheet model as a homework problem. If you face any issue, post your excel with the exact problem and we can discuss the way to move forward.
Next Steps
This series gives you a flavor of how financial modeling is done and an idea about specific nuances in modeling for long gestation projects. In the next part of this, we will learn how the interest payments of a project should be modeled.
Join our Financial Modeling & Project Finance Classes
We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.
Please click here to learn more about the program & sign-up.
For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com
87 Responses to “Printing Excel Reports via a Word Document”
[...] This post was mentioned on Twitter by Chandoo.org and Stray__Cat, Excel Insider. Excel Insider said: Printing Excel Reports via a Word Document: Printing Excel Reports via a Word Document Using Microsoft Excel & W... http://bit.ly/hOtMkA [...]
Hi Chandoo,
Excellent post. I have word in my PC but I don't have words to praise you. A big thanks to you to push us one step deep in the ocean of VBA automation.
Thanks is not enough...
Som D Thapliyal
Chandoo, Thank you for this great idea, however I think the word's built in mail merge function offers enough possibilites for almost all mail merging task without having an Excel macro in the background.
KIndest Regards
Andras Ujszaszy
Hello Chandoo,
This post is excellent and a real time saver. We have the detailed data on SQL. The aggregated data is transferred on to MS-Excel sheet. The data on Excel is further manipulated based on volume/price criteria to arrive at individual invoice values. About 60% of the invoices generated have customized formats. I have been grappling with this issue of invoice generation all along - how to standardize the process and make it automated and less time consuming.
You post came as a real solution and problem-killer. It is awesome!
Thank you once again for making our lives enjoyable with Excel!!
Hi Chandoo,
I was thinking of thing in these lines and there you are! Although I wanted much more simpler. I typically have work with various client related word templates in which I have to make some reports. If at all you tweak a bit, the whole template in MS word goes for a toss and its super mess to clean this mess. So can it be like this - I will input some data in Excel and I want the data to get exported to MS word under their respective Heading. For Example: If I write "Chandoo is a good boy" in excel and when I export this to MS word, I want it to be reflected as "Chandoo" as the name of the Heading 1 and "is a good boy" under this Heading 1. OR it can be "Chandoo" under Heading 1 (whatever be the name of that heading) and "is a good boy" under Heading 2 (whatever be the name of that heading). Can you please help me in this? Or pass me a hint?
@Pulkit
Use a data transfer sheet to re-arrange your data to 2 seperate fields before exporting
So if
A1: Full Name
A2: Chandoo is a good boy
Make 2 new fields
B1: Name
B2: =LEFT(A2,FIND(" ",A2))
C1: Salutation
C2: =RIGHT(A2,LEN(A2)-LEN(B2)+1)
Use Name and Salutation as appropriate as Fields in your word template
@All.. this post is written by Hui. So please direct your love to him 🙂
Our organization has 150+ employees. Every year our finance department has to issue Tax certificates (F-16) before July and the finance heads invariably burnt midnight oil to issue them manually-filled/typed out docs. In 2007 I approached them with a simple solution of mail merge and they wanted to test it out. And it was all finished in 1 day, what used to take them months. In that regard I agree with Andraas, even without the macro Word gives full control over printing the excel database - right from printing a single record to all or multiple records in a range. However, the above macro-based approach I believe is more elegant tool, especially if we want to print a single record. Thanks!
Hi Chandoo
First of all thank you very much to bring this technique to so many people. I have been using this for the last 8-10 years for contract writing etc. I have two suggestions to make.
One is to use a 3-row merge sheet. the 1st row is the header-row. The 3rd one is the data row and the 2nd row is a text converted row for numbers and dates. for example we can convert 2/3/2011 into text such as "3 February, 2011". This would look nicer in a letter. In contracts, we have to use numbers in word and figures. the numbers can be converted as required. This have saved my staff a lot of time and is error free (human error).
The 2nd point is to provide to the user on the control sheet cells to insert names and folder path. For each word document, a unique name can be generated with a date stamp in the file name.
Prem
Thanks so much for this, I've been looking for something like this for ages. I'll be playing with this now to make it fit my evil purposes!
thank you Hui! real insight. i cannot thank you enough for this post.
@Hui....just out of curiosity, how would you transfer a chart (as a bitmap) from Excel to Word within the context of the same VBA program and to a specific field in Word?
@Post : Amazing post chandoo / Hui and team...
This is one of my thought
Keep it up
Thanks so much! I learn so much from this blog. I teach classes and keep track of student registration with a spreadsheet. I have wanted to print receipts for some time and this post has help tremendously. Of course I do have a question. I separate first and last names into their own columns so I can sort by either, so when on the control sheet I am looking up a student name I can only lookup by first name or last name and I anticipate this causing problems because I will have multiple students with the same first name and even the same last name. Is there a way to concatenate those fields during the data validation? Thanks.
@Kathryn, Glad you liked the post.
you can use a number of techniques to lookup based on multiple criteria
Have a read of http://chandoo.org/wp/2010/11/02/multi-condition-lookup/ for some ideas
@Bill, Istiyak, Paul, Prem, Kamesh, Pulkit & Manish
Thankyou for your positive feedback and ideas
@Bill, No idea how to transfer a picture, I will investigate one day.
[...] RSS feed for comments on this post. TrackBack URI [...]
hi chandoo,
first of all, thank you for this example,really helpful. but i got an error when run it.
error appear on ".Destination = _________"
@Syam
This won't work on a Mac version of Excel/Word
What version of Excel/Word are you using ?
.
Did you change the 2 lines in the Macro as listed above?
Const WTempName = “Shipping Template.docx“ ‘This is the Word Templates name, Change as req’d
Const NewFileName = “New Certificate.docx” ‘This is the New Word Documents File Name, Change
No comment, just want to subscribe to new comments.
Dear All, I want to print after one record as like 1,3,5,7,------------.
how to it possible, please give easy way.
@Sihab
The technique presented here is for printing/transferring 1 record, of a number of fields, at a time to Word.
When you say like 1,3,5,7,---
Can you please elaborate ?
This is amazing!!! I have task which requires me to take screenshots from a web page and save it in PDF's. This task takes a good 15-20 days for me. The same data is available for me in excel now, this post has given me a wonderful idea of doing the mail merge from excel to word however saving them as a PDF is a challange. Hopefully I should be able to figure it out after I finish my VBA Classes.
Thanks Hui
@Suma
You can use this technique to do exactly that.
Just set your PDF printer to be your default printer
Hi Hui, Thanks for the response. My case is a little complicated, I have close to 4000 records which needs to be put in a form and saved as PDF. I tried word but the form keeps changing to fit data and the output is not uniform as a work around I have created the form template in excel, I have looped it to take data 1 row at a time, my challange is in saving each of this form as a PDF file through VBA. I am not able to find any options to do the same with the Xlfileformat enumeration. Is there a way? appreciate if you could guide me.
@Suma
Do you want to email me a sample of the data, say 20 records and a Template your trying to use
Email at bottom of: http://chandoo.org/wp/about-hui/
This snippet of code might help you print ot pdf from within Excel. Just reorganize for your needs.
Let vPrtOutputDir = [prtnghbr_outputdir]
Let vPrtOutputFile = [prt_nbhdlst_code]
'print analysis to printer (needs 8.5" x 14" legal size paper)
If Range("prtnghbr_prthard") = True Then
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Else
End If
'print to pdf file
If Range("prtnghbr_prtpdf") = True Then
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=vPrtOutputDir & vPrtOutputFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Else
End If
This is fantastic, but I can't get it to work with my own file (I am fairly new to VBA), I suspect i am not understanding, the Run the Report section. When I run it I get an error saying 'Can't find project or library'. Can you help?
Hi
I need to copy an word file into excel, i am able to copy into excel
by using
1. word as application ( ref -microsoft 11.0 word)
2.Copying the active window details from word into excel
i want to know can we copy and paste as html format
Chandoo,
Thanks for posting this very helpful tutorial and example files.
Regarding future enhancement: "+ Sourcing the New Word File name from the Control sheet"
The easiest fix I found for this was to:
1. Dim NewFileName As String
2. comment out 'Const NewFileName = "New Certificate.docx"
3. replace with: NewFileName = ActiveSheet.Range("D7").Text
Thanks again!
p.s. Code modification courtesy of a macro I grabbed from a discussion thread by Leith Ross on Excel Forum, 2008.
@Stephen - open up VBE, go to Reference, uncheck "Missing Microsoft Word 12.0 Object Library". And check "Microsoft Word 12.0 Object Library". Your library references may be higher or lower than 12.0.
I have tried this macro "1997/03 Sample" on a computer that works with Windows XP and Office 2000 and doesn't work. After 1 minute comes up a windows which says: "Microsoft is waiting that other aplication finish an OLE action". and can't cancel this.
Can someone help me?.
I am a novice with macros. Please explain with detaill.
Thanks. Joey.
@Joey
I've sent you an email
I have exactly the same problem almost 4yrs later (looking at the original message).
Do you have the solution?
Thanks a lot.
How can I use this for charts and data without data validation or drop menu?
@Wil
You don't need to use a drop menu, but it just simplifies the choice of values in this example
You can manually type values as well in the example
The Macro requires a trigger to start and this is achieved via the button, although it could be done by a change in the cells value, although if you type a wrong value the macro will still run.
A few people have asked about transferring charts and graphics and to be honest I haven't investigated this possibility.
[...] [...]
Hi Chandoo and Hui,
Reading all the information and applying it is a real fun!!... u made Excel very simple to understand and easy to use!..
Would like to have your advice on below :-
I generate a MIs report of client and its transaction from a web template to an excel. Now we need to generate Tax advices for these transactions. Now the tax will be generated only for those trn where we have currency conversion. we have a formulae placed which calculates the tax amount. i want to link this (or merge) this excel data to a word file, which will generate an advice with individual client transaction data and the tax amount. Also i wish to produce one summary report for a day.
Please help me on this.
Thanks in advance ,
Regards
shantanu.
Hi Chandoo and Hui
I tested the 2010 samples yesterday and they worked perfectly. Today, I tried it again (I even downloaded the original docs again).
It comes up with, "Error has occurred: External table is not in expected format."
Please assist?
I need step-by-step instructions. I'm not a developer.
[...] heb te snel gereageerd, Dit zou iets kunnen zijn: Printing Excel data via a Word Document | Chandoo.org - Learn Microsoft Excel Online [...]
Google Translate: I reacted too quickly, this would be something: Printing Excel data via a Word Document | Chandoo.org - Learn Microsoft Excel Online
Hi Hui, any idea how this could work with multiple merge records? ie. several lines of product on a single order.
Hi Chandoo,
This was very helpfull for me.
Can you sugesst me any method if there is multiple rows for particular shipment number then how mail merge those data to word document.
looking forward for your response.
Great tutorial!!!
Let's suppose we needed the country (UK in this case) to be printed on a gray background and CAN to be printed on a red background...if my excel data source has this formatting already in place, can we do the same in Word?
@Matt
You can pre-format the field in Word, but I'm not aware if you can transfer the formats across
Thanks for your efforts and support.
I have the following question.
If I have a graph in the excel worksheet that I want to include in the word file along with the other data how can I do that?
How can I give the graph as a "field" in order to transfer it also in the word template?
Thanks in advance and keep going quys.
Nikos
Hi Hui, thanks for the info - it is really informative.
However, although everything worked fine when I used your practise documents, when I tried to create my own, the macro will not work.
The error msg says: Compile Error: User defined type not defined.
When I hit OK, It goes to the code and highlights: objWord As Word.Application
The only thing i changed in the code was the filenames as directed. I have put my excel file and associated word templates and documents in a separate folder. I see the line of code that will read from the path of the workbook, so i guess this should not be a problem.
I am a VBA newbie. Can you help?
@Jason
What version of Excel are you using?\
Make sure the two files are in the same directory and make sure the path variable is set correctly in the VBA Code
You can email me the files if you want
Email is at the bottom of the page http://chandoo.org/wp/about-hui/
I had the same issue, I needed to reference the Word Object library in VBA...
Hit alt-F11 to open VBA window
Tool menu, References, add a check next to Microsoft Office xx.x Object Library
No compile error for the line objWord as Word.Application line after this was added.
chandoo ,, you are awesome man. Excellent Solutions for each every excel problem
Hi Hui,
I really like this excel to word macro. It is saving me so much time. To take it a step further, how could I modify it so that, as well or even instead of saving it to a folder; I could send the new word doc generated to myself or to a particular email address?
Thanks
Kevin
Hi Hui,
Thank you so much for this sheet, it really saved my time and days in life :-). Mean while really excited to see and learn about the "Future Extensions" that were stated above.
Can we know if these are implemented.
Thank you so much for every knowledge.
Regards,
Hema Sapasetty
@Hema
No I am sorry I haven't got around to those extensions
Hui...
Hi Chandoo and Hui,
this is looking grate, exactly what I needed, but I'm having a problem with the merge.
I'm copying the data I need to the transfer sheet, on the first try, I get the correct data in the word document, but when I run it the second time, I'm getting the same data again.
In the "transfer" sheet I see the new Data, in word, I see the old (previous) data.
Any Ideas?
Thanks,
Eitan
@Eitan
the model doesn't increment the data automatically
Are you updating the data/filter in the workbook
That is what is transferred.
If that doesn't help can you post your files
Hui...
Hi Hui,
Thank you for your quick respond
Uploading my files is impossible as it has both lots of Hebrew in it and the entire excel has many user form that are non related to the task...
I can try to upload just the 2 forms that are related along with the word document, again, it's in Hebrew...
Just to make sure I got your answer, I have a range of data that I display in a user form using a combo box, and the combo box displays only relevant data (filtered), once I select an object from the combo box, I display the data in labels, when I need to do the merge, I Identify the corresponding row from the relevant sheet, and copy that row to a new sheet (transfer).
next I run your code to create the report using my "template" word doc.
so, in my main worksheet I have all the data, in my transfer sheet i hanve the data I need to create a report from, so far it is working.
when the report is created, I get a data that is not the one in the transfer sheet,
closing and reopening both word and excel is not helping.
I'm trying log-of and log on and maybe later, restart to see if the data is saved in a cash somewhere.
Hi Hui,
I am using this macro for letters, however I really need your input on one aspect.
Can you help me with writing the vba code to save the merged output file a different folder.
now the output document from the auto merge is getting saved where the master file is located, where I need the output to save in a specific folder. Kindly help me how to define the destination Folder (Output folder name) within the vba code.
Thanks in advance.
Regards,
Hema Sapasetty
Hi,
having created the desired word document, how do we set it so that the new word doc will open automatically as soon as we've run the macro?
@Kevin
In the Excel file goto the line in VBA
Find the two lines that start with Const
Const WTempName = “Shipping Template.docx“ ‘This is the Word Templates name, Change as req’d
Const NewFileName = “New Certificate.docx” ‘This is the New Word Documents File Name, Change as req’d
Adjust as appropriate
@Hui,
okay thanks but I would like the New Certificate doc to open at the end because I need to print them every time.
I want them to open then I will print.
How do I adjust the macro for this to happen?
Showing compiler error
@Asif
What excel version are you using?
Did you enable Macros on opening ?
thankfully from you, chandoo dear
Hi Hui,
Thanks a lot for this post. But i noticed that this is just applicable if i only want to appear a single record in my report, but what if i want to appear numerous record in my record for a certain DATE for example?
@Chano
You can setup the transfer page to say list 10 or 20 records
Then give each a unique name ie Rec01, Rec02 .. Rec20
Then setup your fields in words similarily
@Hui,
Where can i find the "transfer page"? a sample worksheet depicting this scenario is much appreciated. Thank You. 🙂
@Chano
The transfer sheet in the example
So instead of listing one record it will list a number of records
YOU MAY WANT TO USE TECHNIQUES LIKE:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
Hui, Good Afternoon!
Firstly, I am a member of VBA Classes and I have been trying to achieve this myself before coming across this and downloading.
However, I find that trying to run you example xlsm and docx files, I get the same issue that I was coming across before! I get the message
...can't open or wrote to file, it is already opened exclusively by another user or you need permission...etc, so I'm now back to the beginning.
In summary what I'm trying to do, is call the Word Template from Excel to produce Labels, Print, Close Labels, refresh Excel data and repeat process.
I would appreciate your advice as to where I am going wrong please.
Thank you in anticipation
@Graham
Can you pls email me the two files
Click on Hui... above
Email address is at bottom of Page
What version of Office are you using ?
Hello All and Hui,
Thank you for all the knowledge, this more helpful.
But I have a requirement to print thousands of records at a time and saving the output in PDF or docx, did anyone thought of automating the mailmerge and saving the output as PDF or Docx.
Please require your help if you guys know anything about it.
Thank you in Advance.
Regards,
Hema
@Hema
You can put this technique inside a loop
Or simply do it as a mail Merge from Word
Hi Hui
Thank you very much for making this VBA solution available.
I am using this setup to produced Word-based hospital discharge summaries from an Excel database.
I have changed the Control Sheet so that it uses the patient's medical record number rather than the original SH data. That then puts data in the Transfer sheet which is sent to the mail merge.
My problem arises with multiple hospital admissions for the same patient. The instruction in the Transfer sheet pulls the FIRST instance that it finds the relevant medical record number. But I need it to pull the LAST entry.
I have set out the admission data in Column G of the Data tab - so I would to amend the Sheet instruction so that it finds the medical record number AND THEN puts the most recent set of data from the latest admission.
Here is the Sheet instruction set that I am using:
=INDEX(Data!B2:AB2500,MATCH('Control Sheet'!$D$7,Data!$B$2:$B$2500,0),MATCH(A$1,Data!B$1:AA$1,0))
Any help in amending this instruction so that it pulls the most recent admission would be greatly appreciated!
@Chris
Can you post a file on something like Dropbox or Live etc or email it to me to see what your doing
hi Hui, First of all, thank you for this post! it realy is what I was looking for.
I got some problem though (word file doesn't update anymore) when I "share" the workbook. When it is unshared, it works fine.
I have to share the workbook since there are several (3-4 people) that must use it at the same time.
Do you know any solution for this?
I am using MS Excel 2013, while I stick with the MS Word version on your sample file.
Regards,
Edwin
Hi,
Can you help me with changing the macro, so that instead of a new word document, it saves a new pdf document?
Thank you
Hi Folks
This article is great. But does anyone know how to update check boxes on a word document from an excel cell entry such as "Yes" or "No"?
The report learning is nice. But I have another question. If in the sheet1, A1=Current Date, B1=Apple, guava, banana (from drop down menu), C1=Price, D1=Seller. When I shall input data in sheet1 (Only in B1, C1, D1) and those will be replaced but stored in Sheet2 (for Apple), Sheet3 (for guava), Sheet4 (for banana) according to date. Is it possible?
Hi,
Take a look at https://document-merge.com/
It's exactly what you are looking for.
Cheers,
Hi,
I am new to VBA. while running the code, 1st i am getting a msg box as Header Record Delimiters , which is asking me to enter some Delimiters... if I select None, it is saying Invalid Merge Field.
Then I am getting that the error Microsoft Excel is waiting for another application to complete an OLE action.
Then I am getting the error with the below lines of code.
It says
'Merge the data
With objMMMD
.MailMerge.OpenDataSource Name:=cDir + ThisFileName, sqlstatement:="SELECT * FROM `Transfer$`" ' Set this as required
Not sure where it is going wrong. Please help
debug!!!! help me.
Set objMMMD = objWord.Documents.Open(cDir + WTempName)
@Enkhzaya
Check that cDir has a \ at the end because
(cDir + WTempName) should be like c:\mydirectory\filename.docx
with a \ between the directory and filename
Wow! After all I got a website from where I
be capable of in fact obtain useful data concerning my study and knowledge.
Your assistance is very motivating. I am having an issue. I have an excel file which contains multiple records for a key. How can I put multiple records for a single key in word document (i.e. printing some letter) and use this repetitively for other records.
Hurrah! At last I got a webpage from where I can actually
obtain helpful facts concerning my study and knowledge.
Is it possible to include a mailmerge field in the file name?
e.g Document For [Jeff]
Jeff being the name of the person the letter is being drafted for. So in my destination folder I can just generate as many as I want without fear of the file being over written.
@Gareth
Probably not easily
But seeing as the merge is done using VBA you could use that to save the file with the appropriate name
After I originally left a comment I appear to have clicked
the -Notify me when new comments are added- checkbox and now each time a comment is added I get 4
emails with the same comment. Perhaps there is a means you
are able to remove me from that service? Thanks!
Hi, Good evening
I have multiple records in data sheet with same key. I want to transfer all those multiple same rows from data sheet to transfer sheet and then mail merged them to a word document as a report.
Thank you