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

How much do SEO marketing guys charge?

Interesting enough got an email for Microsoft Gold Partners for an SEO mob to help googlisation.

There are so many simple ways to enhance your googlisation.
The wrong ways mean alot of work and little business.
The right ways are easier and add to your profits.

These SEO guys were charging between $1,400 to $3,000:
The $1,400 version just gave you a report each month for 6 months.
The $3,000 one also included 10 hours of consultation.

And to think that there is a free download for .net guys that runs this in seconds on a site!

It is easy to see why everyone is worried about SEO!
The internet is just another marketing mechanism and marketing/advertising requires a few simple principles in order to be EFFECTIVE.

Yes, you can drive 50,000 visitors a month and only get 3 to 5 phones calls or emails.
One can also generate 2,000 visitors a month and get 25 to 50 phone calls or emails.

We know because we can generate the later.

The maths is really simple…Less work and 10 times the business walking in the door.

Q: Is it easy to learn SEO tricks?
A: This keeps changing so you need an expert.

Q: Is it easy to learn EFFECTIVE internet marking tricks to get hot prospects clicking on your site?
A: This requires marketing smarts..and a little psychology.

The most common problem people do with their web sites is not doing anything!
Get it up and out there no matter what..It can also be improved and tweaked.
Just the act of editing it occasionally is often all you need to do to get the Googlisation working.

SEO is also misleading…Some keywords are easier than others…how do you know what ones work?
Googlisation for Generating Business requires someone with search engine knowledge plus marketing smarts and checking once a month what people are doing.
A little bit each month is all that is required to knock even big names out of the water(internet searches). You can't just set it up and hope to get to the top and stay at the top.

So we could say focus on the keywords "SEO Sydney" or "SEO Australia" but would that be useful? The clients we want are those that are referred. Again another trick to using the internet is that referrals are King. But how do you generate referrals via the Internet? Too many questions for tonight…Till my nest post….

Regards,

Tom Bizannes
Sydney, Australia

How to improve but not change an excel application?

Saw an interesting Excel Application today.

But now have to figure out how to tweak it without changing it too much.
Some parts are really simple..but require some of the code we have done for others..
There are a lot more tricks one can do with the many tabs by having a summary sheet etc.

Was quite impressed how they used the Goal Seek Function - will need to look at how one can code to use the what if functions and add "scenarios" etc....

The watch window is also interesting in that it tells you the current value of a cell or tow - so if you change the value that effects this cell, you can see the value...How this could make it easier for someone or whether we can get these values in code is another story...

SharePoint Document Library Windows Explorer view not showing on Windows Server

This seems to be a Windows 2003 Server issue:

One article says to install the web dav client.

The simpler method to look at first is disabling strict name checking on the sql server.
This is just a registry setting, so it isn’t as much trouble as doing an install.
As per the white paper from Microsoft and also as per this article:
http://www.cleverworkarounds.com/2007/10/15/poor-windows-explorer-view-performance-in-sharepoint/




Regards,
Tom Bizannes
SharePoint Consulting

Sydney, Australia

SQL Service Integration Services and Excel Pivot Tables

Excel automation on sql server without Excel or third party addons… Is it possible?

Most people will tell you not if you want pivot tables updated.
Accidently found out why when pumping data into ranges, the amounts are put in as text fields, not numbers.
This means the pivot tables don't update!

But there is a simple workaround to make the data numeric rather than text, so the pivot tables work nicely.

We are working on pumping out dozens of spreadsheets with many pivot tables to a SharePoint Extranet.
SSIS is an obvious choice because they want one big excel spreadsheet with many sheets and Reporting Services (even in sql 2008) cannot deliver a professional finish.
* For those unaware of this issue – reporting services pumps out each sub report on a separate sheet but the sheet names cannot be changed from sheet 1,sheet 2 etc…

The end result is a nice SharePoint Extranet reporting solution which goes well with our special Excel SharePoint Extranet creator Application.
What's funny, is how many times you need to find workarounds for Microsoft Products even in the 21st century…like the workaround to update links in excel services etc.

If you have a similar problem and want a nice resolution, let us know.

Regards,

Tom Bizannes

Solutions for Microsoft office and SharePoint

 

Massive conversion of hundreds of word templates to Word 2007.

Quite a few tricks had to be used.

Weird items like table alignments etc

We have a nice little excel application that lists all the custom toolbars and commands.

It even created the .dotx files for those without macros in them, saving a lot of work.

Some toolbars would delete from the add-ins menu as they were in the standard toolbar.

We had to run a reset in that case to clear them.

ActiveDocument.CommandBars("Custom toolbar Name").Delete

ActiveDocument.CommandBars("Standard").Reset

If you are planning on migrating your word templates, excel templates or other office applications to Office 2007 why not let us know.

Regards,

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

Reporting Services saving excel in Excel 97 to Excel 2003 format

 













Thank goodness Sql Server Reporting Services 2008 saves to Excel as FileFormat 56 – e.g. the old but compatible version of excel from 97 through to 2003.

The principle file format enumerations in Excel 2007 are:

• 51 = xlOpenXMLWorkbook (macro-free Excel 2007 workbook, .xlsx)
  
• 52 = xlOpenXMLWorkbookMacroEnabled (Excel 2007 workbook with or without macros, .xlsm)
  
• 50 = xlExcel12 (Excel 2007 binary formatted workbook, with or without macros, .xlsb)
  
• 56 = xlExcel8 (Excel 97 through Excel 2003 formatted files used in Excel 2007, .xls)
  
• 6 = .csv
  
• -4158 = text
  

Just go into the vba window (alt-F11) after opening the workbook and type thisworkbook.FileFormat.

Regards,

Tom Bizannes
Excel development
Sydney, Australia
http://www.macroview.com.au