Excel file size much larger than the contents

I recently had a weird problem with an Excel doc that jumped in size after making some changes and saving a new revision. It was a kit list/pricing spreadsheet I'd been working on for a while so couldn't pin down the exact action that caused it to increase in size.

In this instance I was using Excel 2007 and saving the doc as an Excel 97-2003 Workbook.

After some Googling I tried the following fixes, the last of which solved my problem:

Inspect the document
Prepare > Inspect Document > Inspect
This shows a list of hidden content including rows, columns and worksheets and if it find any, allows you to delete them.

Force everything on a sheet to be deleted
Press ctrl-a, right click anywhere in the selected worksheet and click 'Delete'
This was overkill for what I was trying to do as I needed to keep the content I could actually see, but it was really useful to go through a copy of my dodgy spreadsheet and clear every tab and once done the file was still huge, so there was still work to do...

Manually delete components in the file
Warning, this may destroy your data so make sure you have a backup of the file first!!!
Save the spreadsheet as an .xslx file. Rename the extension to .zip. Open the file in WinZIP. Look for an xl\externalLinks folder and delete any files in this. Close WinZIP and rename file back to .xlsx, open in Excel and allow Excel to repair the file.
This is great as it shows you exactly where bulky data is stored in the file. In my case WinZIP showed the xl\externalLinks folder contained some bulky data. I dropped one of the .XML files into Notepad and could immediately see that it the entire contents of a Cisco pricelist I'd pasted some content from! I would have only pasted a couple lines from this other spreadsheet so Excel must have brought the whole lot across for an unknown reason but decided not to acknowledge this anywhere. Once I delete the files in the xl\externalLinks folder and saved it as per my instructions above my file was back to a sensible size.

Changing the IP address in CUCM 7.02

Follow the steps below to change the IP address of the Cisco Unified Call Manager Publisher server.


Environment Overview


CUCM version 7.02 running as a virtual machine on VMware Server 2.0. The virtual machine was moved to a different VMware server and subnet.



1) On the new VMware host server in VMware Infrastructure Web Access, go to the settings for the CUCM guest and change the network adapter connection to vmnet1 (hostonly)



2) On the VMware host server change the IP address and subnet of the vmnet1 network adapter to an IP address in the same subnet as the existing IP address of the CUCM. You don't need to set a default gateway on the network adapter.



3) Log into the CUCM via the CLI (I used the VMware Console to access the CLI) and change the default gateway to the new default gateway you want to use using the following command:

set network gateway gateway_ipaddress

4) Log on to Cisco Unified CM Administration and under System > Server > your CUCM server, change the IP address to your new IP address and click save.

5) Back on the CLI change the IP address using the following command

set network ip eth0 ipaddress subnetmask

The CUCM should now reboot.




6) Back on the the VMware host server in WMware Infrastructure Web Access, go to the settings for the CUCM guest and change the network connection back to vmnet0 (bridged).




7) Once the CUCM has rebooted you should now be able to access it via the new IP address. You can check the IP address has change via the CLI using the following command:


show tech network hosts

My Other Lab Location

While sporadically building my home lab setup (which has been mainly for my own personal development) I've also been adding to a lab I have setup at my workplace. I've had this lab in place since around 2007 for testing LCS 2005, OCS 2007 and OCS 2007 R2 integration with Mitel, Avaya and Cisco PBXs.

Summary of what the lab has done/can do, by PBX manufacturer:


Mitel 3300

OCS 2007 and OCS 2007 R2 Remote Call Control via Mitel Live Business Gateway (Linux and Windows versions)

Exchange 2007 UM for Mitel handsets homed on the Mitel 3300


Cisco Unified Call Manager (CUCM)

CUCIMOC integration with MOC on OCS 2007 (CUCM 7.0.2)
Interesting blog entries for CUCIMOC:
http://blog.misthos.com/2009/07/cisco-cucimoc.html
http://blog.misthos.com/2009/08/cisco-cucimoc-features.html

Exchange 2007 UM for Cisco handsets homed on the Cisco CUCM (CUCM 6.1.5)
Microsoft guide to configuring CUCM for Exchange 2007 UM:
http://www.microsoft.com/downloads/details.aspx?FamilyID=68B43D3C-7C84-4C2F-BFD7-98754970D70E&displaylang=en&displaylang=en


Avaya Communications Manager

OCS 2007 Remote Call Control via Avaya AES server
Avaya Softphone integration with MOC on OCS 2007

OCS 2007 R2 Documentation and Setup

Since the last post I have finished off installing OCS 2007 R2 and Exchange 2007 on the home lab setup and have been working on a lab environment elsewhere (see next post).

There isn't much point in going into too much detail on the OCS install as it is relatively straightforward AS LONG AS YOU FOLLOW THE DOCUMENTATION!

I've setup a number of LCS, OCS07 and OCS07 R2 implementations in the past and the one thing that is critical is to follow the step by step docs from Microsoft if you want a functional setup. If you attempt a Next, Next, Finish approach then it will only take up more time in the future as you have to fiddle around trying to fix things.

Anyway, preaching over, click this link for the Microsoft documentation pack for Office Communications Server 2007 R2, updated Dec 2009.

http://www.microsoft.com/downloads/details.aspx?familyid=E9F86F96-AA09-4DCA-9088-F64B4F01C703&displaylang=en

Communications_Server_2007_R2_Documentation.zip contains the whole doc pack in one zip file.

Troubleshooting a dodgy PSU and finally building the servers

In between ordering the Crucial 2x2Gb mem and it arriving my PSU in the box I'm planning to use as the VM host decided to start acting up - while playing Bioshock the PC powered off completely then powered back on a few seconds later. It continued to run for about a minute them powered off and on again. This continued, with the actual amount of time the PC was running reducing each time to the point where it would power off on the XP loading screen.

My initial thought was that it could be memory related so I swapped this out but the issue continued. One thing that was notable was the fact that the issue would go away if the PC was powered off for 5 minutes +, which made me think it could be something overheating - I checked the CPU and GPU coolers and these were fine. The 'something overheating' analysis also fitted in with the PC powering off after playing Bioshock for a while as this game is pretty demanding on the PC.

In the end I narrowed it down to the PSU - I took out the graphics card and used the onboard VGA to rebuild the PC on a spare HDD and it powered off and on again during the XP setup. I still had the old factory fitted Dell PSU so swapped out the OCZ 600W PSU for this and started XP installing again and it didn't power off again - bingo! Just got to ship the PSU back to Ebuyer for an RMA now.

Back to the original task...

The 2x2Gb mem arrived and I made the schoolboy error of forgetting the 4Gb limit on XP 32bit, so I rebuilt the PC with XP 64bit and it happily recognised the 4Gb and made all of it available. I also installed a second HDD I had spare with the aim of using this as the VM drive. I installed VMware Server 2.0 and built a DC with Windows Server 2008 64bit. The lab at this point consisted of a single server with the following spec:

• Windows Server 2008 64bit SP1


• Active Directory


• DNS (AD integrated)


• 1Gb RAM initially, reduced to 512Mb to free up mem for the other servers


• Single CPU core

I built the DC in the normal way - added the AD role then ran DCPROMO.

I decided that all VMs should only use a single core so to leave some processing power for the OS so the PC is still usable - may be flawed logic so I need to check this out in more detail.

Once the DC was fully updated and activated I started another Server 2008 64bit build - this next VM was to be Exchange 2007. This time however, after applying updates but before setting a static IP and joining to the domain, I shut down the VM and took a copy of the folder containing all the VM files. My plan was to use sysprep to reset the SID, etc on the VM to make provisioning of VMs quicker - I'll talk about this in the next post.

Home Lab Setup

I've been building OCS labs on and off for the past few years since I left IT and moved to presales (the Dark side). The infrastructure for this is virtualised on a number of servers in the pre-stage area in our offices. It is used for stuff like engineer training and customer PoCs where OCS is being integrated with another VoIP manufacturer's systems. As the driver for each lab build is usually testing for a specific customer project I have been finding that I'm not getting as much time as I want to check out all the new OCS features, especially since R2 has been released.

Soo, I've decided to build my own lab at home...

Home Lab Infrastructure Build

The brief for the lab is pretty straight forward:

• Be able to run 32- and 64-bit guest OS (OCS 2007 R2 - all roles, Exchange 2007 and 2010)
• Low power consumption so I can have it running 24/7 and not be killing too many polar bears
• With the above in mind, as many spindles as possible per host machine
• As much RAM as poss per host machine
• As cheap as possible

I'm starting the lab with my existing Dell E520 which I use for gaming and web browsing and at this stage I want to continue to use it as my home desktop PC. This means the decision on virtulisation software is already made:

• Windows Server 2008 Hyper-V - Nope, would mean having to run Server 2008 as the host OS - not ideal for gaming
• VMware ESXi - Nope, can't use the box for anything else
• VMware Server - Yep, can continue to use the box for gaming and stuff

The Dell E520 has the following spec at the mo:

• Intel Core 2 1.86GHz 6300XFX GeForce 9800 GT (yep, not really critical for a virtual machine host)
• OCZ StealthXStream 600W PSU (had to get this for the graphics card)
• Hitachi DeskStar 7K160 7,200 RPM 160Gb SATA HDD

I'm planning to upgrade the box to the max 8Gb RAM and chuck in 2 more SATA HDDs that I have kicking around from previous upgrades to give me more spindles to spread the VMs across. For the mem upgrade I've already ordered the following from eBuyer:

4Gb (2x2Gb) Crucial DDR2 800MHz PC-6400 Ballistix CL4 2.0V http://www.ebuyer.com/product/143844

This will be enough to get me started on building the guest machines and I'll order the second set of 4Gb RAM when I've hit capacity - Exchange UM is going to be the biggest killer on mem I reckon.

Once this is up and running and I have a few VMs live I plan to benchmark the power consumption with an energy monitor similar to this: http://www.maplin.co.uk/Module.aspx?moduleno=38343

Based on the results of this I can work out if it will be more cost effective to rip out the 600W PSU and GeForce 9800 GT and put this into a new gaming machine and run the E520 purely as a virtualisation platform.