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

How to add a hyperlink on a intranet to open an access database

Using a unc path with file: in front of the unc path should do it.
e.g. File://Servername/ShareName/Folder/../something.mdb

You can even use the above method to point to a shortcut.
Noting that shortcuts end in .lnk (that's a L not a capital i)
e.g. File://Servername/ShareName/Folder/ShortcutToDatabase.lnk

That way you can specify the link to run the 2003 or 2007 version of access and to open in shared mode – so no locked out users.

The biggest issue is probably that spaces might need to be %20 instead .

Tom Bizannes
Microsoft Access Design and Development
Sydney, Australia
http://www.macroview.com.au

Excel Toolbars

Note: Make the toolbar more robust:

Call your proc but add the full path as per the workbook with the code

.OnAction = "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!SystemInfo"

eg
Public Sub Auto_Open()
Dim cBar As commandbar
Dim cbarReports As commandbar
Dim myControl As CommandBarButton

For Each cBar In Application.CommandBars
If cBar.Name = "Toolbar_Name" Then
cBar.Delete
End If
Next cBar

Set cbarReports = CommandBars.Add("Toolbar_Name")
'Add System button
Set myControl = cbarReports.Controls.Add(Type:=msoControlButton)

With myControl
.Style = msoButtonCaption
.BeginGroup = True
.Caption = "About"
.OnAction = "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!SystemInfo"
End With

End Sub

Themes and Colors in Office 2007

Did some really cool stuff to overcome limitations in Excel 2007 with coloring of charts...

Colors and themes are interesting and found these links really good.

When people first starting using themes in office 2007, they get can easily get confused about the new coloring and font themes you can create, but once you get hte hang of these, you can create charts and documents with real pizazz!

http://www.colorschemer.com/online.html

http://colorschemedesigner.com/

Nightmare presenting at the SharePoint User Group

Presented at the SharePoint User Group on Tuesday 17th June.

The topic was The need for speed and recovery...based on a sql perspective...

Was heckled by both the co-ordinator and another bloke as they were adamant that Sql Backups of SharePoint couldn't be trusted even though they had never had issues with clients and Microsoft had never advised them otherwise.

So how does one handle such heckling...It even went so far as the SharePoint MVP saying he didn't believ in sql log backups and simple recovery mode was the only way to go!

What an &^*&*&( ...say no more....so many people are ignorant of sql server and when they see a realy large log file they think, krap...The log file doesn't grow out of control unless people don't do log backups....

So how does one handle hecklers? Hammer them for more specifics or say that's off the topic tonight, we can get back to you on a break...

Also, looked up books on speaking and there are some good ones that help you get your presentation across....Some reading is now in order...

One book kind of sums up how to present by it's title alone...Presenting with 7 slides or less..
I had 30 slides and was juping all over the place....

Below, then, are links to articles which offer good advice for handling difficult audience members, even hecklers.
• Dealing with difficult speaking situations (work911.com)
• Advice from 3M on dealing with Hecklers (3M website)
• Dealing with disruptive audience members (from Speakernet News)
• How to handle difficult audiences (presentation-pointers.com)
• Handling various forms of audience disruption (refresher.com)
• Dealing with difficult audiences (effectivemeetings.com)
• Dealing with difficult negotiators (negotiatelikethepros.com)
• Presenting to difficult audiences (helpforschools.com)
• Dealing with tough questions (findarticles.com)
• Do's and don'ts for dealing with difficult people (findarticles.com)
• Dealing with difficult people (findarticles.com)
• Dealing with hecklers and "snipers" (findarticles.com)
• Advice for stand-up comics (jessethecomic.com)
• The BBC on why people heckle

DotNetNuke and SharePoint 2007 on the same box

This was fun!
Windows 2008, IIS 7 and MOSS 2007 all on my dev box....
Wanted to avoid virtuals but dotnetnuke5 wouldn't install.
After some tweaks it's fine..It's all about the web.config.
Only two items were not so obvious.
.net trust level...changed this
Issue with mac view state so set it to false - enableViewStateMac="false"
So now have a windows 2008 server, sql 2008, IIS 7, Moss 2007 and DotNukeServer with Visual Studio 2008....
Let's develop....