Mgmtblog886

1. How To Add Excel Chart In Word
2. How To Make A Spreadsheet In Excel
3. Add Words To Excel Formula
4. How To Insert Excel In Word As Icon

Create your Excel Spreadsheet. The most important step in the mail merge process is to set up.

-->

Visual Studio offers features in C# and Visual Basic that improve Microsoft Office programming. Helpful C# features include named and optional arguments and return values of type dynamic. In COM programming, you can omit the ref keyword and gain access to indexed properties. Features in Visual Basic include auto-implemented properties, statements in lambda expressions, and collection initializers.

Both languages enable embedding of type information, which allows deployment of assemblies that interact with COM components without deploying primary interop assemblies (PIAs) to the user's computer. For more information, see Walkthrough: Embedding Types from Managed Assemblies.

This walkthrough demonstrates these features in the context of Office programming, but many of these features are also useful in general programming. In the walkthrough, you use an Excel Add-in application to create an Excel workbook. Next, you create a Word document that contains a link to the workbook. Finally, you see how to enable and disable the PIA dependency.

Prerequisites

You must have Microsoft Office Excel and Microsoft Office Word installed on your computer to complete this walkthrough.

Note

1. And any updates you make to that original spreadsheet are then reflected in your Word document. If you embedded the Excel data, you can edit it directly in Word. Double-click anywhere in the spreadsheet and you’ll stay in the same Word window, but the Word Ribbon gets replaced by the Excel Ribbon and you can access all the Excel functionality.
2. Add A Place (SharePoint) This step might or might not be necessary, depending on whether or not you already added SharePoint as a favorite place (shortcut) from within MS Office. If you see Sites section when you do Save As from Word, Excel or PowerPoint (like shown in the image), then you can skip to Step 2. If not, follow the steps below.

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

To set up an Excel Add-in application

1. Start Visual Studio.

2. On the File menu, point to New, and then click Project.

3. In the Installed Templates pane, expand Visual Basic or Visual C#, expand Office, and then click the version year of the Office product.

4. In the Templates pane, click Excel <version> Add-in.

5. Look at the top of the Templates pane to make sure that .NET Framework 4, or a later version, appears in the Target Framework box.

6. Type a name for your project in the Name box, if you want to.

7. Click OK.

8. The new project appears in Solution Explorer.

To add references

1. In Solution Explorer, right-click your project's name and then click Add Reference. The Add Reference dialog box appears.

2. On the Assemblies tab, select Microsoft.Office.Interop.Excel, version <version>.0.0.0 (for a key to the Office product version numbers, see Microsoft Versions), in the Component Name list, and then hold down the CTRL key and select Microsoft.Office.Interop.Word, version <version>.0.0.0. If you do not see the assemblies, you may need to ensure they are installed and displayed (see How to: Install Office Primary Interop Assemblies).

3. Click OK.

To add necessary Imports statements or using directives

1. In Solution Explorer, right-click the ThisAddIn.vb or ThisAddIn.cs file and then click View Code.

2. Add the following Imports statements (Visual Basic) or using directives (C#) to the top of the code file if they are not already present.

To create a list of bank accounts

1. In Solution Explorer, right-click your project's name, click Add, and then click Class. Name the class Account.vb if you are using Visual Basic or Account.cs if you are using C#. Click Add.

2. Replace the definition of the Account class with the following code. The class definitions use auto-implemented properties. For more information, see Auto-Implemented Properties.

3. To create a bankAccounts list that contains two accounts, add the following code to the ThisAddIn_Startup method in ThisAddIn.vb or ThisAddIn.cs. The list declarations use collection initializers. For more information, see Collection Initializers.

To export data to Excel

1. In the same file, add the following method to the ThisAddIn class. The method sets up an Excel workbook and exports data to it.

   Two new C# features are used in this method. Both of these features already exist in Visual Basic.

   • Method Add has an optional parameter for specifying a particular template. Optional parameters, new in C# 4, enable you to omit the argument for that parameter if you want to use the parameter's default value. Because no argument is sent in the previous example, Add uses the default template and creates a new workbook. The equivalent statement in earlier versions of C# requires a placeholder argument: excelApp.Workbooks.Add(Type.Missing).

     For more information, see Named and Optional Arguments.

   • The Range and Offset properties of the Range object use the indexed properties feature. This feature enables you to consume these properties from COM types by using the following typical C# syntax. Indexed properties also enable you to use the Value property of the Range object, eliminating the need to use the Value2 property. The Value property is indexed, but the index is optional. Optional arguments and indexed properties work together in the following example.

     In earlier versions of the language, the following special syntax is required.

     You cannot create indexed properties of your own. The feature only supports consumption of existing indexed properties.

     For more information, see How to use indexed properties in COM interop programming.

2. Add the following code at the end of DisplayInExcel to adjust the column widths to fit the content.

   These additions demonstrate another feature in C#: treating Object values returned from COM hosts such as Office as if they have type dynamic. This happens automatically when Embed Interop Types is set to its default value, True, or, equivalently, when the assembly is referenced by the EmbedInteropTypes compiler option. Type dynamic allows late binding, already available in Visual Basic, and avoids the explicit casting required in C# 3.0 and earlier versions of the language.

   For example, excelApp.Columns[1] returns an Object, and AutoFit is an Excel Range method. Without dynamic, you must cast the object returned by excelApp.Columns[1] as an instance of Range before calling method AutoFit.

   For more information about embedding interop types, see procedures 'To find the PIA reference' and 'To restore the PIA dependency' later in this topic. For more information about dynamic, see dynamic or Using Type dynamic.

To invoke DisplayInExcel

1. Add the following code at the end of the ThisAddIn_StartUp method. The call to DisplayInExcel contains two arguments. The first argument is the name of the list of accounts to be processed. The second argument is a multiline lambda expression that defines how the data is to be processed. The ID and balance values for each account are displayed in adjacent cells, and the row is displayed in red if the balance is less than zero. For more information, see Lambda Expressions.

2. To run the program, press F5. An Excel worksheet appears that contains the data from the accounts.

To add a Word document

1. Add the following code at the end of the ThisAddIn_StartUp method to create a Word document that contains a link to the Excel workbook.

   This code demonstrates several of the new features in C#: the ability to omit the ref keyword in COM programming, named arguments, and optional arguments. These features already exist in Visual Basic. The PasteSpecial method has seven parameters, all of which are defined as optional reference parameters. Named and optional arguments enable you to designate the parameters you want to access by name and to send arguments to only those parameters. In this example, arguments are sent to indicate that a link to the workbook on the Clipboard should be created (parameter Link) and that the link is to be displayed in the Word document as an icon (parameter DisplayAsIcon). Visual C# also enables you to omit the ref keyword for these arguments.

To run the application

1. Press F5 to run the application. Excel starts and displays a table that contains the information from the two accounts in bankAccounts. Then a Word document appears that contains a link to the Excel table.

To clean up the completed project

1. In Visual Studio, click Clean Solution on the Build menu. Otherwise, the add-in will run every time that you open Excel on your computer.

To find the PIA reference

1. Run the application again, but do not click Clean Solution.

2. Select the Start. Locate Microsoft Visual Studio <version> and open a developer command prompt.

3. Type ildasm in the Developer Command Prompt for Visual Studio window, and then press ENTER. The IL DASM window appears.

4. On the File menu in the IL DASM window, select File > Open. Double-click Visual Studio <version>, and then double-click Projects. Open the folder for your project, and look in the bin/Debug folder for your project name.dll. Double-click your project name.dll. A new window displays your project's attributes, in addition to references to other modules and assemblies. Note that namespaces Microsoft.Office.Interop.Excel and Microsoft.Office.Interop.Word are included in the assembly. By default in Visual Studio, the compiler imports the types you need from a referenced PIA into your assembly.

   For more information, see How to: View Assembly Contents.

5. Double-click the MANIFEST icon. A window appears that contains a list of assemblies that contain items referenced by the project. Microsoft.Office.Interop.Excel and Microsoft.Office.Interop.Word are not included in the list. Because the types your project needs have been imported into your assembly, references to a PIA are not required. This makes deployment easier. The PIAs do not have to be present on the user's computer, and because an application does not require deployment of a specific version of a PIA, applications can be designed to work with multiple versions of Office, provided that the necessary APIs exist in all versions.

   Because deployment of PIAs is no longer necessary, you can create an application in advanced scenarios that works with multiple versions of Office, including earlier versions. However, this works only if your code does not use any APIs that are not available in the version of Office you are working with. It is not always clear whether a particular API was available in an earlier version, and for that reason working with earlier versions of Office is not recommended.

   Note

   Office did not publish PIAs before Office 2003. Therefore, the only way to generate an interop assembly for Office 2002 or earlier versions is by importing the COM reference.

6. Close the manifest window and the assembly window.

To restore the PIA dependency

1. In Solution Explorer, click the Show All Files button. Expand the References folder and select Microsoft.Office.Interop.Excel. Press F4 to display the Properties window.

2. In the Properties window, change the Embed Interop Types property from True to False.

3. Repeat steps 1 and 2 in this procedure for Microsoft.Office.Interop.Word.

4. In C#, comment out the two calls to Autofit at the end of the DisplayInExcel method.

5. Press F5 to verify that the project still runs correctly.

6. Repeat steps 1-3 from the previous procedure to open the assembly window. Notice that Microsoft.Office.Interop.Word and Microsoft.Office.Interop.Excel are no longer in the list of embedded assemblies.

7. Double-click the MANIFEST icon and scroll through the list of referenced assemblies. Both Microsoft.Office.Interop.Word and Microsoft.Office.Interop.Excel are in the list. Because the application references the Excel and Word PIAs, and the Embed Interop Types property is set to False, both assemblies must exist on the end user's computer.

8. In Visual Studio, click Clean Solution on the Build menu to clean up the completed project.

See also

Some time ago, I published a post on various ways to save files to SharePoint. None of the options though replicated the behavior we are used to. I am of course talking about an ability to save files directly from MS Office applications (Word, Excel, PowerPoint). You might also know or refer to this option as Save As. For a very long time, inability to do so has been a big matzo ball for many users. However, a recent update by Microsoft made this option possible. So I am super excited to share it with you!

Why Save As to SharePoint was a problem

The reason why Save As never really worked well was that SharePoint is not file share. When you click on Save As from Word or Excel, you get to choose a folder/location you want to save your file in. But with SharePoint, we do not have a C: drive, My Computer or folders to browse through. In SharePoint, we have sites. Many sites, and even multiple site collections (think Office 365 Groups), that are not organized in any folder-like hierarchies.

Below image depicts what the user experience looked like if you tried to Save As from MS Office apps (Word, Excel, PowerPoint) to SharePoint. Was very frustrating experience, was not intuitive and required that user had a Ph.D. in document management. 🙂

What has changed

Microsoft has recently implemented a change/fix that relies on Office Graph (same concept utilized by Delve and Intelligent Search). It allows you to pick from the sites you follow or visit frequently, thus solving the above-mentioned issue.

Now, I should mention, if you did not realize this already, that this solution only works for those who are in SharePoint Online (Office 365). If you are in SharePoint on-premises, you will need to rely on the other six options.

How to Save As from Word, Excel, and PowerPoint to SharePoint

How To Add Excel Chart In Word

Here are the steps, the user experience is pretty smooth now, just like a glass of good tequila on a Friday afternoon (which is when I am writing this post).

Step 1. Add A Place (SharePoint)

This step might or might not be necessary, depending on whether or not you already added SharePoint as a favorite place (shortcut) from within MS Office. If you see Sites section when you do Save As from Word, Excel or PowerPoint (like shown in the image), then you can skip to Step 2. If not, follow the steps below.

1. From either Word, Excel or PowerPoint (you only need to do this on one of the apps, not all), click on File > Save As > Add a Place
2. On the next screen, choose Office 365 SharePoint
3. On the pop-up, enter your Office 365 User ID (work email), click Next
4. Enter Password, then click Sign In
5. Bingo! Your OneDrive shortcut + SharePoint shortcut will be added to the menu

How To Make A Spreadsheet In Excel

NOTE 1: As stated above, you only need to do the above steps once in one of the apps (i.e., MS Word). Once completed, same favorites will show up in Excel and PowerPoint!

NOTE 2: It will add both, SharePoint + OneDrive for Business. This means you will be able to save files to either SharePoint sites or personal OneDrive space.

Step 2. Save As from MS Office

Now the most exciting part.

1. Go to Word, Excel or PowerPoint, and create a new document or open an existing one
2. Click Save As, choose Sites
3. Form the long list of sites you visit frequently or follow, choose the one you want to save to
4. From the next screen, choose the document library you want to save to. In case you have created many libraries on a site, they will all appear here. Pick one.
5. Next, pick the folder within a library – drill down as necessary
6. Provide or change a file name if necessary, then click Save
7. This is it; we did it – we saved the file to SharePoint! As promised, it was nice and smooth, just like a glass of tequila.

Add Words To Excel Formula

The above would not have been possible without an Office Graph. By the way, in case you do not see the site you want to save to from the list above, make sure you are Following it. It is quite possible, your site will not show up in Frequent section, but if you follow one, it will show up under the Following section.

How To Insert Excel In Word As Icon

Don’t you just love SharePoint and all the positive (end-user and out of the box) changes happening lately? Thank you, Microsoft! OK, I can now go and finish that glass of tequila. Lehaim!