Query Access DB from Excel 2007 by "Microsoft Query"

Overview

Let's consider this scenario: I have a Access DB and want to retrieve the data in Excel. In Excel 2007, the easiest way to do it (as far as I know) is to use built-in Access connect ("Data" tab => "Get External Data" group => "From Access").

However, in this way I cannot pass parameter into the query, how about if I want to use the value of a cell as the query parameter? In this case, you can use "Microsoft Query" in Excel 2007.

Seems that "Microsoft Query" can be used on other DB, but I haven't tried it out yet.

Reference

1. Connect to (Import) external data (Excel 2007)
2. Use Microsoft Query to retrieve external data
3. Customize a parameter query

Steps

Build a parameter query with "Microsoft Query"

1. In Excel 2007, select "Data" tab => "Get External Data" group => "From Other Sources" => "From Microsoft Query"
2. Select your Access database
3. In the "Query Wizard", setup your query and select "View data or edit query in Microsoft Query" to open "Microsoft Query"
4. In "Microsoft Query", click the "Show/Hide Criteria" button (the button with a pair of glasses on it)
5. Select the Criteria Field (Field to be compared in SQL 'WHERE' clause), and enter "[Parameter Name]" in "Value" to specify a parameter in the SQL query.
6. Switch to SQL view and you should see a 'WHERE' clause like this:
   WHERE customer_id = ?
   The question mark "?" represents a parameter.
7. Go ahead and modify the SQL as you like. When finished, select "File" => "Return Data to Microsoft Office Excel" to save and exit.

Use a cell's value as parameter (For more detail, go to Reference 3)

1. Click on any cell of your query result
2. Select "Data" tab => "Connections" group => "Properties" (If "Properties" button is grayed out, redo Step 1 )
3. Click "Connection properties" (The little button next to Connection Name)
4. Select "Definition" tab, click "Parameters" (If there is no "Parameters" button, it means that your query doesn't contain parameter, redo Step 1-7 in previous section)
5. Select the desired parameter, click "Get the value from the following cell:" and select the cell, you may also select "Refresh automatically when cell value changes"
6. Done. Change the value of the cell to test the query.
7. Happy building query! (Hopefully...)

Eliminate error messages with "IFERROR" in Excel 2007

Overview

In making dynamic Excel spreadsheet, sometimes we may have a function with result "#REF" or "#DIV/0". Some of these error messages are avoidable, but some of them aren't. In this case we have to conceal (hide) the error message. In Excel 2003 or before, we were using the "IF" function to check whether the function results in an error. However, this workaround is cumbersome and not efficient, because we have to put the function twice inside the "IF" statement.

Now in Excel 2007, we have a new function "IFERROR" designed to due with this particular situation. Let's see how we can benefit from it.

Reference

Eliminate those unfriendly error messages with Excel 2007’s IFERROR function

Step

The step is very simple, just replace your old "IF" function:

=IF(SOME_FUNCTION(),”Error Message”, SOME_FUNCTION())

with this one

=IFERROR(SOME_FUNCTION(),”Error Message”)

and you are good to go~

香港寛頻轉IP方法

(轉用香港話...)

前幾日見到呢個blog講點樣可以轉香港寬頻派俾我地既IP, 本來我都以為香港寬頻係用static IP無得轉. 點知仲有個咁簡單既方法, 真係道高一尺....

來原

香港寬頻轉IP方法

轉ip方法簡介

1. 按 "開始" -> "執行" -> "輸入 cmd"
2. 彈出 Command Prompt, 輸入 ipconfig/all
3. 記低 "Physical Address"
4. 到 "控制台" -> "網路連線" -> right-click "區域連線" -> "內容"
5. 0向 "區域連線 內容" 內, 按 "設定", 再選 "進階"
6. 在新彈出來既視窗中, 於左面 "內容" 選 "本地管理位址"
7. 在右面 "數值" 下填上之前記低既 "Physical Address"
8. 在填上既 "Physical Address" 入面, 隨意更改一個數字或字母
   (輸入的數值只可以是十六進制, 即是 0-9 或 A-F, 並且如原來數字是雙數, 就填雙數, 否則填單數)
9. 按 "確定", 檢查 IP 係咪改左, 完成

"Page is customized" in SPD 2007

Overview

I come across a situation that the "Edit Page" menu item is missing for certain page in a SharePoint Site. Later I found that it is caused by "Customized Page" created by SharePoint Designer (SPD).
If want to enable the "Edit Page" function of customized page, you can navigate to the page in SPD, right-click on it and select "Reset to Site Definition". However, this action will remove the customized page content.
Links below talk more about "customized page", "customized files" in WSS 3.0 & MOSS 2007

Reference

1. Understanding and Creating Customized and Uncustomized Files in Windows SharePoint Services 3.0
2. Reset a customized page to the site definition
3. 關於 SharePoint Designer 的利與弊

Delete users from a Site collection

Overview

A user might belongs to many user groups in a MOSS site collection. Below steps show how to remove users from all groups of a site collection.

Reference

Removing Selected Users in MOSS 2007/WSS v3

Steps

1. Select "Site Actions" -> "Site Settings"
2. Click "People and groups" under "Users and Permission"
3. In "People and Groups" page, click "All People"
4. Select the users you want to remove, then select "Actions" -> "Delete Users from Site collection"

Save MOSS sub sites as a template

Overview

Site templates are very useful when creating sites with similar structure & settings. However the link "Save site as template" is only available under Look and Feel at the Top Level of the site collection. So can I create site template of my sub site? Yes, and the way to do it is indeed quite simple.

Reference

MOSS 2007 - Save site as a template missing

Steps

1. Navigate to the site you want to create site template.
2. Copy its URL (e.g. "http://www.abc.com/demo/default.aspx")
3. Change the URL to "http://www.abc.com/demo/_layout/savetmpl.aspx", this will lead you to the "Create site template" page.

Silverlight + SharePoint integration (Silverlight Blueprint for SharePoint)

Overview

Microsoft provides a Blueprint for integrating Silverlight 2 applications with SharePoint sites. The Blueprint includes guides to configure SharePoint runtime environment for hosting Silverlight 2 application, as well as VS 2008 development environment for building Silverlight 2 wrapper webparts. Besides, 5 examples application are included in the Blueprint.

Reference

Silverlight Blueprint for SharePoint Beta 2

Steps

Below only briefly shows the steps required for the integration, for details please refer to Silverlight Blueprint Guidance.

Configure VS 2008 development environment

1. Install the Microsoft Silverlight 2 Beta 2 plug-in
2. Install VS 2008 SP1 and .NET Framework 3.5 SP1
3. Install Microsoft Silverlight 2 Tools Beta 2 for VS 2008
4. Install Windows SharePoint Services 3.0 Tools: VS 2008 Extensions

Configure SharePoint runtime environment

1. Install Service Pack 1 for your SharePoint (either WSS 3.0 or MOSS 2007)
2. Deploy System.Web.Silverlight.dll into Global Assembly Cache
3. Add a new MIME type for (.xap) file to the IIS Web Application hosting SharePoint Sites
4. Modify the web.config file of the IIS Web Application hosting SharePoint Sites to enable ASP.NET AJAX 1.0 and Silverlight 2 applications.