Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
The Excel Team Blog » All Tags » Programmability (RSS)

Browse by Tags

  • Using Excel 2007 for Progress Tracking in the Classroom

    Today's author, Danny Khen, a Program Manager on the Excel team, discusses a solution he recently built using Excel. The Manual Spreadsheet Spreadsheets are the bread and butter of running the business world, where PCs are ubiquitous. The possibility, indeed even the need, to use spreadsheets in an environment where access to a computer is not taken for granted seems absurd to us, as business users and technology professionals. We do still print reports for some output scenarios, such as executive...

  • Reading Excel Files from Linux

    Today's author, Chris Rae, a Program Manager on the Excel team, talks about using Perl code to read Excel's new file format. The OOXML Format As most readers will know, we spent a lot of time during the development of Office 2007 in creating and documenting a new XML-based file format (Office Open XML) to replace the much more complex binary formats the various Office applications have used for the last twenty years. A lot of time. Many of us dream in XML now. OOXML encompasses an XML file...

  • Ten Tips for Using Excel Services

    A new MSDN article about Excel Services was just released. If you do any kind of work with Excel Services, whether you are just getting started with it or trying to do something a bit more advanced, this article is a pretty good read because it covers some of the more common pitfalls that we hear from customers. The following topics are discussed: Making an Excel Workbook Compatible with Excel Services Configuring External Data Connections Configuring Excel Services to Work with Kerberos Saving an...

  • New Technical Article on Excel Services

    There's a new article up on MSDN that describes a custom solution built by the IT department here at Microsoft that uses Excel Services. Rather than re-paraphrase, I'll "borrow" liberally from Shahar's description : The solution is probably one of the most advanced implementation of Excel Services I have seen to date and includes lots of ingenious customizations - the white paper, specifically, talks about how to use Excel Services as a landing pad for data and then get the...

  • Improving Sheet Selection

    Today's author: Sam Radakovitz, a Program Manager on the Excel team that enjoys creating VBA applications in Excel. A bit ago I got a question through email asking if we could change the 'grid selection color' in Excel. Or at least I called it 'grid selection color', it's basically what the cells look like when you select a block or multiple blocks of them: As you can see, the color is a bit light and hard for some folks to see, especially on a laptop if you're looking...
    Filed under:

  • Building the Excel Media Player – Part 3

    Last post we talked about how to use Excel as a flexible development environment and touched on how to use ActiveX Controls to enhance your solution and using tables programmatically to manipulate data. In this post we are going to drill into how to add custom Ribbon UI, context menus. Custom Context Menu In order to make playlist creation a lot easier in the Excel Media Player, I added the following custom right click context options to my Tables. To see how to do this open the MediaPlayer.xlsm, press Alt + F11 and double click ThisWorkbook. You will see the following code in the Workbook_Open() event: AddMenuItem "Add Selected Song", "AddSelected" AddMenuItem "Add Selected Artist", "AddArtist" AddMenuItem "Add Selected Album", "AddAlbum" AddMenuItem "Add Current Filter", "AddFilter" And Addmenu item this is defined below as: Private Sub AddMenuItem(title As String, command As String) Dim x As CommandBarControl Set x = Application.CommandBars("List Range Popup").Controls.Add(1, temporary:=True) x.Caption = title x.OnAction = command x.Visible = True End Sub CommnadBars("List Range Popup") is the bar for right click context menu for Lists/Tables. For cells, you can use CommandBars("Cells") instead. Using Status Bar and Application.ScreenUpdating When you are doing a lot of things in the background programmatically such as bringing in a lot of data (such as a media library) or modifying lots of charts, or anything that is time intensive, you probably want to do a combination of 3 things: Update status with info Turn off screen updating DoEvents Update Status: Excel's status bar can be written to by setting Application.StatusBar, so if you are in a loop it is useful to do something like Application.StatusBar = "Now Importing Item " & i & " of " & MC.Count so that the end user will know approximately the time remaining for your operation. One thing to note, if you don't set Application.StatusBar = "" the status bar will keep your last value until you exit the application (not just the workbook) or something else writes to the status bar. Turn off Screen updating If you are moving around lots of data and the user doesn't need to see what is going on set Application.ScreenUpdating = "False" before your code and Application.ScreenUpdating = "True" after your code completes. With this setting you will gain a slight performance increase and the user will not see flickering and data moving do to your code changes. DoEvents If, on the other hand, you want to allow the user to continue to use the app while the code is running (ie, if you have an animated object or a long loop) then you can sprinkle DoEvents into your loops or methods, for Example: While Range("isRunning").Value DoEvents Animate Wend Where I define Animate to modify a few objects. Finish up with custom Ribbon UI Before I go into detail on this topic, I want to point out a great primer that Sam Radakovitz wrote a few months back on Dynamic Ribbon Galleries . This is a great primer on how to customize the ribbons as are the links he provides are relevant to this post as well: "Office Fluent Ribbon Developer Portal http://msdn2.microsoft.com/en-us/office/aa905530.aspx Tools and Utilities for the 2007 Microsoft Office System http://msdn2.microsoft.com/en-us/office/aa905356.aspx The RibbonX we will create will live in the document and travel with it. We will have to use a tool to get the RibbonX in the document. The one I'll use for this example is called the "Office 2007 Custom UI Editor". You can download it freely from here: http://openxmldeveloper.org/articles/CustomUIeditor.aspx " Custom UI Editor is a very light weight tool for giving you a few suggestions, validating CustomUI XML and generating sample VBA callback code. If you are going to be doing add-in development or extensive Ribbon UI customization, I highly recommend using VSTO 2008 instead of this tool. You can find info here: http://msdn2.microsoft.com/en-us/library/d2tx7z6d.aspx After you install the CustomUIeditor, open the attached "MediaPlayer.xlsm" to see the CustomUI.xml: < ?xml version = " 1.0 " encoding = " utf-8 " ?> < customUI xmlns = " http://schemas.microsoft.com/office/2006/01/customui " onLoad = " Mediaplayer.xlsm!OnRibbonLoad " > < ribbon startFromScratch = " true " > < tabs > < tab id = " settingsTab " label = " Media Player " > < group idMso = " GroupThemesExcel " visible = " true " /> < group id = " LibrarySettings " label = " Library " > < button id = " GetLibrary " label = " Get Library From WMP " onAction = " Mediaplayer.xlsm!GetFromWMP " image = " bones " Read...
    Filed under: ,

  • Building the Excel Media Player – Part 2

    Last post we talked about how to use Excel as a flexible development environment and touched on the benefits of using Office graphics to build your UI. This post goes into how to use ActiveX controls in Excel and how to contain and manipulate data in tables to drive your application. Using ActiveX Controls First we need to enable that hidden Developer Tab, Click Office Button, Excel Options and Toggle 'Show Developer tab in the Ribbon' on. Now click Developer Tab, Insert, on the bottom right corner is a 'More Controls' option. Here you will find a large variety of controls from File Browsers to Calendar controls, buttons, Internet Explorer; you can even register your own custom controls for using in Excel. We require music, let's search for Windows Media Player and draw it on the grid. This provides us with full media player functionality as well as access to its object model in VBA. The full Media Player functionality is out of scope but you can learn more by looking at the reference here: http://msdn2.microsoft.com/en-us/library/bb248315(VS.85).aspx , but we are going to briefly talk about tying our custom UI to control to it. Let's use the Play button as an example: Right click on the your 'play' button and click 'Assign Macro'. Since I named my play button 'Play' the default name is perfect 'Play_Click', click new. This will open VBA which you can access at any time by pressing Alt + F11. To begin using our control let's start simply by accessing pressing play by adding the following to your Play_Click method. Sheet1.WindowsMediaPlayer1.Controls.Play WindowsMediaPlayer1.Controls will contain almost all the media control functionality we need, but let's add some volume control. On the developer tab insert a scroll bar. Right click the scrollbar and click properties. Volume usually goes from 0-100, set this to min and max. Using the selection pane, rename the scrollbar to 'Volume' and finally double click the scrollbar and add this code: Sheet1.WMP.settings.Volume = Sheet1.Volume.Value That is all it takes to control volume. Before we go any further we need music to play, we have two options. Either search directories or grab the information directly from the Media Player. To further demonstrate ActiveX we'll be doing the latter, but first we need a place to store this data, what better place than a Table. Using Tables for data manipulation Ok, so now we have our basic front end UI, and a basic understanding on how to use ActiveX in our project, let's add a few tables and some data so that this media player can do something. In the application I have 2 main tables, one called library and one called playlist . To create a table, Select your range and press ctrl + T, or insert Tab,Ttable. I added a table that contains headers for all the useful information I might want to know about media, most important to us now are ID, Title, Artist, and Source URL. To populate this table I used Windows Media Player again, you can browse the directory directly but to further elaborate on how to use ActiveX with Tables I decided to do it this way: Dim MC, i Dim LList As ListObject Set LList = Sheets("Library").ListObjects("Library") Set MC = Sheets("player").WMP.mediaCollection.getAll Sheets("settings").ListObjects("playlist").DataBodyRange.Delete LList.DataBodyRange.Delete For i = 0 To MC.Count - 1 LList.ListRows.Add().Range.Value = Array(i, _ MC.Item(i).getItemInfo("name"), _ MC.Item(i).getItemInfo("artist"), _ MC.Item(i).getItemInfo("album"), _ MC.Item(i).getItemInfo("sourceURl"), _ MC.Item(i).getItemInfo("duration"), _ MC.Item(i).getItemInfo("genre"), _ MC.Item(i).getItemInfo("filetype"), _ MC.Item(i).getItemInfo("UserPlayCount"), _ MC.Item(i).getItemInfo("AlbumPicture"), _ MC.Item(i).getItemInfo("UserRating"), _ MC.Item(i).getItemInfo("UserLastPlayedTime"), _ MC.Item(i).getItemInfo("FileSize")) Application.StatusBar = "Now Importing Item " & i & " of " & MC.Count Next i First off, manipulating data in a table is much easier than doing the same in a simple range. Tables give you the ability to add calculated columns, reference columns by their name rather than location, add a row to the end as a simple array, and treat the entire table as an object, rather than a piece of your grid. This is just a touch of what tables can do for you. You can find more information here . For the most part you can rely on ListObject.ListRows.Add() which will (by default) return the last ListRow in your table. In the above example I am setting the array by passing the value an array of strings that I get from the Windows Media Player media collection object. You can learn more about the Table/Listobject OM here . Now we have a list of songs as populated from our...
    Filed under: ,

  • Building the Excel Media Player – Part 1

    Today's author, Jon Adams, a Tester on the Excel team. A copy of the spreadsheet discussed in this post can be found as an attachment at the bottom of this post. As you are probably well aware, Excel is a very versatile tool and is often used in ways that are hard to imagine. This blog post is a follow up of Joe's post "Unusual (but cool!) Uses of Excel" . I am going to show you how to use Excel for rapid application development using the new Office Art graphics Engine, ActiveX, Table Objects, VBA (Excel OM and ActiveX), and a touch of custom Ribbon code. The complete solution description will be split up over several posts, with the following topics covered in each: Part 1 Building UI using Office Graphics Part 2 Using ActiveX Controls Table Manipulation Data Analysis Part 3 Context Menus Ribbon UI Since I am an avid music geek and spend way too much time organizing my media library, our sample project is going to be a fully functional Media Player. Before I get started I will answer a question that I am sure is burning in your mind. Why in the world would I write such an application in Excel? Flexibility : I can quickly create mash-ups of my music library data with web service data with a few more clicks and determine things like 'listening habits' and do pivots on things like artists playing in the area. Best of all, this can all be done outside of a development environment and without compiling. Office Graphics : First off, as a tester, I wanted to create something that exercised as much of the Office Graphics engine that was kind of fun. Secondly, the new office graphics engine is much more professional looking and provides the end user with a lot more flexibility right out of the box. I wanted to come up with an interesting solution that showcased the power of the new graphics engine. Now let's get started. Download the file (link at the bottom of this post), play with it and get a feel for how it works. You will have to enable macros in order for the application to work. Building UI using Office Graphics Excel 2007 completely changed what is possible with graphics on the grid, and I will show you some of the features you can use to make an elegant solution. First let's break apart this UI. Choose a background and frame your UI: The background can be as a simple as a grey box to frame some controls or in this case I decided to use a picture. If you are building a tool for your company, you can use your corporate logo as a watermark. After the size is set by your frame, you can clean things up by holding Alt and dragging resizing to line up with the grid. For a crisper UI, I like to hide the rest of the rows. Select the first column header after your picture and press ctrl + right to select the remaining columns. Repeat for rows. This gives you something like this: If you wish to hide the column/row headers you can go to Excel Options (Alt + T, O) and toggle "Show row and column headers". Let's create the side-pane now. This simply consists of 5 rectangles grouped and a series of shapes for the playback controls. To make this step easy I suggest using the new Selection Pane (Home, Find and Select, Selection Pane) . This will help you to create deep grouped objects, and assign them useful names for later in the tutorial. Now add a few shapes to the grid. On the insert tab, shapes dropdown, you have a choice of many shapes for our UI. I added 6 rectangles, an equal sign and a few chevron symbols. I also went ahead and typed a few simple titles in the rectangles and was able to get this. If you multi-select the objects (hold down CTRL while you click the objects you want to select), you can right click and group them to make it a little easier to move them together. Now in the selection pane you can name your groups and your objects for better organization. I renamed all of my objects in the tree view and grouped them for easier use later and it looks like this in the selection pane. Notice the visibility toggles. When building UI it is quite useful to be able to just hide an entire group while working on something else. Ok, now let's make this look a little better with some snazzy effects. Some effects work on the group as a whole, such as reflection and glow, whereas others, such as bevel and some scene3d effects, will apply to each object individually. Let's start by selecting an object and clicking the format tab. Let's try adding a group effect. From the Shape Effects dropdown add a glow. The Format Tab gives us a lot of options, and maybe some canned preset will work but let's dive deeper. Press Ctrl + 1 to bring up the advanced formatting dialog. In the 3-D format tab, Top Bevel, the first bevel creates a very button like illusion. For a more metallic look change lighting to flood and material to metal . Now onto the fill tab; If you add a gradient it will be applied to the entire group. I also like to use transparency to create a more...
    Filed under: ,

  • Handling Workbook Dependencies using Excel Services

    Today's author, Sanjay Kulkarni, a Program Manager on the Excel Services team. We have heard from several customers about scenarios where they have workbook dependencies. So the output of one workbook is used in other workbooks for calculations. The dependencies could be a result of the business model meaning different people in the organization being responsible for the workbooks or it could simply be a way of isolating related logic in different workbooks. In this blog post we will look at a sample way of how these workbook dependencies can be handled by writing a little code using Excel Services Web Services API and SharePoint object model. Note that you will need MOSS 2007 with Excel Services configured to use what is described here. To simplify things let us look at a scenario with two workbooks. At a high level workbook one (Rates.xlsx) has mortgage rates that are refreshed from the database. This workbook also adds the overhead and calculates the retail rate that can be offered to customers. The second workbook (Payments.xlsx) has a list of potential customers and information about them. The idea is to calculate the first workbook to get the retail rates and then use these rates to calculate the mortgage payment for the customers in the second workbook. We will use document properties to pass values from the Rates workbook to the Payment workbook. The Rates workbook has a Pivot Table that has interest rates and a cell that stores the margin. To make the sample work independently we have removed the connection to the external database and instead we get the data from another sheet in the workbook however that can be easily changed. Here is what the Rates workbook looks like. 0.75 Margin above the wholesale rates Values Row Labels Interest Rates Retail Rates 1 Year 4.5 5.25 3 Year 5 5.75 30 Year 5.875 6.625 5 Year 5.25 6 7 Year 5.5 6.25 The Payments workbook has information about the clients, their mortgage as well as cells where the retail mortgage rates can be updated. Mortgage Type Interest Rate 1 Year ARM 5 3 Year ARM 5.5 5 Year ARM 5.75 7 Year ARM 6 30 Year 6.375 Client Name Email Id Loan Type Interest Rate Loan Term in Years Principal Monthly Payment Jack jack@contoso.com 3 Year ARM 5.5 30 $350,000 $1,987.26 Judy judy@contoso.com 5 Year ARM 5.75 30 $400,000 $2,334.29 The following sections describe the steps involved with some code snippets. The complete code and the workbooks can be downloaded at the bottom of this post (see attachments). This is a console application and accepts the margin rate added for overhead as a parameter. We use a fixed constant in the workbook if no margin is specified. To simplify things I have omitted the error handling for the most part. You will definitely need to add robust error handling if you are planning to deploy it for a practical application. First print the margin rate and some initialization if (args.Length > 0) Console .WriteLine( "Margin rate is = " + args[0] + "%" ); // Instantiate the Web service and create a status array object. ExcelService es = new ExcelService(); Status[] outStatus; string ratesWorkbook = "http://server-name/Site/doclib//Rates.xlsx" ; // Set credentials for requests es.Credentials = System.Net.CredentialCache.DefaultCredentials; Now we open the workbook and set the margin rate in the Rates workbook, refresh data and calculate the retail rates. // Open the Rates workbook, get new rates by refreshing the external data and // calculate. string sessionId = es.OpenWorkbook(ratesWorkbook, "" , "" , out outStatus); //Set the input overhead margin rate if specified omcommand line. if (args.Length > 0) { es.SetCellA1(sessionId, "RatesSheet" , "Margin" , args[0]); } // Refresh data and recalculate the workbook. es.Refresh(sessionId, "" ); es.CalculateWorkbook(sessionId, CalculateType.CalculateFull); Now that the retail rates are calculated; we get the retail rates and set them as document properties on the Payments workbook. The advantages of using the document properties for storing values are firstly the properties are associated with the document so if you update the workbook they will still be preserved. Secondly since they are stored by SharePoint the process of calculating rates and payments can be done at different times if needed by the business process. //Get the calculated rates from the workbook double rate1Year, rate3Year, rate5Year, rate30Year; valObj = es.GetCellA1(sessionId, "RatesSheet" , "Rate1Year" , false , out outStatus); rate1Year = Convert .ToDouble(valObj); valObj = es.GetCellA1(sessionId, "RatesSheet" , "Rate3Year" , false , out outStatus); rate3Year = Convert .ToDouble(valObj); valObj = es.GetCellA1(sessionId, "RatesSheet" , "Rate5Year" , false , out outStatus); rate5Year = Convert .ToDouble(valObj); valObj = es.GetCellA1(sessionId, "RatesSheet" , "Rate30Year"...

  • Clarification on VBA Support

    Following MacWorld earlier this week, there has been some inaccurate information circulating online regarding VBA support in Office for Windows. While it’s true that VBA isn't supported in the latest version of Office for the Mac and the VBA licensing program did close to new customers last year, we have no plans to remove VBA from future versions of Office for Windows. We understand that VBA is a critical capability for large numbers of our customers; accordingly, there is no plan to remove...
    Filed under:

  • The 2008 Office Developers Conference

    A bit of older news but worth mentioning again for those that might be interested. The Office Developers Conference (ODC) is being held this year in San Jose, California and will take place February 10-13. To quote the site: "ODC 2008 is the premier event for Microsoft Office and SharePoint developers , bringing together architects, developers, industry technical experts, Microsoft insiders, and key partners to redefine what it means to build on the Office system." Read More...
    Filed under:

  • Integrating Excel (Services) and PowerPoint with the Microsoft SDK for Open XML Formats

    Today's author: Danny Khen, a program manager who works on both the Excel and Excel Services teams. What is this about? Office 2007 introduced new default file formats – Open XML. Excel, Word and PowerPoint take advantage of them. One of the nicest things about those file formats is that they enable easy manipulation of Office documents without needing the Office applications up and running – by simply modifying XML “parts” within the files. In June 2007 we published an SDK that facilitates this...

  • Switching Data Sources in Excel 2007

    Today’s Author: Howie Dickerman, a program manager on the Excel team. Howie is going to discuss different ways to change data sources in Excel 2007. Prerequisite: This post assumes that you have read the blog post entitled “Managing External Database Connections in Excel 12” which can be found here . Folks who connect to external data from Excel will sometimes encounter the following user scenarios: Switch between test and production data sources I want to test my spreadsheet while connecting to...

  • Title: Excel 2007 XLL SDK is released!

    Today’s author: Danny Khen, a program manager on the Excel team who works in the areas of programmability and business intelligence. Danny worked with a number of people, both internal and external, on the Excel 2007 XLL SDK. The Microsoft Office Excel 2007 XLL Software Development Kit is out the door at long last. Many Excel developers have been waiting for the new XLL SDK for a long time now, since we released Office 2007 at the end of last year. It took us longer than we had initially thought...
    Filed under:
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.