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/
Thursday, June 18, 2009
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
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
Sunday, May 24, 2009
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....
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....
Sunday, May 17, 2009
MacroViewWISDOM.com goes live
Finally MacroView has split the products and services into two different web sites.
The SharePoint WISDOM solutions are now on http://www.macroviewWisdom.com
The products and services are on http://www.macroview.com.au
It's funny, only one business day and the google analytics are already showing a good response....
The SharePoint WISDOM solutions are now on http://www.macroviewWisdom.com
The products and services are on http://www.macroview.com.au
It's funny, only one business day and the google analytics are already showing a good response....
Sunday, April 05, 2009
Dealing with large spreadsheets
Special techniques are required to cope with large volumes of data and/or to have many Excel Reports based on one spreadsheet.
Introduction
What is a large spreadsheet?
Is it the sheer size, the number of used rows in a particular sheet or having lots of worksheets?
All and any of these make for large unwieldy spreadsheets prone to error and known to consume lots of a user’s time and effort.
This article discusses techniques to deal with each of these situations and the general problems that they are prone to.
Dealing with large Spreadsheets
Why do spreadsheets grow very large?
Often they are used as databases, and with Excel 2007 now being able to cater for a millions rows, they can hold quite a lot of data.
The typical scenario is to have a worksheet with the data and other worksheets which use pivot tables and lookup functions like vlookup to analyse and report on the data.
Another situation that is quite common is monthly reporting. You start off with reporting for a number of branches for one month and then you end up with reporting month after month for a year or two and you now have a very complex set of linked workbooks.
e.g. 10 branches by 24 months 240 linked workbooks plus the 24 data sheets….!
Ghost rows can also bloat a workbook. Especially if it came from a very early version of excel….There are techniques to eliminate these.
Vlookup versus Index/Match
Let’s examine this vlookup formula.
=VLOOKUP(E2,A:C,2,FALSE)
There are four parts:
1: Cell Value to use – on some versions of excel there were major issues with dashes in the value to lookup.
2: The range to look-in – Note: How we used the columns, rather than a selected range.
e.g. We could have used
=VLOOKUP(E2,A1:C6,2,FALSE) instead with the range A1 to C6 specified
3: The Column with the value. In this case the 2nd column of the range is specified
4: Exact or Approximate match…beware this can cause major issues!
Setting this to false makes it do an exact match.
* One trick to getting values is to do an approximate match on a list with ever increasing values and it stops when the value is larger than the one it is trying to find. This enables you to use vlookup to get a value with a specific range.
Often people will use the more complicated index/offset and match formulae to get the same or a more accurate result.
e.g. =INDEX(B:C,MATCH(E2,A:A,0),1)
or =OFFSET(B:C,MATCH(E2,A:A,0)-1,0,1,1)
Noting that the index/match combination is simpler and easier than the offset one.
The match part looks up the column with the value and returns the row number.
The index formula then uses the row number and simply retrieves the value from the column specified in the range selected.
Here’s an example doing it across sheets:
=VLOOKUP(A2,Sheet1!A:C,2,FALSE)
=INDEX(Sheet1!B:C,MATCH(A2,Sheet1!A:A,0),2)
Or
=VLOOKUP(A2,'Large Data Worksheet'!A:C,2,FALSE)
=INDEX('Large Data Worksheet'!B:C,MATCH(A2,'Large Data Worksheet'!A:A,0),1)
We often have had to replace every formula for a client changing the vlookup to an index / match for better accuracy.
Linked Workbooks
The story gets even better when you have a linked workbook!
What if the workbook gets moved? You get those nice messages says that it cannot update the links, when you open the workbook.
=VLOOKUP(A2,'[My Data.xlsx]Large Data Worksheet'!$A:$C,2,FALSE)
=INDEX('[My Data.xlsx]Large Data Worksheet'!$B:$C,MATCH(A2,'[My Data.xlsx]Large Data Worksheet'!$A:$A,0),1)
You can select “Updated” and “Edit Links” when it cannot find the source workbook. This then allows you to select the new workbook.
If you don’t select the workbook and just say Continue, then you see the last set of values that it remembered.
In most cases, it is the monthly reporting scenario were most of these large spreadsheet systems are first created and then the whole system becomes a very complex and prone to error. E.g. The example above of 10 branches by 24 months 240 linked workbooks plus the 24 data sheets….!
What should you do?
Use Index/Match rather than vlookup if looking up text values.
Copy and past values into your monthly reports, so you don’t have links everywhere.
Get a developer in to assist you to get the data directly out of a database using a little vba.
Pump the data back into a database for monthly reporting.
* Makes comparing months and years a lot easier.
Summary
Often spreadsheets start small and grow to become very large and hard to use. By using inbuilt lookup functions and knowing when to separate large lists, you can save a lot of time and improve the usability and accuracy of your data.
Being aware of issues with certain lookup functions such as vlookup and linked workbooks, is half the battle with gaining control again.
Know when to use a database for your data and not excel.
Questions?
If you have any questions about ways to deal with huge spreadsheets, let us know.
http://www.macroview.com.au
Introduction
What is a large spreadsheet?
Is it the sheer size, the number of used rows in a particular sheet or having lots of worksheets?
All and any of these make for large unwieldy spreadsheets prone to error and known to consume lots of a user’s time and effort.
This article discusses techniques to deal with each of these situations and the general problems that they are prone to.
Dealing with large Spreadsheets
Why do spreadsheets grow very large?
Often they are used as databases, and with Excel 2007 now being able to cater for a millions rows, they can hold quite a lot of data.
The typical scenario is to have a worksheet with the data and other worksheets which use pivot tables and lookup functions like vlookup to analyse and report on the data.
Another situation that is quite common is monthly reporting. You start off with reporting for a number of branches for one month and then you end up with reporting month after month for a year or two and you now have a very complex set of linked workbooks.
e.g. 10 branches by 24 months 240 linked workbooks plus the 24 data sheets….!
Ghost rows can also bloat a workbook. Especially if it came from a very early version of excel….There are techniques to eliminate these.
Vlookup versus Index/Match
Let’s examine this vlookup formula.
=VLOOKUP(E2,A:C,2,FALSE)
There are four parts:
1: Cell Value to use – on some versions of excel there were major issues with dashes in the value to lookup.
2: The range to look-in – Note: How we used the columns, rather than a selected range.
e.g. We could have used
=VLOOKUP(E2,A1:C6,2,FALSE) instead with the range A1 to C6 specified
3: The Column with the value. In this case the 2nd column of the range is specified
4: Exact or Approximate match…beware this can cause major issues!
Setting this to false makes it do an exact match.
* One trick to getting values is to do an approximate match on a list with ever increasing values and it stops when the value is larger than the one it is trying to find. This enables you to use vlookup to get a value with a specific range.
Often people will use the more complicated index/offset and match formulae to get the same or a more accurate result.
e.g. =INDEX(B:C,MATCH(E2,A:A,0),1)
or =OFFSET(B:C,MATCH(E2,A:A,0)-1,0,1,1)
Noting that the index/match combination is simpler and easier than the offset one.
The match part looks up the column with the value and returns the row number.
The index formula then uses the row number and simply retrieves the value from the column specified in the range selected.
Here’s an example doing it across sheets:
=VLOOKUP(A2,Sheet1!A:C,2,FALSE)
=INDEX(Sheet1!B:C,MATCH(A2,Sheet1!A:A,0),2)
Or
=VLOOKUP(A2,'Large Data Worksheet'!A:C,2,FALSE)
=INDEX('Large Data Worksheet'!B:C,MATCH(A2,'Large Data Worksheet'!A:A,0),1)
We often have had to replace every formula for a client changing the vlookup to an index / match for better accuracy.
Linked Workbooks
The story gets even better when you have a linked workbook!
What if the workbook gets moved? You get those nice messages says that it cannot update the links, when you open the workbook.
=VLOOKUP(A2,'[My Data.xlsx]Large Data Worksheet'!$A:$C,2,FALSE)
=INDEX('[My Data.xlsx]Large Data Worksheet'!$B:$C,MATCH(A2,'[My Data.xlsx]Large Data Worksheet'!$A:$A,0),1)
You can select “Updated” and “Edit Links” when it cannot find the source workbook. This then allows you to select the new workbook.
If you don’t select the workbook and just say Continue, then you see the last set of values that it remembered.
In most cases, it is the monthly reporting scenario were most of these large spreadsheet systems are first created and then the whole system becomes a very complex and prone to error. E.g. The example above of 10 branches by 24 months 240 linked workbooks plus the 24 data sheets….!
What should you do?
Use Index/Match rather than vlookup if looking up text values.
Copy and past values into your monthly reports, so you don’t have links everywhere.
Get a developer in to assist you to get the data directly out of a database using a little vba.
Pump the data back into a database for monthly reporting.
* Makes comparing months and years a lot easier.
Summary
Often spreadsheets start small and grow to become very large and hard to use. By using inbuilt lookup functions and knowing when to separate large lists, you can save a lot of time and improve the usability and accuracy of your data.
Being aware of issues with certain lookup functions such as vlookup and linked workbooks, is half the battle with gaining control again.
Know when to use a database for your data and not excel.
Questions?
If you have any questions about ways to deal with huge spreadsheets, let us know.
http://www.macroview.com.au
Monday, March 23, 2009
Error 2227 Issue with clipboard in Access 2007
Error 2227: The data on the Clipboard is damaged, so Microsoft Office Access can't paste it.
Had to catch the error and advise the user to close excel first!
This nightmare was caused by an patch to excel 2007.
So put this error message at the bottom of the procedure:
Noting it was in an access 2003 database but the error occurs when running access 2007:
If Err.Number = 2227 And Application.Version = "12.0" Then
MsgBox "There is an issue with an update of excel 2007 and you need to close excel before pasting", vbCritical
Else
MsgBox "Error pasting and updating data" & vbNewLine & Err.Description & vbNewLine & "Error No:" & Err.Number, vbInformation
End If
Had to catch the error and advise the user to close excel first!
This nightmare was caused by an patch to excel 2007.
So put this error message at the bottom of the procedure:
Noting it was in an access 2003 database but the error occurs when running access 2007:
If Err.Number = 2227 And Application.Version = "12.0" Then
MsgBox "There is an issue with an update of excel 2007 and you need to close excel before pasting", vbCritical
Else
MsgBox "Error pasting and updating data" & vbNewLine & Err.Description & vbNewLine & "Error No:" & Err.Number, vbInformation
End If
Thursday, March 19, 2009
Re: the Dot Net User group on Wednesday night 18th March 2009
Learnt a few things but went hungry…
What’s the deal that we missed pizza etc
Poor show, sitting there hungry while trying to digest c# stuff…..
News
– There was something about Microsoft Slashing Pricing by 15 to 25% for enterprise server software?
- .Net Code Camp in first weekend in April…
Slide Rocket is cool to do presentations…Can then output to PowerPoint.
Does nice spinning like a cube and allows you to easily select pictures from flicker etc
iPhone
They showed some of the new apple features coming up for the iPod….Maybe that’s why we got no pizza!!!!
Interesting how many apps and developers are into this now, all with apple taking 30% cut of everything!
Wait till the european commission tells apple to cut the monopoly!
There was even a dmf like app for SharePoint that runs on the iPhone!!!!
Saas stuff was though provoking.
There are quite a few things in the cloud and they are getting very compelling as an alternative.
Things like using external providers for bulk emailing, accounting, CRM, Support….
e.g.
Saasu for accounting rather than myob……cheaper and better and web based!
Constant Contact or Campaign Monitor for bulk emailing / newsletters as they have anti-spam agreements you can safely bulk email etc…
Noting the Campaign Monitor is sydney based…..
Zendesk and LivePerson for support
Cross site scripting – making your site more hackerproof
Then a quick one on cross site scripting and how an enterprising guy wrote some javascript he put into his facebook page that wrote a comment in anyone’s page who visited his.
Just a case of adding javascript to do something in a comments area on a web page…think blogs as well etc…
That hack got a good comment about him on thousands of pages very rapidly…..
In .net you can ensure comments cannot have javascript inserted by using html encoding….or antixss.htmlencode …..
Or write a function and add it to your tagmapping in the web.config for all lables and literals…….
6 Design Patterns…
Heavy stuff and kind of do some of them anyway depending on the situation.
For more information suggested first going to Wikipedia…also good for lots of other coding situations and information.
Interestingly the suggest was that there is a book “Head First Design Patterns” code patterns with a blonde with pony tails on the front – obviosuly marketing ot the geek factor…
Even though it is in java, to translate to c# is as simple as 1 line…..
The other book was c# 3.0 design patterns….
Justin King the presneter said he had another book about design patterns but it was so boring he fell asleep on every page.
www.dofactory.com has examples one can download examples from teh books above...
Regards,
Tom Bizannes
http://www.macroview.com.au
What’s the deal that we missed pizza etc
Poor show, sitting there hungry while trying to digest c# stuff…..
News
– There was something about Microsoft Slashing Pricing by 15 to 25% for enterprise server software?
- .Net Code Camp in first weekend in April…
Slide Rocket is cool to do presentations…Can then output to PowerPoint.
Does nice spinning like a cube and allows you to easily select pictures from flicker etc
iPhone
They showed some of the new apple features coming up for the iPod….Maybe that’s why we got no pizza!!!!
Interesting how many apps and developers are into this now, all with apple taking 30% cut of everything!
Wait till the european commission tells apple to cut the monopoly!
There was even a dmf like app for SharePoint that runs on the iPhone!!!!
Saas stuff was though provoking.
There are quite a few things in the cloud and they are getting very compelling as an alternative.
Things like using external providers for bulk emailing, accounting, CRM, Support….
e.g.
Saasu for accounting rather than myob……cheaper and better and web based!
Constant Contact or Campaign Monitor for bulk emailing / newsletters as they have anti-spam agreements you can safely bulk email etc…
Noting the Campaign Monitor is sydney based…..
Zendesk and LivePerson for support
Cross site scripting – making your site more hackerproof
Then a quick one on cross site scripting and how an enterprising guy wrote some javascript he put into his facebook page that wrote a comment in anyone’s page who visited his.
Just a case of adding javascript to do something in a comments area on a web page…think blogs as well etc…
That hack got a good comment about him on thousands of pages very rapidly…..
In .net you can ensure comments cannot have javascript inserted by using html encoding….or antixss.htmlencode …..
Or write a function and add it to your tagmapping in the web.config for all lables and literals…….
6 Design Patterns…
Heavy stuff and kind of do some of them anyway depending on the situation.
For more information suggested first going to Wikipedia…also good for lots of other coding situations and information.
Interestingly the suggest was that there is a book “Head First Design Patterns” code patterns with a blonde with pony tails on the front – obviosuly marketing ot the geek factor…
Even though it is in java, to translate to c# is as simple as 1 line…..
The other book was c# 3.0 design patterns….
Justin King the presneter said he had another book about design patterns but it was so boring he fell asleep on every page.
www.dofactory.com has examples one can download examples from teh books above...
Regards,
Tom Bizannes
http://www.macroview.com.au
Sunday, March 15, 2009
Synchronizing lists in SharePoint with Excel
Yes you can do it in both Excel 2003 and 2007
Did you know that you can get an addin for Excel 2007 for two way synchronization and you can write a macro to update a list from SharePoint via VBA code?
Just google "Publishing ad Synchronizing Excel 2007 tables to SharePoint Lists" to find the addin.
As for the Excel 2003 or 2007 vba code, this was cool.
A client wanted to color code a sheet from excel so we wrote code to dynamically create the list, copy it to another worksheet and colour in cells depending on the value in one column...
Just one click and this list always comes out nicely formatted.
Another case where a little coding can make life easier!
For Excel or SharePoint questions just goto:
http://www.macroview.com.au
Did you know that you can get an addin for Excel 2007 for two way synchronization and you can write a macro to update a list from SharePoint via VBA code?
Just google "Publishing ad Synchronizing Excel 2007 tables to SharePoint Lists" to find the addin.
As for the Excel 2003 or 2007 vba code, this was cool.
A client wanted to color code a sheet from excel so we wrote code to dynamically create the list, copy it to another worksheet and colour in cells depending on the value in one column...
Just one click and this list always comes out nicely formatted.
Another case where a little coding can make life easier!
For Excel or SharePoint questions just goto:
http://www.macroview.com.au
SharePoint Designer Issues with local - remote screen
If you don't refresh the local and remote views by clicking on the refresh item, it doesn't sort by date although the newer date shows!
Also, getting issures were a file uploaded is still showing as changed because the auther is different...
But I just uploaded it!
So something weird about the PC setting is making this show me as another user.
Also, getting issures were a file uploaded is still showing as changed because the auther is different...
But I just uploaded it!
So something weird about the PC setting is making this show me as another user.
Startup options for Microsoft Access
You can add command line switches when opening up access via a shortcut or bat file.
To go one step further we had to also use a specific security file.
See below for the bat file script:
@echo off
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" %~dp0MyDatabase.mdb /wrkgrp %~dp0MySystem.mdw
cls
@exit
This opens using access 2003 a database called MyDatabase and a Security file caled MySystem.
What is nice is that everything is relative to where the script lives...
Apparently this opens in shared mode and the other command line options are:
To go one step further we had to also use a specific security file.
See below for the bat file script:
@echo off
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" %~dp0MyDatabase.mdb /wrkgrp %~dp0MySystem.mdw
cls
@exit
This opens using access 2003 a database called MyDatabase and a Security file caled MySystem.
What is nice is that everything is relative to where the script lives...
Apparently this opens in shared mode and the other command line options are:
/excl exclusive access.
/ro read-only access.
/user Starts Access using the specified user name
/pwd Starts Access using the specified password
/excl Opens the specified Access database for exclusive access.
To open the database database for shared access in a multiuser only environment, omit this option.
/x macro Starts Access and runs the specified macro.
Another way to run a macro when you open a database is to use an Access AutoExec macro.
/cmd Specifies that what follows on the command line is the value that will be returned by the Command function.
This option must be the last option on the project command line.
You can use a semicolon (;) as an alternative to /cmd.
/nostartup Starts Access without displaying the startup dialog box
/wrkgrp Starts Access using the specified workgroup information file. database
/runtime Starts Access in run-time mode.
And these look good to use if you want to schedule compact and repair etc
Although, we put temp tables in separate tables to avoid database bloat and conflicts, so compact and repair isn't required.
/compact [target database] Compacts and repairs the Access database
/convert [target database] Converts an Access database in an earlier version
* Specify the source database before using the /convert option.
For any more detail just Google:
"How to use command-line switches in Microsoft Access"
###
Thinking of Migrating Access to Sql Server?
Talk to the guys who know all the tricks....
http://www.macroview.com.au
Subscribe to:
Posts (Atom)
