Category: Microsoft

Categories
Microsoft Office

Google Calendar Sync – Auto Run

A nice quick hint.

I kept forgetting to start Google Calendar Sync when I started Outlook. I don’t always start Outlook when I start my laptop and I’m not always online when I run Outlook so puting it in my Startup folder is not really an option.
My solution, a macro that runs when Outlook starts that gives me the option of running Google Calendar Sync if I feel it’s appropriate.

Private Sub Application_Startup()
Dim response As Integer
Dim RetVal
op
response = MsgBox(prompt:=”Do you want to run Google Calendar Sync?”, buttons:=vbYesNo)

If response = vbYes Then
RetVal = Shell(“C:\Program Files\Google\Google Calendar Sync\GoogleCalendarSync.exe”, vbMinimizedNoFocus)
End If

End Sub

NOTE: WordPress seems to “prettyfy” the quotes so when copying the code above it is better to delete and retype the quotation marks in your code editor of choice.

Categories
Microsoft Office

How Annoying is the Microsoft At Home RSS Feed?

Microsoft At Home
I’m using Microsoft Outlook 2007 and I think it’s a great piece of software.
It has a built in RSS feed reader. Typically I use Google Reader but as that can’t handle authenticated feeds I’m using Outlook for all of my work related Sharepoint feeds.
In order to introduce people to RSS, Microsoft have included two “standard” RSS feeds: “Microsoft At Home” and “Microsoft At Work”.
The only problem is, I’m not really interested in reading these feeds.

Continue reading “How Annoying is the Microsoft At Home RSS Feed?”
Categories
Maxima Information Group Microsoft Office Work

MS Excel – Convert Percentage To Number

I have spent most of today throwing data around in MS Excel.

One of the things that I have had to do is to convert percentages into real numbers.

In excel percentages are stored as fractions, for example 50% is stored as 0.5, 75% as 0.75 etc.

I am trying to chart these figures in Business Objects Xcelsius but the y-axis labels are just showing 0 to 0 hence why i need to convert my numbers.

I have just found a really easy way to do this.

  • In the target cells, enter the value 100 in each cell.
  • Copy the source cells
  • Select “Paste Special” using the options “Values” and “Multiply”

This takes the source value 0.75 multiplies it by 100 and stores the result 75 !

I have used pase special – values often but I have never seen the need for the multiply option until now !

You may want to check out some of my other Excel related posts here.

Categories
Maxima Information Group Microsoft Work

Latest ETL Tool – MS Excel

I have spent all day in MS Excel.
I receive excel spreadsheets in a “fairly” fixed format.
I need to get this data into Business Objects Xcelsius via another spreadsheet that stores historical records and keeps the data in a format optimised for display.
“Normally” I would be using Data integrator (DI) to move the data from the source, store the data in a database table or two, use query as a web service (QAAWS) or Live Office to pull the data from the database directly into Xcelsius.
Here we do not have access to any of these luxuries.
I have written a translation spreadsheet that I can point to a specific source and it will re-shape the data so that it can fit my target spreadsheet. Then it’s just a case of copy and then paste values….
To make this process easier I am using the “INDIRECT” function in excel.
=INDIRECT("[" & $H$1 & "]Data!" & AU$1 & $C17) * 100
Here the current cell will grab the vaue from the workbook named in H1, go to the “Data” spreadsheet then grab the row and column attributes from AU1 and C17 then multiply the number by 100.
This means that next month I just need to change the name of my source spreadsheet and it will all just work. If a row or column “moves” in the source, I just update the metadata (AU1 or C17) in this case.
It takes a long time to set up and to check but next month it should be a breeze.

Categories
Business Objects Maxima Information Group Microsoft Work XI 3.0

XI 3.0 – New Features – Publisher

One of the major features being “re-introduced” to the XI product family is Publishing.

This gives you the ability to write a single report, that runs a single query but sends a different “version” of the report to each user.

This is only scraping the surface of the full Publisher functionality, lets have a look at houw you can create your first user targeted publications.

Continue reading “XI 3.0 – New Features – Publisher”
Categories
Microsoft Office Work

Microsoft Word 2007 Keyboard Shortcuts

I use Microsoft Office 2008 on my work laptop. Whilst I am no touch typist; I wish I could take the productivity hit whilst I learn, I do prefer to use the keyboard for most functions, growling when I have to resort to using the mouse.

I was torn by the ribbon interface; for 80% of the things that i want to do i can find it very quickly, the remaining 20% often required a trip to help to find it. Another thing that I hated was that I “had” to use the mouse to access the ribbon features.

Then I pressed the “Alt” key !!!!!!!

This pops up an overlay on top of the ribbon interface that shows you which key to press to access http://pharmacy-no-rx.net each feature. Or should I say, EVERY feature. If you can see it on the ribbon, you can access it via a keystroke. I’m beginning to like this user interface.

The remaining bugbear is that I always use styles, nost commonly the heading styles and normal. In the previous version of Word I always assigned my own keystroke shortcuts to access them, but I was always over riding exisiing keystrokes and features.

In Word 2007 I have found that there are system assigned keystrokes:

Ctrl + Alt + 1 = Heading 1

Ctrl + Alt + 2 = Heading 2

etc

Strl + Shift + N = Normal

Productive typing here I come !

Categories
Maxima Information Group Microsoft SQLServer Work

Concatenate Two Fields And Get A Smaller Answer !?!?

Had a “strange” problem last week that really stumped me for longer than it should have.

My colleague had run a query to list some data and was getting approximately 200,000 rows back. We were investigating why a unique constraint was being violated so I took his query and did a count distinct to try and find the duplicates. I got 30,000 distinct values.

This really confused me, I was expecting only a handfull of duplicates, if any. Then I noticed that the first query already WAS a distinct list !

select distinct col1_id, col2_id from tablea

select count(distinct col1_id + ‘-‘ + col2_id) from tablea

I concatenated the fields together so I am counting a single field, I included the separator so that I could differentiate 1-11 from 11-1.

See if you can spot what the problem is before you read on.

Continue reading “Concatenate Two Fields And Get A Smaller Answer !?!?”
Categories
Maxima Information Group Microsoft Work

Microsoft Excel 2007 Annoyances

MS Office Logo

I have had office 2007 on my laptop for ages now. Like everyone I am still using the “old” file formats until the rest of the world catches up. On the whole I have been impressed by the ribbon interface.

I have just found my first major annoyance. Freeze Panes. You now have three options: Freeze Panes, Freeze Top Row and Freeze First Column. Freeze Panes works exactly the same as it did in previous versions, no problem there. I can just continue to use that option.

The problem is with Freeze Top Row; this option freezes the top row that is shown on the screen not the top row of the sheet. I know that’s what it does, it’s just that, it’s not what I expect it to do, and I can’t stop myself from clicking it !