The following features cannot be saved in macro-free workbooks

The following features cannot be saved in macro-free workbooks

VB Project

To save a file with these features, click no, and then choose a macro -
enabled file type in the file type list.

To continue saving as a macro freee workbook, click yes.

That was the error I was getting today when I wanted to write a macro in Excel 2007.

In order to make an Excel 2007 workbook macro enabled, you need to save the workbook as .xlsm rather than .xlsx That solves the problem of the macros not being enabled.

PowerPoint Show (*.pps) file copies as a PowerPoint (.ppt) file when downloaded from the internet

Today we had a probelm on our website, where when you try to download a PowerPoint Show (.pps) file, the file was downloading as a PowerPoint Presentation (.ppt) file instead of a .pps file.

I have done some research on google and found the following solution to my problem.

• In IIS Administrative Console snap-in, right-click the specific Web server where the .pps file is located, and then click Properties.
• Click the HTTP Headers tab.
• Click on Mime Types button in IIS 6.0
• Click the File Types button in older versions of IIS.
• Click New Type.
• In the Extension box, type .pps, and then in the Content type (MIME) box, type application/vnd.ms-pps.
• Click OK, and then restart IIS.

To know more click on the link from Microsoft

Checksum Vs. Hashbytes

What is Checksum?
Checksum is a function available in sql server 2005 and sql server 2008. It is intended to build a hash index based on an expression or column list.

Determining if two rows or expressions are equal can be a difficult and resource intensive process. This can be the case where the update was conditional based on all of the columns being equal or not for a specific row. Without checksums it is a long process of using innerjoins to identify the exact row to update in the update statements.

When is Checksum used?
When you need to compare the unique characteristics of an expression, columns or a table consider using the Checksum function.

When you have to update a row where many columns are compared to determine if the data is unique, use CHECKSUM function to build a unique value using checksum function and then compare the CHECKSUM values.



Example:

Hashbytes

What is hashbytes function?
HashBytes function was introduced in SQL server 2005 to simplify creating hashes in the database. It can convert values to MD2, MD4, MD5, SHA, or SHA1 formats.

When can you use a hash function?
Hash functions are a useful option to improve the efficiency of particular queries on large volumes of data. For example, on long strings of text, you can build a hash-index to perform efficient lookups or to speed up aggregate operations.


Differences between checksum and Hashbytes

Checksum	Hashbytes
Faster but can produce lot of duplicate values	Slower but efficient than checksum
Returns an int value	Returns a varbinary(8000)
Microsoft does NOT recommend using CHECKSUM for change detection purposes	Use Hashbytes for change detection purposes

Problem problems with Powerpoint

I had problems printing powerpoint slides this morning and here is an excerpt from an article on pptfaq.

Workaround

If you're willing to accept a slightly smaller slide image, you can print Notes Pages instead of Slides. Modify the Notes Master in your presentation:

• Choose File, Slide Setup and set Notes pages to print in the same orientation as your slide pages
• Choose View, Master, Notes Master
• Select and delete the notes text and any other text placeholders you don't want to appear on your slide printouts.
• Select and scale the slide image on the notes master to a larger size. NOT full page, but somewhat smaller. You'll need to find out what the trailing margin on your printer is and make the margins on both sides of your notes master at least that large.
• Print a trial notes page from one of your slides. It will probably be off center. Go back to the notes master and move the slide image accordingly. It'll probably take several trial printouts to get right, but once you've got it nailed down, you should be able to get printouts of just your slides, properly centered on your inkjet printouts.
  
• Once you've got it nailed, right click the slide image, choose Format and record the size and position of the slide image so you can use the same info in your other presentations.

Source: http://www.pptfaq.com/FAQ00097.htm

Password protect a workbook in Excel 2007

If you want to password protect your workbook in Excel 2007 the following are the steps that need to be followed.

• Click on the Office button on the top left hand corner of the workbook
• click on prepare
• choose restrict permission
• choose a pasword
• you may be asked to reenter the password

That's it your workbook is password protected.

Displaying powerpoint files on a website

I had trouble displaying powerpoint files on the website that I was working on. The problem was the link to the powerpoint file was a powerpoint 2007 show file (.ppsx). But when you clikc on the hyperlink, it was downloading with a .ppt extension

I have done some googling to resolve this problem and found that in order for links to PPTX/PPSX files to work, the two conditions must be met:

• The user must have PowerPoint 2007, the 2007 Viewer or an older version of PowerPoint with the compatibility pack installed so that they can open PPTX/PPSX files in the first place
• The web server configuration on your site must have the correct mediatype/MIME settings to serve PPTX/PPSX files as PowerPoint 2007 presentations.

The above is an extract from the article on pptfaq called Control how the browser opens powerpoint files

Disable accessing a hidden sheet in Excel workbook

I had a requirement today to not display the hidden worksheets in an excel workbook when you try to unhide sheets. Here are the steps I have taken to achieve this.

• Open the VBE (alt-f11)
• Go to the sheet that you want to disable in the unhide option
• View properties window
• Change Visible property to 2 -XlSheetVeryHidden.
• Save and close

Test the unhide option

Outlook meeting request response myth busted

In outlook when a meeting request is received from the organiser, I was under the impression that when I accept the request and choose do not sent response, then the meeting request at the organisers end gets updated that the request has been accepted but the organiser will not be sent an email. This is a myth. I am sure most of us are under this impression.

When you choose do not send a response, there is no way outlook updates the request at the organisers end. So if you want to let the organiser know that you have accepted then you need to send the reponse when you have accepted.

The credit for this learning today goes to Vanessa Taylor -- our Executive Assistant to the CEO.

Convert date to text in excel

If you have a date in a cell, when you try to use it as it is for example in concatenation, the date gets converted to a number and does not retain the date format.

In order to retain the date format and still be used as text -- the formula that can be used is TExt(datecell, "dd-mm-yyyy")

Reflections from CLoud Camp -- Auckland

I have attended a cloud camp that was held in Auckland on Feb 26th 2010. Here are some points sent by Ben the Cloud camp organiser.

I posted a roundup here giving my thoughts on the event. Thanks to all our sponsors who made the event possible - Gen-i, enStratus, Microsoft, salesforce, vmware, Intergen, the University of Auckland and Zendesk. Thanks also to Anne Bilek for doing a great job of helping to organise things on the ground.

Paul from Gen-i wrote an interesting post with his reflections on the event - you can read it here.

Similarly Ryan from Intergen wrote an interesting perspective here.

Finally Nigel from Microsoft filmed the Lightning Talk given by Darryl Burling - also well worth a look. click here to watch the vid.

Have you heard of Worldcard?

Worldcard is a bizcard recognition solution for iPhones. This uses a built-in camera in smartphone to capture bizcard images and has a powerful recognition function to turn business card information into contacts right away.

To know more about it.........

How to become an exceptional DBA?

Today I have attended the webinar by Brad McGehee titled 'How to become an Exceptional DBA' which was an inspirational talk about why and how you need to be exceptional. Following are the learnings from this webinar.

Characteristics of an Exceptional DBA:
Enjoy Technology, challenges, problem solving, eye for detail and embrace change, trustworthy, accept responsibility, maintain professionalism, dependable, manage time well, be patient, good listener and most of all self confident.


Honing your skills

• Having a 4 year degree differentiates you from the crowd and this degree need not be in IT or CS.
• Boosting your technical skills is a self taught process by attending seminars, conferences, code camps, online training etc.
• Mastering related technical skills like virtualisation, operating system, hardware, clustering
• Practical experience is the key to be an exceptional DBA.
• Mastering soft skills like people skills, teamwork skills, project management, leadership skills, time management, writing skills, speaking skills, knowledge of legal responsibilities. These can be done by volunteering.

Specialising as a DBA:

Choose one or more of the following:

• DBA Adminstrator
• DBA Developer
• DBA High Availability/Disaster Recovery
• DBA Database Designer/Architect
• DBA BI SSAS specialist
• DBA BI SSIS specialist
• DBA BI SSRS specialist
• DBA Trainer
• DBA Consultant

• Professional certification helps you differentiate from other DBAs
• Volunteer and participate in the SQL SERVER community. this is mainly to help yourself as participating develops your soft skills, make new contacts, increases your bdepth and breadth of knowledge, building your network etc.

Manage your internal brand

• Understand business goals of your company, and focus on how to help achieve them.
• Be a leader and take the intitiative.
• Accept responsibilty for your actions
• Have a "Get it done" attitude
• Don't abuse your power
• Actively participate and contribute in meetings
• Help others be successful
• Most importantly avoid office politics
• Be proactive by keeping all the stakeholders in the loop

Manage your online brand

• Seperate professional and personal online presence
• Don't post anything negative
• Online conducta and content should be professional, and

Last but not the least, you should take control of your career by deciding what you want from your career, set realsitic, short term goals in order to reach major milestones in your career plan. Most importantly follow through your goals.

Connecting to SQL Server via Windows PowerShell with SQL Server authentication

Here is a tip from MSSQLTIPS that shows you how to connect to powershell with sql server authentication.

Today I have attended the free webinar on T-SQL for SLQ Server from Pragmatic Works. This was a fantastic session by Patrick LeBlanc. Even though I had some experience in T-SQL I have learnt a few new things mostly the new features of SQL SERVER 2008. The recorded webinar can be viewed from the resources page and click on View Recording at the relevant webinar.

Interesting things I have learnt from this webinar are as follows:

• If you don't want to type the table names you can drag a table into the query window from the object explorer.
• If you don't want to type the field names you can expand the table and drag the columns folder into the query window from the object explorer and the list of fields of the table automatically appear in the query window.
  
  Set Identity_Insert On or OFF
  
• You can update an ID column by setting the above variable on for a specific column. But remember to set the value to Off once you have updated the ID column as this works for one table at a time.
  Example:
  SET Identity_Insert tablename ON
  Insert INTO (IDcolumn, field1, field2)
  Values(6,ssss,zzzz)
  SET Identity_Insert tablename OFF
  
  
• A very good description (illustration) of rather differnces between, Inner join, left outer join and right outer join.
• The use of output statement
• Grouping Sets feature of SQL SERVER 2008
  

Hope you also find this resource useful.

Do your backups for SQL server still exist?

Now that you have confirgured your backups on SQLSERVER, the backups themselves seem to be working, but are the files still where you think they are? How do you know they have not been moved, or worse, deleted to free up some space on your server? And when would you find out that the files were no longer there? If you are looking for the file to do a restore, and cannot find it, then it is too late.

Here is an tip from MSsqltips that shows you a simple way to check through the backup history to find the last full backup for each database and also to check that the file still exists where it is supposed to exist. Read More .............

Cloud computing Costs and ROI

I came across this video on you tube that shows how to calculate the costs and ROI for cloud computing.

What is Cloud Computing?

Cloud computing has taken the IT world by storm, quickly making its way up the list of technology buzz words - but what does the term really mean? And how will cloud computing change the way you manage and organize your digital information in the future? (Relax News)

Here is the link to an article published by The Independent on What is Cloud Computing

Here is a video published on You Tube on What is Cloud Computing?

Cloudcamp in Auckland

Today I have registered for the CLoudcamp event that is happening in Auckland. If you also want to register for the Auckland cloudcamp please go to this link.

Cloud camps are in the tradition of unconferences where anyone can put themselves forward to present a session.

CloudCamp is an unconference where early adopters of Cloud Computing technologies exchange ideas. With the rapid change occurring in the industry, we need a place where we can meet to share our experiences, challenges and solutions. At CloudCamp, you are encouraged to share your thoughts in several open discussions, as we strive for the advancement of Cloud Computing. End users, IT professionals and vendors are all encouraged to participate.

Adding a calculated field in pivot table

The following are the steps to add a calculated field inside a pivot table in Excel

• Click on a cell inside a pivot table.
• Go the PivotTable Tools -- Options -- Click on Formula --Calculated Field
• Add the formula for the calculated field
• Click ok. This will add the calculated field to the Pivot table as well.

Upgrade plan for SQL SERVER 2008

We have to upgrade out sql server 2005 to sql server 2008 in the coming months. Here is a comprehensive article that I found on MSSQLTIPS that could be used when you plan an upgrade. Read More