I have a list of addresses within an excel spreadsheet, and I need to copy them into word in
Name
address
address
postcode
telephone
description of business
format, one after the other, in alphabetical order.
Is there an easy way to do this?
Thank you What you need to do is in Word create a .doc file and edit it as you would like the completed document to look like, ie insert table or columns.
Then in your excel document, edit it so that the top line has the vital merge headings, ie Column 1 is Name1 (ie Mr M), Column 2 is Name2 (ie surname), Column 3 is Address1, Column 4 is Address2, Column 5 is Postcode, etc, etc
What you have to make sure is that each line of the address (and the other information) uses a different column and in turn each column has to have a heading.
If you want to sort your listing in alphabetical order (using the Surname in this instance), on Toolbar select DATA and down to SORT, in the dialog box Select from drop down list Column B and ASCENDING, also click on My List HAS A HEADER ROW, then click on OK.
When you have finished editing your excel document, save it.
Go back to your Word document and you can either run the Mail Merge Wizard (under Tools) or click on Tools on the Top Bar and select SHOW MAIL MERGE TOOLBAR.
If using the latter, in your Word document select "OPEN DATA SOURCE" (this looks like a little excel document with a blue line on the top), browse and select the excel document you have been editing.
Now place your cursor where the Name would go (for instance first column of table) and click on "INSERT MERGE FIELD" from the Mail Merge Toolbar, a list will drop down and select, in the first instance, Name
Then go to where you want the Address to be and select Address1 (or whatever) from the drop down list (as above). If using tables or columns when you move on to the next cell, before you put in Name, Address1, 2, postcode, etc you will have to select "Next Record" from the Mail Merge Toolbar and you will find this on the drop down list under INSERT WORD FIELD
Once you have done one of these (with 鈥渘ext record鈥?before it) you can select it (SHIFT 鈫?, copy it (CTL C) and paste (CTL V) onto the next cell and keep doing this until you get to the end of the page.
If you want to see what the completed document will look like before you merge it, select <<ABC>> on the Mail Merge Toolbar.
When you have finished editing your word document, select MERGE TO NEW DOCUMENT, Click Okay and wait a few seconds and you should have your finished address file.
I hope this works out for you. There is a simple solution to accomplish this. For better understanding i just breakdown the problem into two phases. First one is to read data from your excel file and the second one is to prepare a .doc file for the above information you mentioned.
For reading information from excel file please follow the code bellow. Hope this will help to get information from excell file.
public System.Collections.IList CategoryUpload(String strPath, String strUpfile)
{
// initialize the Excel Application class
Excel.ApplicationClass app = new ApplicationClass();
// create the workbook object by opening the excel file.
Excel.Workbook workBook = app.Workbooks.Open(strPath,
0,
true,
5,
"",
"",
true,
Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
// get the active worksheet using sheet name or active sheet
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
int index = 0;
IList objList = new ArrayList();
// This row,column index should be changed as per your need.
// i.e. which cell in the excel you are interesting to read.
object rowIndex = 2;
object colIndex1 = 1;
object colIndex2 = 2;
try
{
while (((Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
{
string catg="";
string categoryCode = ((Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
string Name = ((Excel.Range)workSheet.Cells[rowIndex, colIndex2]).Value2.ToString();
if(categoryCode == "VERP")
{
catg = "PM";
}
else if(categoryCode == "ROH")
{
catg = "RM";
}
else if(categoryCode == "ZQCR")
{
catg = "QC";
}
category.Code = catg.Trim();
category.Name = Name.Trim();
//movementType.Description = Desc;
category.Rectime =System.DateTime.Now;
objList.Add(category);
}
}
catch (Exception ex)
{
Process[] prs = Process.GetProcesses();
foreach(Process pr in prs)
{
if(pr.ProcessName == "EXCEL")
{
pr.Kill();
}
}
log.Error("Error:", ex);
return null;
}
msg = objList.Count + " of " + index + " Records sucessfully uploaded";
return objList;
}
For the later part please have a look on the net. Yet i did not get anything related this!!
Do all your sorting an formatting inside excel and copy paste to word. Or else you can add the excel sheet itself as an object copy and paste them im guessing, im not sure if you can actually merge it into word, its been forever since ive used excel, but i didnt get what your saying just copy/paste and word will take over and treat list like a word table!
Just tried it works! |
