Rexiology::Work

Microsoft, Information Technologies...

Community

News

  • From Taiwan, living and working at Tokyo, Japan.




Recent Posts

Tags

Microsoft Sites

Other Sites

Blog pools

Bloggers

Archives

Site Info



Locations of visitors to this page




Logos & Chicklets


GeoURL


Rex's Certifications
Rex's Certifications


Creative Commons授權條款
本 著作 係採用
Creative Commons 授權條款



September 2007 - Posts

Compare Cells in Excel Sheets and mark differences with colors...

crosspost from http://blogs.msdn.com/rextang

Just got a task that there is a database table which got row add / delete / updates and wants to compare and mark out differences in Excel sheets.

Input:

  • Original Database Table named "orgTable"
  • Updated Database Table named "rstTable"
  • orgTable and rstTable are the same tables that have same schema

Output:

  • Excel file with sheets of
    1. Added rows
    2. Deleted rows
    3. Primary-key mapped orgTable rows with cell-color marked differences from rstTable
    4. Primary-key mapped rstTable rows with cell-color marked differences from orgTable

Steps:

  1. Prepare Table-View SQL Scripts
    1. Added rows view
      • select * from rstTable where rstTable.primaryKey not in (select orgTable.primaryKey from orgTable) order by rstTable.primaryKey
    2. Deleted rows view
      • select * from orgTable where orgTable.primaryKey not in (select rstTable.primaryKey from rstTable) order by orgTable.primaryKey
    3. Primary-key mapped orgTable rows view
      • select * from orgTable where orgTable.primaryKey in (select rstTable.primaryKey from rstTable) order by orgTable.primaryKey
    4. Primary-key mapped rstTable rows view
      • select * from rstTable where rstTable.primaryKey in (select orgTable.primaryKey from orgTable) order by rstTable.primaryKey
  2. Export Views to Excel Sheets
    1. Using SQL Server Data Export function to export those views to a single Excel file with multiple sheets.
  3. Using Excel VBA Macro to mark colors
    1. Open exported Excel file
    2. Open VBA Macro Editor
    3. Identify orgTable and rstTable mapped output sheets name (here defining "orgTableSheet" as Sheet3 object and "rstTableSheet" as Sheet4 object)
    4. Create the following CompareSheet function by adding a new Module:

        Sub comparesheet()
        '''''using rstTableSheet (Sheet4) as base for compare
        For Each MyCell In Sheet4.UsedRange
          '''''first reset the cell with white background
          MyCell.Interior.ColorIndex = 0
          Sheet3.Range(MyCell.Address).Interior.ColorIndex = 0
          '''''if cell is not empty...
          If Trim(MyCell.Value) <> "" Then
            '''''if cells in the same position of those 2 sheets got different values...
            If Trim(MyCell.Value) <> Trim(Sheet3.Range(MyCell.Address).Value) Then
              '''''paint both cells background to red
              MyCell.Interior.ColorIndex = 3
              Sheet3.Range(MyCell.Address).Interior.ColorIndex = 3
            End If
          End If
        Next
        End Sub
         

    5. Run the macro to mark colors in both sheets.

FYI.

Let me know if you got more efficient ways to do this task...

Technorati tags: , , , , ,
5GB for your Windows Live Hotmail account!!

crosspost from http://blogs.msdn.com/rextang

[via Walter Stiers]

Check your Windows Live Hotmail account! started from September your free hotmail account got 5GB mailbox to use, with paid Hotmail Plus user your got 10GB space!!!

more features here...

It would be better if those space can be used not just for mails... we now got SkiDrive too (500mb per account), can those mail space be shared with SkyDrive???

(Google now got shared storage program for you to manage your GoogleApp storages in one place, you can pay for a increase of storage of specified App...)

FYI.

Technorati tags: , , , , ,
When your local DRM / IRM credential is corrupted...

crosspost from http://blogs.msdn.com/rextang

Sometimes it happens, that when you try to open a IRMed email , the Outlook will tell you that your IRM credential is corrupted and please contact your administrator, blah blah blah.

on the situation, the fastest way is to just delete all your local DRM / IRM cache located at

c:\users\<your-account-name\appdata\local\microsoft\drm

So that the IRM function can connect to the server again to get the credential.

(remember to close your outlook first and reopen it later when you deleted IRM files...)

FYI

Technorati tags: , , ,

WebService and IIS Compression

crosspost from http://blogs.msdn.com/rextang

On the system we are building we do use Web Services with WSE3.0 for some Win / Web messaging. Just got a problem that while we are enabling IIS compression on the web server and added .asmx and other file extension to the compression section of MetaBase.xml under system32\inetserv directory, most of the file (.aspx, .html, .css, .axd, .js, etc) were compressed without problems. but the asmx file didn't be compressed.

In searching of the solution for this I got posts here states that when doing web service compression it's not only to have the server-side IIS compression features turned on and set the file extensions that wished to be compressed. there is also one more step needs to be done.

While tracing the web service request headers via Fiddler, we found that actually the request headers of asmx files didn't contain the "Accept-Encoding" header to tell IIS that the client accepts compression contents, therefore IIS won't compress the content and will send the original content back.

To enable sending the "Accept-Encoding" header in web service requests, after generating the proxy class, set the webservice proxy's "EnableDecompression"  property to "true" so that while doing the request, the web service proxy class will generate the header to tell IIS that it accepts compression.

The WSE 3.0 doc on WebServicesClientProtocol class properties states that EnableDecompression is inherited from HttpWebClientProtocol class, under the .NET 2.0 "EnableDecompression" property docs of this class stated that the default of the value is "true", but actually it's not like that while testing.

(although on the same property doc if you change to .NET framework 3.0 doc it did change the default value to be "false"!!)

Next time when you want to enable http compression on your web service request, not only set IIS server to compress dynamic files, but also remember to set proxy class to output "Accept-Encoding" header by setting the "EnableDecompression" property to "true"!

FYI

Posted: 2007/9/12 23:21 by rextangtw | with no comments
Filed under: ,
Scott Hanselman joined Microsoft...

crosspost from http://blogs.msdn.com/rextang

Am I the last one in the world that knowing this "News" ?

Been too busy recently to clear my un-read blog posts and this happened 2 months ago!!!

I am always a big fan of reading Scott's blog, met him in person once while TechEd 2005 at Orlando, and glad that he can join ScottGu's DevDiv team to make our dev tools better and better.

According to Scott's latest post, he should be on-board at Seattle now and 15+ hours later (here in Japan is 9/12 19:00 GMT+9 for now) he should appear at the the Crossroads Bellevue Mall Food Court for the Nerd dinner.

Welcome on-board, Scott!

(pic taken at TechEd 2005 Birds of Feathers session)

Technorati tags: ,
Posted: 2007/9/12 19:31 by rextangtw | with 1 comment(s)
Filed under:
Single Sign On (SSO) with Windows Live ID SDK...

crosspost from http://blogs.msdn.com/rextang

[via ijliao in Chinese] [via Slashdot] [via Angus Logan]

At 8/16 Angus announced the news on MSDN blogs, that Windows Live ID SDK with samples was released to developers  for developing SSO applications using Live ID. Documentation is also renewed.

according to ijliao's info, there are SSO solutions from major internet companies for now as follows:

For developers and architects, it should be good to develop a SSO wrapper for self use and includes those worldwide SSO providers for applications.

FYI.

Technorati tags: , , , ,

Office Outlook Connector and Windows Live packages...

crosspost from http://blogs.msdn.com/rextang

Just got the news today that the team just release new beta of Windows Live Services.

The new beta includes something new:

no need to mention those already existing Live Spaces, Live Search, etc.

the SkyDrive service is pretty useful to store public files to share with friends. as it's also integrated to Windows Live packages, the interoperability will also be a good point for content integration.

Also to bring online experience to offline, my long-waited Office Outlook Connector finally been available for download! It's always a pain that I can not POP3 my MSN Live Mail account's mailes to Outlook for a unified mail management experience but now finally it worked! this is also worth a try, and I found it very convenient to setup.

Take a look of those services and see if it fits your daily usage...

Technorati tags: , , , , ,