Select similarly formatted text in Microsoft office 2007 Word
Did you know in Word you can automatically select all the text in a document that shares the same formatting? Office 2007 professional and Office 2007 Ultimate are so powerfull.
For example, you can select all text that is Arial, 12pt. Very handy if you are not using styles, yet want to apply a formatting change across select blocks of text.
To select text with similar formatting in Word 2007:
1) Select some of the text you want to select.Office 2007 key is available here.
2) Make sure you are on the home tab of the ribbon
3) Look for the “Editing” group on the right hand side of the ribbon
4) Click on “Select”
5) Click on “Select Text with Similar Formatting”.Office 2007 enterprise is the expert for the office workers.
Change the design of your handouts in PowerPoint 2007
If you have ever printed handouts for your PowerPoint presentations before, you know that they can be pretty bland. Just the slides all stacked up on a page, with plenty of whitespace.Dreamweaver CS4 is very easy-to-use!
Well did you know that you have complete control over how those handouts actually look? You can add your company logo, or a copyright statement, or remove the date or the page numbers. You can do whatever you want!
All of this is controlled from what is called the “Handout Master”. Those of you familiar with the “Slide Master” will know exactly what we are talking about. The easiest way to describe the handout master is that it is the template for all the handouts printed from the presentation.Office 2007 Pro is so great!
To access the Handout Master in PowerPoint 2007:
1) Click on the “View” tab in the ribbon
2) In the “Presentation Views” group on the left hand side, click on “Handout Master”
Once you are in the master, you can control all manner of different things, including the page setup, the handout orientation (portrait or landscape), the slide orientation on the handouts, and how many slides will appear on each page.Microsoft Office 2007 is welcomed by the whole world.
To get out of the “Handout Master” view… simply click the “Close Master View” button on the right hand side of the ribbon.Office 2007 professional and Office 2007 Ultimate are so powerfull.
‘till next time!
Add commas to your numbers in Excel 2007
Do you prefer to use commas when you write long numbers? Those commas are called “Thousands Separators”, and you can quickly add them to the cells in your Excel 2007 spreadsheet.windows 7 home premium is also my love!
To turn on the thousands separator:
1) Select the cells you want to apply the commas to
2) Make sure you are on the “Home” tab of the ribbon.Photoshop CS4 is so magic!
3) Look for the “Number” group – in the middle of the ribbon
4) Click on the button that looks like a comma! Acrobat 9 is so useful!
‘till next time!
Turn on (or off) sound when an email arrives in Outlook 2007
Are you really annoyed by the sound that Outlook makes when an email arrives in your (or more likely your colleagues) inbox. Or do you need to know instantly when there is a new message addressed to you? windows 7 home premium is also my love!
Whichever side of the fence you sit on, the chime or sound email notification is a configurable feature of Outlook 2007.
To turn on or turn of audible email notifications in Outlook 2007:
1) Click on “Tools” in the menu”
2) Click on “Options”
3) Make sure you are on the “Preferences” tab
4) Click on “E-mail Options…”
5) Click on “Advanced E-Mail Options…”
6) About 1/3 of the way down the “Advanced E-mail Options” windows, there is a section called “When new items arrive in my Inbox”. Check (or uncheck) “Play a sound”. Photoshop CS4 is so magic!
7) Click “OK” three times to close all the windows
‘till next time!
Making MZTools for VBA work on Vista
I was fooling around writing some code tonight, and realized that I hadn’t set up my custom error handler text in MZTools. Photoshop CS4 is so magic! I’ve had MZTools on here since I last re-formatted my laptop back in September, and use it all the time, but I never bothered to go in and tweak my own settings. Tonight I was working on a larger project, and wanted some customized error handlers, procedure headers and the like. I jumped into the settings, set it up the way I wanted, and clicked the OK button to save it… and Boom! Acrobat 9 is so useful! An error popped up:
Now, I am using the 64 bit edition of Vista on this machine, which is why it’s giving “C:\Program Files (x86)\MZTools3VBA\MZTools3VBA.ini” as the path. I took a second to browse through my machine, and discovered that the path does exist. So of the “Path/File access error”, that pretty much meant that something was gumming up the access to the file itself. Dreamweaver CS4 is very easy-to-use!
Basically, this is a permissions issue. I’m not sure if it is the same on the 32 bit version of Vista, but I’d bet it is. Vista is a *little* more security conscious that XP, but what was funny to me about this is that I am an administrator on my machine. Regardless, I decided to take a look at the file permissions. To do this:
- Right click the file
- Choose Properties
- Click the Security tab
Now, on my machine there are 3 account classes listed in the box:
- SYSTEM
- Administrators
- Users
Office 2007 Pro is so great! When I click on SYSTEM or Administrators, I can see that they have full permissions to modify this file, as shown below:
But look at the settings for Users:
:
Fortunately, this is relatively easy to toggle.
- Click on the Edit button (and say yes to the Vista warning).
- Click on Users
- Check the Allow box for Full Control
At this point, as shown below, all the checkboxes under “Allow” will be checked. (Except for “Special Permissions” which doesn’t appear on screen at the moment.).Microsoft Office 2007 is welcomed by the whole world.
Click Apply, then OK, then OK again to get out of the permissions screen.
And that’s it. You’ll now be able to save your customizations to MZTools.
To be clear, this issue doesn’t actually have anything to do with MZTools, but rather is a permissions issue with Windows Vista. What really makes me shake my head, though, is that my account is an administrator account. I should not need to do this.Office 2007 professional and Office 2007 Ultimate are so powerfull.
At any rate… I’m not sure if you can set permissions on files during installation, as I’ve never deployed a full program of my own. (I’m guessing that it is possible, and expect that someone will confirm that for me shortly. )
To be very clear, the rest of the functionality of MZTools3 works flawlessly. It’s only the ability to customize your settings that runs into trouble. Given that it was written long before Vista came on board, I think it’s pretty impressive that this is the only change you need to pull off to keep it fully functional.
Oh, and if you do any programming in Office and you’re not using MZTools yet, you should be. It’s a free download that you can get right here (you want the MZ-Tools 3.0 for VB6, VB5 and VBA.) It adds a load of useful functionality, and works in Excel 97-2007, and on Windows XP and Vista. Office 2007 key is available here.
Excel 2010 Finally Fixes SpecialCells 8192 Limit
If you filter data out of large data sets, you may have run into an issue where you can hit 8192 non-contiguous ranges, which Excel couldn’t handle. While this issue has been in Excel for many versions, I can honestly say that I haven’t run into it in years, as I’ve always sorted my data first, to avoid this issue. Office 2007 enterprise is the expert for the office workers.
To replicate the issue, try this in any version of Excel prior to 2010:
Enter the following data in a blank sheet:
- A1: Row
- B1: Index
- A2: =ROW(B2)
- B2: =IF(A1=1,2,1)
Now copy row 2 down about 20,000 rows… MS Office 2007 is the best invention in the world.
Next, select row 1 and Filter your data on Column B to only show 1’s
Now, select from cell A3 to the end of your data in column B and try to copy it… You’ll get a message like this one…
Well guess what… that’s no longer necessary! In Excel 2010, the Excel team has removed this limit. And while I haven’t tested this, the new limit is apparently imposed by the memory in your computer, not a hard coded limit. Enjoy the Quickbooks 2010 bringing you the best life ever.
It’s great to see that such a long standing bug has finally been resolved.
VBA Focus Group at TechEd
Do you use VBA (or any other extensibility mechanism) in Office and are you headed to TechEd? If so the we would like to talk with you in an "invitation only" meeting.Office 2007 Pro is so great! This is your chance to share your feedback and influence the work that Microsoft is doing in the programmability space. You must be available for either:
- TechEd Dev: Held at 4:30 on Thursday, June 5th
- TechEd ITPro: Held at 3:00pm on Monday, June 9th
If interested, please respond to the following questions to Amanda.Silver[at]microsoft.com (replace [at] with @):
- Dev week or ITPro week session?
- Please describe your current role.
- What's the size of your organization? Workgroup?
- How/when do you use VBA? Enjoy the Quickbooks 2010 bringing you the best life ever.
- Are you familiar with .NET and Visual Studio Tools for Office? If so, do you use those tools? If not, why not?
We want to hear from you -- come and make yourself heard! windows 7 home premium is also my love!
Building an Asset Tracking Application in Excel Services – Part 4 of 5
Today's author, Dan Parish, continues on his discussion of building an Excel Services solution.Office 2007 Pro is so great!
In Part 3b I walked through creating a User Defined Function (UDF) that pulls information from a SharePoint List into Excel Services. In today's post, I will describe how to create the two web part pages that make up the actual application: the page that end user's see, and the page that the admin uses.
Overview
Now that we have our spreadsheet running on the server and displaying data entered into the SharePoint List, all we need to do is present this information in a user-friendly way. Using Excel Web Access (EWA) – Excel Services' web part – we can seamlessly integrate this information into any existing site.Microsoft Office 2007 is welcomed by the whole world.
Creating the end user's web part page
As a refresher, here is the page we are going to create:
The first thing we need to do is actually create the web part page itself. Virtually every page in SharePoint is a web part page, which is a page that you can add web parts to and remove web parts from. You can also create your own web part pages from scratch, and that's what we are going to do here.
To create a new web part page, go to the site in which you created your SharePoint List in Part 3a, and click the Site Actions button located on the top right of the page, and then click Create. If you can't see a Site Actions button, then you don't have sufficient permissions to create a web part page on that site.Office 2007 enterprise is the expert for the office workers.
On the Create page you'll see a wide assortment of things you can create. On the right hand side, under Web Pages, click Web Part Page to create a new web part page. Call this page Machines and pick the Full Page, Vertical layout. You can save the web part page to any Document Library you have (such as Shared Documents). Then click Create.
You'll be taken to your new web part page in edit mode. The first thing we need to do now is add web parts. The page we want to create is actually composed of five web parts which are, from top to bottom:
- A Current User Filter which detects the current user's name and passes it into connected web parts .Enjoy the Quickbooks 2010 bringing you the best life ever.
- An EWA web part displaying the WelcomeMessage named range
- A Content Editor web part displaying the link to enter new information into the SharePoint List
- An EWA web part displaying the Laptops named range
- An EWA web part displaying the Desktops named range
To add a web part just click the big orange bar that says Add a Web Part and then select the web parts you want to add.
Once you've added all of the web parts, we need to start configuring them, which we'll do starting from the top.
Configuring the Current User Filter
The Current User Filter is actually hidden when the page is rendered not in edit mode. What it does is detect the identity of the user visiting the web part page and send it into any connected web parts. We'll connect the web parts in the next step, but first we want to change the format that the Current User Filter will send the user's identity in.Windows 7 Professional brings people more convenience!
By default, the Current User Filter sends the user's identity in the form DOMAIN\UserName. We want it to send a friendly name (like Dan Parish) though. To change this, open the tool pane for the web part by clicking Edit > Modify Shared Web Part at the top right of the Current User Filter. When the page reloads you'll be presented with the tool pane for this web part.
Select the radio button for SharePoint profile value for current user, and then select Name as the value. Now, if your SharePoint farm is configured correctly, the Current User Filter will send the friendly name instead of the username.Office 2007 professional and Office 2007 Ultimate are so powerfull.
When you are done, click OK to apply your changes.
Configuring the EWA Web Parts
To configure the first EWA web part (the welcome message) start by clicking the Click here to open the tool pane link in the EWA. That will reload the page and display the EWA's web part tool pane.
The first thing we need to do is pick the workbook we want to display. Do this by clicking the … to the right of the Workbook textbox which will launch a file picker. Use the file picker to browse for the workbook we created and published in the previous parts of this series. windows 7 home premium is also my love!Once you've found it and selected it click OK (or double-click on the workbook).
The next thing we need to do is tell the EWA what we want to display in the workbook using the Named Item field. We could leave this blank to display the entire workbook, but in this case we only want to display the welcome message, so enter WelcomeMessage (which is the name of the named range encompassing that message). This will cause the EWA to only render that named range.Photoshop CS4 is so magic!
If you click Apply now, you'll see the welcome message render in the EWA saying welcome to Mr. Nobody. However you'll also see that the toolbar displays, and the web part isn't sized correctly. To really make the EWA blend into the web part page, we need to tweak some more settings.
First, in the Toolbar and Title Bar section, set Type of Toolbar to None. This will turn off the toolbar for the EWA. Next, scroll all the way down in the tool pane and expand the Appearance section. Select that the web part should have a fixed height, and set it to 50 pixels. Finally, at the bottom of the Appearance section, set the Chrome Type to None.Acrobat 9 is so useful!
Click OK, and you'll see the EWA render just as you'd expect it to: sized correctly and integrated into the web part page. There is only one final issue: the Parameters Task Pane in the EWA is still showing the EmployeeName parameter. The EmployeeName parameter is the cell that we will be sending the user's friendly name from the Current User Filter into. To learn more about parameters, see the Change workbook parameters in Excel Services article on Office Online.
There are two ways to hide the Parameters Task Pane: there is a web part property we could turn off in the EWA's web part tool pane, or, if all parameters in a workbook are having their values sent in from other web parts, the EWA automatically hides the task pane. Every time you connect a parameter in an EWA to another web part it is removed from the task pane, and once they are all connected the task pane is automatically hidden. So, since we need to connect the EWA to the Current User Filter anyway, we'll take that approach.Dreamweaver CS4 is very easy-to-use!
To connect the EWA to the Current User Filter, click Edit > Connections > Get Filter Values From > Current User Filter on the EWA. This will display the Configure Connection dialog box where you can select the parameter that you want the Current User Filter to send its value into. Select EmployeeName and click Finish.
You should now see Welcome <Your Name>! and no Parameters Task Pane.
To configure the other two EWA web parts follow the exact same steps (including connecting them to the Current User Filter, which is required so that they know which user to show the laptop/desktop results for) but for the Laptops EWA web part the named item to display is Laptops, and for the Desktops EWA web part it is Desktops. Also, enter 210 pixels for the height of both of these web parts.
Configuring the Content Editor Web Part
The final web part to configure on the end user's web part page is the Content Editor web part. This is the web part that will contain the link to the SharePoint List where people will enter their machine information.
We want this web part to do two things:
- Link users to the SharePoint List where they will enter their machine information in such a way that when they submit their machine information they will be taken back to this web part page
- Convey information about what this page is for
The Content Editor Web Part allows you to enter information in one of two ways: using a Rich Text Editor, or by entering source code. We'll use the Rich Text Editor.
First, open the tool pane for the Content Editor web part by clicking Edit > Modify Shared Web Part at the top right of the web part. When the page reloads and shows the web part tool pane, click Rich Text Editor… to launch the Rich Text Editor.
Enter the following text:
Add a new machine
Please ensure you add all machines currently in your possession, including desktops and laptops.
Shrink the size of the second line by highlighting it, clicking the Font Size button, and then selecting 1 as the font size.
Next, make Add a new machine into a link by highlighting it and clicking the Hyperlink button. This will open a new window where you can enter the hyperlink. The hyperlink you want to enter is:
http://<server>/<site>/Lists/Machines/NewForm.aspx?Source=/<site>/Shared Documents/Machines.aspx
Make sure to change <server> to your server and <site> to your site. The Source parameter is what tells the SharePoint List where to send the user after they have entered a new item. In this case, we want to send them back to our end user web part page.
You can now change any other formatting you like, and then click OK in the Rich Text Editor and again in the web part tool pane to commit your changes. You can then click Exit Edit Mode in the top right of the web part page to exit edit mode and see your web part page the same way that everyone else will.
Creating the Admin's Web Part Page
As a refresher, here is the page we are going to create:
Since you just finished building the end user's page you already know everything you need to know to build this page as well. There are only a few differences:
- When creating this web part page call it Machine Overview and pick the Header, Right Column, Body layout. We'll add a custom web part to the right column in Part 5.
- Since the admin page shows data for all users it doesn't need a Current User Filter. The web part page simply consists of three EWA web parts.
-
From top to bottom, the named range each EWA web part displays is:
- EmployeeList (the list of which employees have and have not completed the survey)
- LaptopListing (the list of all laptops entered with conditional formatting showing which need to be upgraded or replaced)
- DesktopListing (the list of all desktops entered with conditional formatting showing which need to be upgraded or replaced)
- Each of the three EWA web parts should have a height of 300 pixels
The only thing that is different that we didn't do in the end user's web part page is that since we aren't connecting the Current User Filter to each of the web parts on the admin page, we need to explicitly tell each EWA to not show the Parameters Task Pane. To do that, in the Navigation and Interactivity section of the EWA's web part tool pane, uncheck Display Parameters Task Pane.
Once completed, you should now have both web part pages displaying all the relevant data in EWAs that are seamlessly integrated into your new web part pages.
In Part 5, we'll walk through how to create a custom web part that archives these reports to a separate Document Library for record keeping.
Building an Asset Tracking Application in Excel Services – Part 5 of 5
Today's author, Dan Parish, concludes his discussion of building an Excel Services solution. Office Professional 2007 and Office Ultimate 2007 are my favorite.
In Part 4, I walked through creating the web part pages for the solution. At this point, you should have a fully functional asset tracking application up and running. The last piece, that we'll build today, is a simple web part that will allow your admin to archive all of the information they have collected.
Overview
At this point we now have the entire application up and running. We can collect information entered by users, and they can see how their machines compare against their coworkers. Our admin can at a glance see who has (and hasn't) filled out the survey, and can see who needs upgrades or new machines.windows 7 home premium is also my love!
However, there is still one problem with our application: there is no real way to archive the information. Archiving information is important for transparency, accountability, and sometimes for government compliance. This scenario is no different. With the current model someone could change their information at any point, even after the data collection period was supposed to end. There is also no good way to do year over year comparisons.Photoshop CS4 is so magic!
To rectify this, we are going to build a small web part that goes on the admin's page. The admin can at any point just click this button to create an archive of all of the information. The web part will look like this:
After clicking "Archive", the web part will show a completion message, like this:
The archive will be an Excel workbook that is a snapshot of the information in our asset tracking workbook. You can learn more about snapshots in the Open a workbook or snapshot in Excel from Excel Services article on Office Online, but basically, a snapshot is just a copy of the workbook that only contains the values and formatting of objects and sheets that the user is allowed to see. Since a snapshot contains no formulas or data connections, they are perfect for this scenario since they remove the link between the workbook and the source data, and so whenever they are opened in the future they will always only contain the data that they had when they were created.Acrobat 9 is so useful!
The code for this web part is included at the end of this post.
Creating the Archive Document Library
The first thing we need to do is create a place to store the archived reports. To do this, we'll need to create a new Document Library. The steps to do this are very similar to when we created the SharePoint List in Part 3a.
Navigate to your site and click on Site Actions, and then Create. If you don't see a Site Actions menu, or if there is no Create in the Site Actions menu, then you aren't an owner of the site.
The next page that appears is a page showing all of the different types of things you can create. We want to create a Document Library, so under Libraries on the left, click Document Library.
You'll then be prompted to enter a name for the Document Library. Call it Archived Workbooks, select Microsoft Office Excel spreadsheet for the Document Template, and then click Create. Once created, you'll be taken to your new Document Library.
Creating the archive web part
To get started, create a new Visual C# Web Control Library, and add references to the following:
-
Microsoft.SharePoint
- Since web parts need to inherit from Microsoft.SharePoint.WebPartPages.WebPart
-
System.Xml
- We will be using this to add a custom web part property
-
Microsoft.Office.Excel.Server.WebServices.dll
- This is Excel Services' web service which we will be calling into to get a snapshot of the workbook .Dreamweaver CS4 is very easy-to-use!
If you've never created a web part before, take a look at the Using Excel Web Services in a SharePoint Web Part and Deploying and Optimizing a SharePoint Web Part That Calls Excel Web Services articles on MSDN. They walk through all of the steps to create your web part, sign it, and build it into a CAB package that can be deployed into SharePoint. For this post, I will assume that you've either already done all of that setup work to your new project, or that you've simply opened the completed project included at the end of this post.Office 2007 Pro is so great!
To get started, first create a new class and inherit from WebPart, like so:
public class ArchiveMachineReport : WebPart
{
…
The next thing we need to do is declare some global variables and add a new event handler to call our own custom function on load:
…
// Declare global variables
protected Label lblResult = new Label();
protected Button cmdArchive = new Button();
private string wbLocation = null;protected override void OnInit(EventArgs e)
{
// Call our own load event
this.Load += new EventHandler(ArchiveMachineReport_Load);
base.OnInit(e);
}
protected override void RenderWebPart(HtmlTextWriter output)
{
base.RenderWebPart(output);
}
…
Our global variables reference three things which are, in order:
- lblResult: The label that will show if the web part is ready, or provide error strings or a link to the archived report if the archiving process completed successfully
- cmdArchive: The button that the user will click on
- wbLocation: A string specifying the location of the Asset Tracking workbook that we will be archiving. This is a class variable because it will be specified by the site admin as a web part property. Microsoft Office 2007 is welcomed by the whole world.
During load, we want to call our own method because we need to create the UI for the web part. We do this in the ArchiveMachineReport_Load method.
Building the UI for the web part
Our web part is basically just a table with two rows and two columns. In the first column are the labels Archive Report, and Status. In the second column is the Archive button, and the actual status. So, all we need to do is write some C# that creates this simple table. We start by creating the function and defining the table itself:
…
private void ArchiveMachineReport_Load(object sender, EventArgs e)
{
// Create the HTML table to display in our web part
Table tbl = new Table();
TableRow actionRow = new TableRow();
actionRow.Cells.Add(new TableCell());
actionRow.Cells.Add(new TableCell());TableRow resultRow = new TableRow();
resultRow.Cells.Add(new TableCell());
resultRow.Cells.Add(new TableCell());
…
As you can see, we created a row and added two columns to it, and then created another row and added two columns to it.
Next, we'll add controls to the first row:
…
// Add archive row controls to the table
actionRow.Cells[0].Text = "Archive Report:";
actionRow.Cells[0].Font.Bold = true;
cmdArchive.Text = "Archive";
actionRow.Cells[1].Controls.Add(cmdArchive);
…
In the first cell we put the text Archive Report:, and in the second we set the text on our button and add it to the cell. Remember that we already created the cmdArchive button as a global variable.
Now we'll add controls to the second row:
…
// Add result row controls to the table
resultRow.Cells[0].Text = "Status:";
resultRow.Cells[0].Font.Bold = true;
lblResult.Text = "Ready";
resultRow.Cells[1].Controls.Add(lblResult);
…
In the first cell we put the text Status:, and in the second we set the default label text to Ready and added the label to the cell. Remember also that we already created the lblResult label as a global variable.
Next, we want to actually assign an action to the button, like so:
…
// Add button action
cmdArchive.Click += new EventHandler(ArchiveReportUsingWebService);
…
What we did was say that when the cmdArchive button is clicked, call the ArchiveReportUsingWebService function, which we'll write in just a moment.
Finally, we need to actually add these two rows we just created to the table, and add the table to the web part:
…
// Add everything to the table
tbl.Rows.AddRange(new TableRow[] {actionRow,
resultRow });// Add the table to the web part
this.Controls.Add(tbl);
}
…
That's it. If you built and deployed your web part now it would show the UI you see in the first screenshot above, but of course it wouldn't actually do anything yet. Let's change that by building the function that is executed when you click the button.
Archiving the report
The first thing we need to do is declare our function and our variables, like so:
…
private void ArchiveReportUsingWebService(object sender, EventArgs e)
{
// Declare variables
Status[] status;
string sessionid = null;
string archiveName = null;
ExcelService es = new ExcelService();
…
The variables are, in order:
- status: All calls to Excel Services' web service return a status that can contain things such as error messages, etc.
- sessionid: All calls to Excel Services' web service require passing a session id (other than OpenWorkbook, which returns a session id). The session id uniquely identifies the session that you are working on.
- archiveName: This will be the name of the archived workbook we will create.
- es: An ExcelService object to call into Excel Services' web service
We'll start by opening the Asset Tracking workbook and getting the session id for this session:
…
// Get the workbook stream and save it to the specified SharePoint List
try
{
// Open a session with the workbook
sessionid = es.OpenWorkbook(wbLocation, "en-us", "en-us", out status);
…
Everything is within a try block in case something goes wrong. If something does go wrong, this will allow us to catch it and notify the user. The OpenWorkbook method takes three in parameters and one out parameter. They are, in order:
- The full path to the workbook to open. In this case, wbLocation is the global variable we created earlier and that the page creator will set as a web part property.
- The UI culture for the workbook.
- The data culture for the workbook.
- Any status messages that are returned from this call.
If it succeeds, the method returns a session id that must be used in all subsequent calls to this session.
So, now that we've opened the workbook, let's get a file stream of it and save it into memory:
…
// Get workbook into memory
byte[] workbookContent;
workbookContent = es.GetWorkbook(sessionid, WorkbookType.PublishedItemsSnapshot, out status);
MemoryStream file = new MemoryStream(workbookContent);
…
Here we call Excel Services' GetWorkbook method. GetWorkbook is used to retrieve a full workbook that can be opened in Excel. It takes two in parameters, and one out parameter. They are, in order:
- The session id of the session to get the workbook from. This is important since if you had also set some cells, refreshed some external data, etc., the workbook GetWorkbook would return would be the modified version in your session.
-
The type of workbook to retrieve. This can be one of three things:
- FullSnapshot: This is a snapshot of the entire workbook, not just the published items. It still only contains the values and formatting, but it contains sheets that you may have marked as not viewable on the server. You must have at least Read permissions to the workbook to call this method.
- FullWorkbook: This is the full workbook, formulas and all. You must have at least Read permissions to the workbook to call this method.
- PublishedItemsSnapshot: This is what we actually are using. It is a snapshot of the workbook that is restricted to just the sheets or objects you specifically marked as viewable on the server.
- Any status messages returned from this call.
The next thing we need to do is find the location we are going to save it to:
…
// Get the folder to save it to
SPSite site = new SPSite(http://server);
SPWeb web = site.OpenWeb("site");
web.AllowUnsafeUpdates = true;
SPFolder folder = web.GetFolder("archive folder");
…
This could also be a web part property, but for simplicity we are hardcoding it here. Simply change server to your server name, site to your site name, and archive folder to the folder you want to archive the workbook to (Archived Workbooks if you named it as specified above).
Now we just need to actually save the workbook:
…
// Save workbook to SharePoint List
SPFileCollection files = folder.Files;
archiveName = "Archived Report " + (files.Count + 1) + ".xlsx";
files.Add(archiveName, file, true);
…
Here you can see that we call the workbook Archived Report #.xlsx where # is one greater than the last number used. So, it will start as Archived Report 1.xlsx, and increase from there. That way, you never overwrite any existing archives. We then add the file to the Document Library.
Finally, we just need to close the file, update the status message for the user, and catch any errors that could have occurred:
…
// Finish off
file.Close();
lblResult.Text = "Report successfully archived (<a href='http://server/_layouts/xlviewer.aspx?id=http://server/site/archived folder/" + archiveName + "&DefaultItemOpen=1'>view</a>)";
}
catch (Exception ex)
{// Failed to get workbook stream or save the workbook
lblResult.Text = ex.Message;
}
}
…
Again, we have hardcoded in the link to the archived workbook that the admin will see for simplicity. You'll have to update server, site, and archived folder here as well.
We then catch anything that could have gone wrong in our try block, and update the status message in the web part with what went wrong (if anything).
That's the end of the method! The last thing we need to do is create the web part property that I have referenced several times.
Creating the web part property
Because we want this web part to work no matter where the Asset Tracking workbook is, we need to add a simple web part property so that the page creator can paste in the URL of the workbook they've already created. The code for this is:
…
#region Public Properties
[Browsable(true),
Category("Miscellaneous"),
WebPartStorage(Storage.Personal),
FriendlyName("Workbook Location"),
Description("Enter the location of the workbook to archive.")]public string WorkbookLocation
{
get
{
return wbLocation;
}
set
{
wbLocation = value;
}
}
#endregion
}
}
This adds a web part property into the Miscellaneous section of the web part tool pane, with the specified name and description. It sets the wbLocation variable that we use when calling the OpenWorkbook method.
That also completes our class. Now all we need to do is deploy the web part and add it to our web part page.
Deploying the web part
This part is simple. If your web part is created as per the referenced articles (or if you are using the attached code sample), when it is built it will generate a CAB file. All you need to do is copy it to your server and run the following STSADM command on your server:
"<drive>:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm.exe" -o addwppack -filename "<location of built CAB file>\ArchiveMachineReportCAB.CAB" -url http://localhost -globalinstall -force
Don't forget to replace <drive> with the drive you have MOSS installed on, and <location of built CAB file> with wherever the CAB file is on your server. It's often easiest just to put it in the same folder as stsadm.exe.
Adding the web part to your web part page
Once you've executed the command, navigate to the admin's web part page and put it into Edit Mode by selecting Site Actions > Edit Page. In the right hand column, click Add a Web Part. If you scroll to the Miscellaneous section, you will see a web part called Report Archive Web Part (or whatever you named it). Check the box next to it and click Add.
The web part should now be added to your web part page. All you need to do now configure it. Click the edit dropdown at the top right of the web part and then click Modify Shared Web Part. When the web part tool pane appears, first select that the web part should have a fixed width, and set it to 200 pixels. Next, set the Chrome Type to None, and then expand the Miscellaneous section and enter the location of the Asset Tracking workbook into our custom web part property. When you are done, click OK.
The web part should now be ready to use. Exit Edit Mode by clicking Exit Edit Mode at the top right of the page and give it a try!
Conclusion
I hope you've enjoyed this series. This solution shows you how to leverage Excel Services to build rich SharePoint applications, and really focuses on three key areas:
- Extending the capabilities of Excel Services using User Defined Functions
- Integrating Excel Services into your existing dashboards
- Building custom applications using Excel Services Web Service
Using the basics described here, you should be well equipped to branch out and build the custom solutions your company has been waiting for.
How to Create a Perpetual Yearly Calendar in Excel
Today's author: Mark Gillis is an Excel writer. He was born October 3, 1952. Does his birthday fall on the same weekday as your birthday? Office 2007 Pro is so great! Do you have nagging, unanswered questions floating around in the back of your mind like: "What day of the week does the date, January 23, 4589 fall on?", "What's the date of the eighth Thursday of 1922?", and "Is 9000 a leap year?".
If you're like me, the answer is clearly no. Nevertheless, there's a remote chance that someday a question like this will arise and you won't have the answer at your finger tips. If and when that day comes, you will either need a friend with a photographic memory or a perpetual yearly calendar. If your friends have other talents, you'll need to dig up a calendar of this type. To save you the trouble, I've written one for you that uses Excel and here's how it works. Microsoft Office 2007 is welcomed by the whole world.
Download the perpetual yearly calendar
An overview of the problem
As most of you probably know, Excel's date calculation engine handles a time span between the years 1900 and 9999 by using a serial number to calculate dates within that time span. January 1, 1900 is a value of 1 and Dec. 31 9999 is a value of 2,958,465. Excel's date formatting features, formula calculation abilities, and built-in date and time functions give you a wonderful foundation when it comes to creating a perpetual calendar. MS Office 2007 is the best invention in the world.
Many yearly calendars are formatted by showing the twelve months of the year in a grid pattern of three by four for the months, with each month further displayed as a "month matrix" of six rows and seven columns, with the month and day of the week as column headers.
As you can see, the month name and day of the week are fixed in a pattern that works with any month of any year, so that part is easy when it comes to creating a perpetual yearly calendar. And we already know what a valid range of years is for the calendar, so that's a simple matter of adding data validation to ensure a correct year. Enjoy the Quickbooks 2010 bringing you the best life ever.
All we need to do is fill in the blanks, where appropriate, for each year. That's all…
The most pressing challenge when creating a perpetual yearly calendar is to create a formula or set of formulas that can calculate the correct date number for each possible position in the calendar, and only display the date if it falls within the current month matrix. The issue is the same for each month and year, although the position of each date varies from month to month and year to year. Here are the extreme cases that you would need to handle in the formula for each "month matrix":
|
The "first" falls on a Sunday |
The "first" falls on a Saturday |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Dates at the end of each month are not complicated to handle. When the month of the date changes to the following month, don't display it. Windows 7 Professional brings people more convenience!Dates at the beginning of each month are complicated to handle. If only the first day of every month began on a Sunday. But the calendar gods -- from assorted mythologies -- Janus (Roman), Thor (Norse), the Sun god (Egyptian), others who I dare not offend -- have conspired against us and so we have to figure that out for ourselves.
The first of any month can fall on any day of the week. The first Sunday of any month matrix can be any one of seven different dates. Therein lies the tricky part of creating a formula that can handle the vagaries of time. To find the date of the first Sunday of any month matrix, you need to find the date of the first of the month, subtract the day of the week number, and then add 1. When you subtract the day of the week number, you end up with the last date of the previous month, so that's why it's necessary to add 1. Here are the seven possibilities for any month, using January as an example:
| +1 | ||||||
| -1 |
-2 |
-3 |
-4 |
-5 |
-6 |
-7 |
| S |
M |
T |
W |
T |
F |
S |
| 1 |
2 |
3 |
4 |
5 |
6 |
7 |
| 31 |
1 |
2 |
3 |
4 |
5 |
6 |
| 30 |
31 |
1 |
2 |
3 |
4 |
5 |
| 29 |
30 |
31 |
1 |
2 |
3 |
4 |
| 28 |
29 |
30 |
31 |
1 |
2 |
3 |
| 27 |
28 |
29 |
30 |
31 |
1 |
2 |
| 26 |
27 |
28 |
29 |
30 |
31 |
1 |
Let's summarize the problem that the formula calculation must solve. For the years 1900 to 9999, in each position in a calendar grid, calculate the correct date number (from 1 to 31) and display that number, but only within the month matrix that the date occurs. Office 2007 professional and Office 2007 Ultimate are so powerfull.
For the dates of the previous month that fall within a month matrix (in row one), don't display them, and for the dates of the following month that fall within the month matrix (in row five or six), don't display them either.
The solution
If a picture is worth a thousand words, then a demo is worth a million. It's time for a little show and tell. Watch the following demo to see how the perpetual calendar works and how the formulas and names are used to calculate the correct dates.
Watch this: How the perpetual yearly calendar works
Summing up
Some final observations. Without stating the obvious, this calendar doesn't work properly without automatic calculation set. I mention this only because it's easy to forget that you might turn it off for whatever reason. Also, the calendar handles the possibility that the workbook might be set to the 1904 date system by using conditional formatting. Although I could have used error handling in the formula, that would have made the formula more difficult to read.
And yes, the rumors are true, there were years before 1900 and there hopefully will be years after 10,000. This calendar doesn't handle them. You are welcome to try. Time is on your side.