Excel Addin Support about code

When doing Add-ins for Microsoft Excel, Microsoft Word, Microsoft Outlook or other Office VBA, you often want to know what your client is running.

We add an about button to the ribbon or toolbar as follows:

Public Sub SystemVersion()

    MsgBox "Version " & g_sVersion & vbNewLine & vbNewLine & _
    ThisWorkbook.Path & "\" & ThisWorkbook.Name & vbNewLine & vbNewLine & _
    "Excel Version " & Application.Version & "." & Application.Build & vbNewLine & _
    "Running on " & Application.OperatingSystem & vbNewLine & vbNewLine & _
    "MacroView Business Technology" & vbNewLine & _
    "Solutions for SharePoint and Office" & vbNewLine & _
    "http://www.macroview.com.au", vbOKCancel, g_sSystemName
   
End Sub

As you can see, this shows the excel version, the build and the version of Windows as well as the location and the add-in version. It makes diagnosing what is wrong just that little bit easier.

Excel Chart Title Positioning in Excel 2010

Positioning the Chart Title by VBA code in Excel 2010.

There are two options:
xlChartElementPositionCustom
xlChartElementPositionAutomatic

So using code:
oChart.ChartTitle.Position = xlChartElementPositionAutomatic will make it centre at the top

One interesting chart add-in we did forced the user to enter the "Source" of the data which was placed in the chart title in small font. Just one of the things we have been asked to do for consistency in formatting Excel across an organization.

Setting up custom themes in Microsoft Word 2007

When creating new themes in Office 2007 be aware that by default these will be saved in the User templates location, not the Workgroups template location.
Workgroup Templates should be kept for your .dotx files or .dotm files.
Also, for consistency sake, you may want to push out theme changes to the User Templates location.

To find and change the user templates location:

• Open Microsoft Word 2007
• Click on the big button at the top left hand corner
• Click on the Word Options Button
• Select Advnaced at the left hand side and scroll down to the bottom
• Click on the File locations button
• Double click on user templates and change this location to your desired one.

As for editing the colors and fonts, that involves having a few colors to choose from.
Most of the colours in the templates are used for standardising charts, but you have to use Microsoft Word to set/change the template locations.
Once set, you can export the registry key so that can be rolled out if required.

Fun upgrading to Access 2010

After upgrading Microsoft Excel Add-ins and Applications to Excel 2007 and Excel 2010 with only a few known tweaks required, upgrading to access 2010 looked easy.

Little things that worked okay in development didn't work in the wild. Things like .filesearch and DAO 3.6 object libraries.

Suspect maybe installing the latest jet pack might be one way to avoid upgrade hassles.

The other main item that can get you is simple corruption which can disguise itself as a host of other issues.

Just heard from a client the other day that Microsoft Office 2010 training sources in Sydney had been booked out for the next two months….

Will wait for the stampede in November for Office Migrations!

Tom Bizannes
The Numbers man (Well I deal with Excel and Databases)…..

Three Steps to marketing your services on the web

Many people ask me about internet marketing and start talking about SEO.
This stands for search engine optimization which is about the web site appearing in the first page in google for some keywords.

The simple fact is that marketing for the web is similar to marketing anywhere else.

Trying to figure out my speech at the next Toastmaster's session in the Sydney CBD, if I were to break down the most important steps into a 5 to 7 minute speech.

There are three simple steps:
1) Attention
2) Interest
3) Response
So this makes the acronym AIR. This s a much simplified version of AIDA and some Internet Marketing Concepts.

So how do you make your web site relevant?
1) Get someone's attention! But how?
You have to know what the correct audience wants. Like the company I work for. We used to get 2,000 visitors per month and 2 phone calls a day until they de-googlised the web site. There's always someone who know better! A similar company was getting 40,000 visitors per month and only 2 calls a month.

Attention can be broken down into:
a) Knowing your audience
b) Finding out the pain points your service/product solves
c) Writing articles about that

I love the analogy of selling tyres. Most people don't care about their tyres unless rego or a long interstate trip is coming. Then they want to know where to get good tyres that aren't too expensive. So you need to target them by having articles about what to check when your rego is coming up and important things to check before your interstate road trip.

2) Make the article/web site interesting. Again this goes back to marketing basics. Make your article useful, not full of sales material.
You can again break this down into three points
a) Pick out the top three points and then write in simple conversational English as if you were talking to a teenager.
b) Provide feedback/reviews from customers
c) Use pictures and video if possible

3) Now you've hooked them, get a response.
a) Make sure simple things like your phone and email address are easy to get to.
b) Let them sign up for a newsletter or even just updates on services / products you offer.
c) Make it easy for them to ask you a question.

Back to the three principles: If you focus on Attention, Interest, and Response the web site will fall into place.
Write 1 to 3 articles, each discussing three points of interest, and sit back and let the phone ring and your email box to fill up.

Tom Bizannes dabbles in Internet Marketing for the challenge and has developed systems to quickly identify the steps you need to take to get to the front page in Google.

Recent Projects completed by the MacroView Sydney Office Consulting Team

The MacroView Services team is currently undertaking projects to upgrade clients based in Sydney to Microsoft Office 2010. As well as that, the team has developed applications that work with Office 2010 and SharePoint 2010. Recent projects include:

Microsoft Excel 2007 Chart and Table formatting for large professional services firm

We had previously done an Excel chart formatting add-in last year as part of a project to upgrade to Microsoft Office 2007. The client wanted to include nicely formatted tables as well as charts from Microsoft Excel in their Word Documents and PowerPoint Presentations. Based on their requirements, we made a customised ribbon which allowed the users select a chart or range of cells and click on the format button. After clicking on the format button a form popped up allowing them to select a standard size. They could also tick to output the result as an image for pasting straight into Microsoft Word or Microsoft PowerPoint. These images could be resized inside Microsoft Word or Microsoft PowerPoint without the fonts or other components losing their fidelity. We also helped them roll out a custom Microsoft Office Theme which was used to set the colours and the font used.

Automated daily Microsoft Excel Branch Reporting to a SharePoint Extranet

The client had a problem generating and emailing daily reports in Excel to each branch one by one. They wanted to automate the saving of these Excel Reports into SharePoint every day. Initially SQL Server Reporting Services 2008 was looked at, but it didn’t output the worksheet names nicely. We ended up using Microsoft SQL Server Integration Services to do the Microsoft Excel automation. There were a few tricks to make the pivot tables work nicely and now a Microsoft Excel report is saved each morning for each branch into their specific document library. The SharePoint Extranet was created from a script generated by our special Excel SharePoint Extranet creator Application which makes it easy to set up and maintain SharePoint Groups.

Major upgrade to Microsoft Office 2007

We upgraded hundreds of Microsoft Word templates and Microsoft Excel macros from Microsoft Office 2000 to 2007 for large engineering company. To do this efficiently, we used a Microsoft Excel application to find all the common functions, forms and toolbar buttons in the existing Microsoft Word templates. After analysing the results, the solution involved moving most of the code, toolbars and forms from the Microsoft Word templates to one main Microsoft Word Add-in. It is now much easier to maintain and update any of the Microsoft Word templates.

Excel Application for generating monthly branch reports to large SharePoint Extranet

The client previously had a mess of workbooks all linked for each branch for the last 3 years. Maintenance of these workbooks was a nightmare and updating them was extremely time consuming. A Microsoft Excel application was used to setup the scripts to create SharePoint Sites for over a hundred branches. This made creating the SharePoint groups and Sites just a matter of filling in the Excel Worksheet. Then a Microsoft Excel Application was created for generating monthly Excel reports and saving them to each branch’s SharePoint document library. The end result allowed a user to click a button in Excel once a month after downloading the latest extract.

For more information go to MacroView or call us on 02-9249-2700.

Copying Charts and Tables into PowerPoint

All fun and games.

We had to copy charts and tables as EMF files for printing quality into PowerPoint, but make it easy to jump back to the initial excel workbook.

Also was advised, some EMF files lost their quality. Have some code to fix them but on looking, the problem was the aspect ratio. If it was 74% to 75% then the quality would be slightly less - still better than a straight image.

Now just need to add the code to buttons in Excel and PowerPoint so that the client can create as many high quality presentations as possible.


Tom Bizannes works for a Microsoft Gold Partner specializing in Office and SharePoint

Excel Chart Types

Here's some simple code to get the actual chart type from Excel.
We are using this for our customized Chart Formatter using Excel Themes to color and doing funky things like making simple data labels for lines charts with the series name in the same color as the line.

'-----------------------------------------------------------
'
' Code created by Tom Bizannes - Sydney, Australia
'
'-----------------------------------------------------------
Public Function ChartType(lChartType As Long) As String
  Select Case lChartType
  Case 4, 63, 64, 65, 66, 67, -4101 'line charts
    ChartType = "line"
  Case 1, 76, 77, 78, 79, -4098, -4111 ' area charts
    ChartType = "area"
  Case 57, 58, 59, 60, 61, 62, 102, 103, 104, 109, 110, 111
    ChartType = "bar"
  Case 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 92, 93, 94, 95, 96, 97, 98, -4100, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112 ' column charts
    ChartType = "column"
  Case 5, 68, 69, 70, 71, -4102 ' pie charts
    ChartType = "pie"
  Case -4169, 72, 73, 74, 75
    ChartType = "scatter"
  Case 15, 87
    ChartType = "bubble"
  Case -4120, 80
    ChartType = "doughnut"
  Case -4151, 81, 82
    ChartType = "radar"
  Case 88, 89, 90, 91
    ChartType = "stock"
  Case 83, 84, 85, 86
    ChartType = "surface"
  Case Else
    MsgBox "This application doesn't cater for this type of chart.", vbInformation, "StandardiseChart"
    Exit Function
  End Select
End Function

Tom Bizannes - Sydney - Australia
If you want to see the Excel Chart Formatter for High Quality Presentations or making it easy for everyone in your company to make good looking charts for word or powerpoint then goto MacroView and ask.

Recent Projects I’ve done

Microsoft Excel 2007 Chart and Table formatting for large professional services firm.
We had previously done an Excel Chart Formatting Add-in last year as part of a project to upgrade to Microsoft Office 2007. The client wanted to include nicely formatted tables as well as charts from Microsoft Excel into their Word Documents and PowerPoint Presentations.
As a result of their requirements, we made a customized ribbon which allowed the users select a chart or range of cells and click on the format button. After clicking on the format button a form popped up allowing them to select a standard size. They could also tick to output the result as an image for pasting straight into Microsoft Word or Microsoft PowerPoint. These images could be resized inside Microsoft Word or Microsoft PowerPoint without the fonts or other components losing their fidelity. We also helped them roll out a custom Microsoft Office Theme which was used to set the colours and the font used.

 
Automated daily Microsoft Excel Branch Reporting to a SharePoint Extranet.
The problem the client had was generating and emailing daily reports in Excel to each branch one by one. They wanted to automate the saving of these Excel Reports into SharePoint daily.

Initially SQL Server Reporting Services 2008 was looked at, but it didn't output the worksheet names nicely. We ended up using Microsoft Sql Server Integration Services to do the Microsoft Excel automation. There were a few tricks to make the pivot tables work nicely and now a Microsoft Excel report is saved each morning for each branch into their specific document library. The SharePoint Extranet was created from a script generated by our special Excel SharePoint Extranet creator Application. This makes it easy to set up SharePoint Groups that are easy to maintain.


 
Excel Application for generating monthly branch reports to large SharePoint Extranet.
The client previously had a mess of workbooks all linked for each branch for the last 3 years. Maintenance of these workbooks was a nightmare and updating them was extremely time consuming. A Microsoft Excel application was used to setup the scripts to create SharePoint Sites for over a hundred branches. This made creating the SharePoint groups and Sites just a matter of filling in the Excel Worksheet. Then a Microsoft Excel Application was created for generating monthly Excel reports and saving them to each branch's SharePoint document library. The end result allowed a user to click a button in Excel once a month after downloading the latest extract.
 
Major upgrade to Microsoft Office 2007
We upgraded Hundreds of Microsoft Word Templates and Microsoft Excel Macros from Microsoft Office 2000 to 2007 for large engineering company. To do this efficiently we used a Microsoft Excel application to find all the common functions, forms and toolbar buttons in the existing Microsoft Word Templates. After analysing the results, the solution involved moving most of the code, toolbars and forms from the Microsoft Word Templates to one main Microsoft Word Add-in. It is now much easier to maintain and update any of the Microsoft Word Templates.


 

Excel 2010 – Business Intelligence for the end user

This is totally awesome…


Code named Gemini, PowerPivot is an addin for Excel 2010 which gives you BI out of the box.

There's still the need for a decent data warehouse, so Database guys like me will still be required.

Given the nature of most data out there, this is both great and dangerous at the same time.

What's really compelling is the ability to suck in data from the web to compare your current data with.


There's also the PowerPivot server option in SharePoint 2010.
Note: You need to install SharePoint 2010 and not configure it and then install Sql2008R2 to let the magic begin.


Most people will only install the 32 bit version of Excel 2010 - the 64 bit is for those business analysts using PowerPivot to the extreme..as long as they have more than 4G of RAM on their desktop.

Regards,

Tom Bizannes
http://www.macroview.com.au