Tom Bizannes from Sydney, Australia

PowerPoint 2007 add-in issues

2012-01-19T18:49:00.000-08:00

Created a PowerPoint 2007 Add-in for a client to add buttons in the ribbon to paste in excel objects and also to break the linked objects.
But it didn't run on my tst pc!
After some muckign around, found these worked fine in the addins directory.
So was it hte d: drive area that was causing issues?
Tried d:\temp and it worked. But the d:\!Clients didn't.
They all were in trusted locations so the only difference was the exclamation in the file name.
Why would this cause issues?
Another case of finding a workaround for some Microsoft Office Product.
###
Tom Bizannes is a Microsoft Developer who can talk the language of Business Users.
He works best with a good cup of coffee.

Studying for the SharePoitn Administrator Exam 70-668

2011-12-29T20:04:00.000-08:00

There are 4 main areas one nees to know very well to pass this:

Designing a SharePoint 2010 Farm Topology (27 percent)
Planning SharePoint 2010 Deployment (26 percent)
Defining a SharePoint 2010 Operations Strategy and Business Continuity (25 percent)
Planning for Search and Business Solutions (22 percent)

What does this mean for my study?

It looks like I know the first 3 areas quite well and the 4ths ok except for some of the new stuff or should I say, the marketing fluff that Microsoft outputs as some of what they are asking for seems a little ambigous.
Will now go over tehir learning plan and do a few step by step SharePoint installs and configuration.

This is going to mean a little work over the next week or so.
Here's to my painful journey to dichipher and study for an exam with no real studfy book or course material except what one can glean from the requirements.

Found out that there are hundreds if not thousands of links to mini articles at technet and msdn.
http://technet.microsoft.com/en-ca/library/ff627858.aspx
http://msdn.microsoft.com/en-us/library/dd776256%28v=office.12%29.aspx
So will go through these, plus hte help, and one of the wrox books and this will hopefully be what is required.

Marketing XMas email

2011-12-14T17:57:00.000-08:00

Interesting discussion was had when I drafted up an emai lto go to all consulting clients in nsw.
The big question was, why are you mentioning New stuff we don't do yet?
Why don't people get it?
This is the draft as below...but need to tweak it with even more "New" stuff....
Some of the new stuff allow one to put in a picture of a parrot....better than web photos eh?

Merry Christmas ….,

We hope you’ve had a good year.

This year has been an interesting one for ###’sconsulting services in the following three areas and

we do thank our clients who have worked with our consultingand development teams in the following areas.

aa) MicrosoftOffice Conversions and Upgrades

bb) DocumentManagement (Quality Office Add-ins)

cc) ContentManagement (WebSite Development)

Microsoft is bringing out some exciting new solutions nextincluding Parrot for CRM (note you need SharePoint for CRM) and the newversions of Exchange and Sql Server.

So you’ll hear new terms such as ”SocialMarketing” and “Digital Marketing” ina lot of their communication.

If you have any Microsoft Consulting or Developmentrequirements call us on 02-9249-2700 or email us.

The top three pointsof consulting/development work above include:

a) Office Conversions:

· The Speed testers to prevent locking andslowness issues with Excel and Access when you upgrade Microsoft Office

· Rebranding and updating Templates and Macros inWord is always busy

· New Charting and Reporting Solutions that usethe new Excel format to output professional graphics and numbers

b) Document Management

· MacroView DMF leading the way with more usefulfunctionality than most traditional DM systems

· ClauseBank enabling you to squirt in wordsnippets into your emails and corporate documents

· Saving Messages to SharePoint – the hottestadd-in for SharePoint world-wide.

c) Content Management (Web Site Development):

· The SharePoint Standardiser – to bring alldocument libraries into a standard configuration

· Using the new DotNetNuke Content ManagementSystem with it’s nice new editor and SmartPhone Templates

· ASP.Net and the old Classic ASP Web Developmenthas also kept us busy this year with clients’ websites and intranets

###

SharePoint 2010 Administration exam

2011-12-07T18:54:00.001-08:00

For the Microsoft Gold Partnership coming up in Feb 2012 this requires 4 people to do the exam below.

Exam 70-668:

PRO: Microsoft SharePoint 2010, Administrator

Designing a SharePoint 2010 Farm Topology (27 percent)

Planning SharePoint 2010 Deployment (26 percent)

Defining a SharePoint 2010 Operations Strategy and Business Continuity(25 percent)

Planning for Search and Business Solutions (22 percent)

Interesting how the farm topology is worth more than the other parts......

For SharePoint Consulting Services click this link

Microsoft Access 2007/2010 Exporting tables to Excel

2011-11-29T19:33:00.001-08:00

Interesting question:

There is a command in access VBA that's been around for ages called TransferSpreadsheet
Funny enough there are a few variables to do with te format:

acSpreadsheetTypeExcel12 --> this is to export as .xlsb
acSpreadsheetTypeExcel12xml --> This is to export as .xlsx

e.g.:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "MyTable", "c:\temp\MyTable.xlsx", True

acSpreadsheetTypeExcel9 --> This refers to Excel 2000 format . e.g. MyFile.xls

The intellisense doesn't give you a acSpreadsheetTypeExcel10 or acSpreadsheetTypeExcel11 so maybe these don't exist.

##

Rotating Images on a web site

2011-11-23T22:28:00.001-08:00

Interesting but simple request.

Three images to rotate every 10 seconds on the front page of a web site.

Well this called for an Animated PNG. We first quickly tried an Animated Gif but the 256 colours makes pictures files quite grainy.

There is a Firefox add-in but it didn't work with our latest version so we downloaded a free but useful utility called "APNG Anime Maker."

Now the client has a very nice rotating set of images on their web site.

Just waiting for the next tricky bit of logic.

Just for your information, the alternative was some groovy jquery logic and even found that DotNetNuke has a free Widget Module called the Carouselat http://dnnwidgets.com/.

Still the Animated PNG is the simplest and easiest way to go in this case, but can see us using some jquery logic in the future for Web Development.

###

CSS Div Overlapping the footer area

2011-11-22T14:14:00.001-08:00

CSS can be very tricky as well as being quite elegant.
Sometimes you wish you'd stuck to html tables, and with html 5 becoming more prevalent, everyone will soon switch back to tables.
One issue we had recently was overalapping divs.
This was caused by the heightr being set on a div on the right side so the bottom footer was appearing over the top.
It seems the div itself expanded more than it's height.
The solution was to remove any height styles in the div and everything fitted.

How to best do an online survey or questionnare

2011-11-17T01:02:00.001-08:00

Interesting question.

This was asked by a client for whom I do asp.net and Classic asp updates for.

Said they were better off using an online program like Survey Monkey.

Funny enough when looking, there are quite a few programs out there.

wufoo also looks good.

So for Online Surveys or questionnaires there are three things to look for.

Is the audience internal or external?
How important is the security of the information?
What kind of reporting do you want from this afterwards?

Of course there are other minor items, like are there numbers? Are there lost of answeres requiring an unknown number of lines or tables rows to to generated? etc

Let's first look at the top three components and how the online and inhouse software can meet your needs.

1) If the audience is internal and the company is using Microsoft Office 2003 onwards, then the infopath tool is brilliant.
Once the audience goes external, then this becoimes a more expensive option and you would look at some online tools instead.
Noting not much comes close to the ability to add multiple lines with numbers and calcs as easily as infopath does.

2) If security is important, then that also needs to be checked and locked down. Emailing may not be the best option etc

3) Reporting always comes after and should be specified up front. What's trickier is reporting on open ending questions or multi-lined questions. This might even effect the way the questions are phrased

There you go, online surveys are go....There's lot of otehr components in successful surveys, but these three points cover the more technical aspects.

###

Tom Bizannes has a Business Degree and specializes in Microsoft Office and Web Solutions (Including SharePoint).

What's the perfect size Company for IT work?

2011-11-17T00:25:00.001-08:00

Interesting discussion I had today with a new employee at one of my middle sized clients.

She said, that this employer was just right. Smaller firms had too much putting out fires, little tasks to do etc, whilst big firms had too many processes and fixed ideas.

When I mentioned the Six Sigma with it's massive failure rate in industry, she mentioned a very large IT firm that uses it. No wonder they've tried to outsource so much work from india, because there are too many processes to get anything done.

This kind of changes the way one looks at what companies you'd want to work for.

The whole industry is changing at the moment due to cost cutting, so even the sizes and types of firms that are good to work for are changing.

Even one company that aggressively promotes that it was voted a top firm to work for a few years ago, now with it's Six Sigma implementation and other processes, everyone there seems under the gun and not the most pleasant workplace.

As for me, as long as the company makes sure it's employees have access to plenty of Moccona Coffee, they are ok.

Microsoft Access Upgrade Issues

2011-11-15T18:19:00.001-08:00

Had someone yesterday said it was too hard to upgrade a Microsoft Access Application to the 2007 or 2010 version.
Where do people get this idea?
Ignorance is not good.

My answer was simply, "We've done it quite a few times".
There are a few issues and googling Microsoft Access Upgrade quieries brings up 35 million results.

And "MsAccess Upgrade Issues" brings up just under 10 million results. (Pros like me refer to Microsoft Access as MsAccess and if you are googling for help, this is useful to know).

The main issues with upgrading access that I know are:

Moving the data, schema and logic to a 2007 or 2010 version, when you have access security set.
Dealing with hard-coded references to Excel, Word or Outlook
Some file manipulation macros
Generally dealing with really badly written code
ADO References caused by Windows 7 Service pack
New APIis if you are also using the new 64 bit of office
File Searching / handling functionality
Making the toolbars apepar in the ribbon

The one major item from a coding perspective is that the new versions of access are "fussier".
This means bad code is more likely to cause issues than ever before.

Also, Windows 7 running MsAccess 2007 or MsAccess 2010 can have issues which weren't there with MsAccess 2003 on an XP PC running applications on the network file share.
To prevent this, my Excel Speed app can show up some network issues, and one often needs to tweak the network and the PCs via some group policies and registry settings to fix.

Also one often raised question, is why not move the backend into sql. That's ok if you are using sql 2000, but from sql 2005 onwards, Microsoft SQL Server locks a chunk of rows at a time. So a well behaving multi-user application will start locking up when you move the data into SQL.
To get around this, ASP.Net is one solution, as it copies the record to memory and releases and lock and only writes back when it detects you have made a change.
Why they couldn't just build this into MsAccess is quite silly, as it would promote more use of Sql Server which can only be a good thing for someone qualified in Sql Server like me.

Converting your MsAccess Applications to newer versions is not tricky if you use a professional. ###

Microsoft Office Migrations

2011-11-13T21:52:00.000-08:00

Thinking of upgrading to Microsoft Office 2010?

A quick search in Google Insights shows a very interesting trend:
Searching on the words "microsoft office upgrade":
The top five in order are:

United States
Australia
United Kingdom
Canda
Germany

In the last 90 days, it's just the United states followed by the United Kingdom.

Does this mean that the US and the UK have put off their upgrades until now in order to reduce costs etc?

Then looking at just Australia, you get QLD followed by NSW and then VIC.
Does this mean the floods put QLD off and that NSW is just trying to cut costs?

There are a few pain points, especially in Microsoft Excel and Microsoft Access or (MsAccess) as it's commonly referred to in web searches.
There's a conversion tool aimed at MsAccess, from a mob called converter technology. They don't seem to have a handle on Excel or even a way to check the network is optimized for the combination of Windows 7 and Office 2010.

We even developed a network speed/locking tester written in excel that can show up issues before Office goes into production. Now to write one in MsAccess that covers issues with DAO/ADO connections taking forever.
One of the most interseting outcomes of the Excel Speed Tester is that a good network environment can read and write your file fastger than alot of PC hard disks can. This means that having your files on an optimized network can be faster in some cases than on your hard disk. We do say quite carefully, "In some cases", because once a network gets busy, most of us can feel if not see the effects.
For more information on the Speed tester email me via http://www.macroview.com.au.

The whole migration story gets murkier when Office 2007 is also thrown in as this is a nightmare version in terms of upgrading/coverting macro driven applications.

Unfortunately for us Excel and MsAccess Developers, Office 2007 will plague us for years, and even now, companies are migrating to Office 2007, because some ignorant adviser said this was less risky than office 2010. They obviously haven't consulted us!

###

Web Marketing

2011-08-31T05:18:00.001-07:00

Seo is a four letter word. Find out why.
Web Marketing is different from print advertising in many ways and requires a different approach.
Three things to look at are:
1-someone with web marketing knowledge
2-optimizing your web sites
3-utilizing other sites, email marketing and other sources to enhance your internet presence
What do I know?
A 400x better response than a competitor. 20x the calls from 5% of no of visitors of a competitor. That is why SEO is a four letter word.
Whilst you can pay people to increase your seo, beware what happened to BMW. They got blacklisted by google!

Excel 2007 Chart losing ActiveChart

2011-04-27T00:13:00.000-07:00

Interesting issue today with Excel 2007 and a little bit of VBA.

Have a client with issues with an excel addin not interacting well with the bloomberg addin.

So to get around this we used the old toolbar in the addins option.

Problem is that Excel 2007 deselects the Chart, so the active Chart code doesn't work.
This issue doesn't occur in Excel 2010 or Excel 2003.

We were first getting this ambiguous message:
"The specified dimension is not valid for the current chart type"
Another reason to go straight to Excel 2010 to crunch your data and charts.

Regards,
Tom Bizannes
Excel and Database Magician

Migration to Excel 2010 and Access 2010 the easy way

2011-04-16T20:14:00.000-07:00

Migrating to a new version of Office used be quite painless. With the major openxml format changes and major overhaul to both Microsoft Access and Excel, there are many breaking changes that can break many an existing application.
With the right amount of planning, most issues can be dealt with prior or when the migration goes ahead.
What is disturbing is the amount of mis-information about the different versions of Microsoft Office.
This article focuses on what you need to be aware of, and what are the best choices when upgrading the desktops and notebooks of all your users.

Recently a client asked me to assist with migrating to Excel 2007 using a $20K tool.
This appeared to have been written in ancient vb and doesn't seem to be built to cater for the two biggest issues with Excel Migrations.
What I couldn't believe was how developers make tools without knowing the product or add extensions to their products as an afterthought.
The Tool did work okay against Microsoft Access databases, although still haven't put this under pressure to test how it deals with issues we have discovered in simple access databases like fields not being big enough so forms just crash erratically.

Firstly there's the choice between the Microsoft Office 2010 32 bit or the Microsoft Office 2010 64 bit version.
Recommendation from Microsoft is to upgrade to the 32 bit version.
Only a few super power users will need the 64 bit version, and that's most likely due to badly designed cubes or lack of good database guys to roll up information nicely.

Why not 64 bit version of Microsoft Office?
Because that's causes alot of applications with code to break. Also many addons will not work, due to library differences between 32 and 64 bit office. It's not because the 64 bit version is buggy. It's different.

Version of Windows 7 to go to?
Go to the 64 bit version of Windows 7. Any 32 bit office app that doesn't work in Windows 7 64 bit version should be trashed or the developers given a talking to. There are some issues about the Windows 7 Service Pack 1 causing problems for users who don't have the same service pack. Microsoft has a few workarounds.
On first glance it appears to be mainly office 64 bit users who would be effected. The solution being to change the code to use late binding on the adodb (MDAC) libraries. Thes are used mainly to link to sql and other dobc databases. Most Access developerws went back to dao development, unless there were sql links, so alot of code can be fixed quickly and easily.

Do you upgrade your Microsoft Access databases to SQL Server?
No. Not unless you know what you are doing. If the Access databases only have 5 to 10 users, then keep them in Access. If you have more, be ware that sql can cause more locking and other issues, if you do not know what you are doing.

What about Excel files. Should we upgrade them?
Only if you want to as many excel workbooks might be linked to others.
The solution we came up was to add an addon in the Excel ribbon that dealt the linking and the other major issues so the users could do their conversions with less pain, or one click of the button as they say.

What items are missed or done the wrong way?
The new colouring themes are often forgotten about or not done correctly.
Ironically, the word guys try to dictate the styles, but Excel and PowerPoint users are mostly effected by the colouring. Some clients have spent thousands on graphic designer ideas for styles and fonts, but these guys don't always have a grasp on Office 2007 or Office 2010 themes as they live in their adobe software world.

###

Tom Bizannes is a Microsoft Certified Professional with a business background.
He works for a Microsoft Gold Partner in Sydney, Australia
http://www.macroview.com.au

Web Marketing the easy way

2011-03-07T03:35:00.001-08:00

What would you do if you could click a button and automatically have enough info to knock your competitor's links below yours?

By following some basic steps this can be easy to do.

The web us all about words. And it is also about providing information and entertainment to the masses.

As long as you can address these points and find out what your competitors are doing then you will get more business.

Tom Bizannes

Web Marketing faster and easier

Tom Bizannes
Web Marketing faster and easier

Excel having issues with dates importing CSV files

2011-02-10T16:12:00.000-08:00

Have you ever had a csv file opened by excel and the dates are in MM/DD/YYY format when you want them in DD/MM/YYYY format?

After going nuts with setting regional settings etc, it seems there's a parameter off by default when you open a csv file with excel in VBA.

Here's what you need to do to open and read csv files with dates if youdo not want USA datye formats.

sMyFileBook = ThisWorkbook.Path & "\MyFile.csv"
Set wbMyFile = Workbooks.Open(Filename:=sMyFileBook, UpdateLinks:=False, ReadOnly:=True, local:=True)
Set wsMyFile = wbMyFile.ActiveSheet

Regards,
Tom Bizannes

Development Notebooks - why so hard?

2011-01-24T18:34:00.001-08:00

It seems most notebooks come in only two flavours. HD and Full HD. and then there are netbooks.

HD doesn't have enough vertical pixels for viewing emails etc. So why does anyone use them?

So put hte question out there and got a few suggestions as follows:

Here are some of the responses:

I am running a ASUS G51J with 12GB RAM and FHD.

I'm using a Toshiba R700 with an I7 and a 256 solid state drive. The fact they use both slots to give the 4GB so you have to chuck away 4GB to get 8GB is annoying. At 1.1 kg it’s a great travel machine.

I've given up on trying to find a notebook with the resolution to develop and a size that's easy to fly with. So I use a Dell E4300 (13") and the docking station has a Dell U3011 (30" 2560x1600) monitor. I find the height of the monitor is great at 16x10 ratio. The U3011 is expensive but it makes a huge difference. It's like having the whole wall to work with. I'm also done with buying non-commercial grade hardware. I only get Dell Latitudes, no more Studios or XPS machines.

I have used a notebook with 1920 x 1200 screen res for about 5 years. You need to have good eyes since the text is pretty small (unless you change it to a larger DPI setting for text). I find it necessary for most of my work to have a higher res screen. My current laptop is an HP EliteBook 8530W with 8 GB RAM, and 7200 RPM HDD. It runs well for most tasks in development. I also hook it up to a second screen (1920 x 1200) via HDMI and it handles both screens at full res quite well.

As you can see, it was a mixed bag.

I need to carry the notebook so 17inches is too big.

At hte moment it's a choice between the ASUS g53W or the Apple Mac Book Pro.
The Sony one is nice but too expensive considering.
And hte dell xps15 is cheaper but .....not too sure.

Firefoxs addons for web development

2011-01-16T22:50:00.000-08:00

Found a page listing 10 best firefox addons.

Some of these didn't deserve to be on that list.

There are a heap of them for checking the search ranking, and trying to find ones you've used can be hard like the one that helps you to clear your google cache so you see what others are seeing, plus a new one to get the mobile phone view.
Also one always needs a youtube downloader.

Here's three of mine:

Firebug - https://addons.mozilla.org/en-US/firefox/addon/firebug/
FireSizer - https://addons.mozilla.org/en-US/firefox/addon/firesizer/
MeasureIt - http://www.kevinfreitas.net/extensions/measureit/

If you have any other ones let me. Also one's for Internet Explorer or Chrome.
As you might figure, we use FireFox alot to figure out the css styles in web sites when changing them.

It's also useful to add your delicious and other link saving addons...

Tell us your favorites

Office developer conference

2010-11-07T17:45:00.001-08:00

This was quite successful last
weekend. Will now go back and clue up on some other sharepoint & office techologies. One of the best sessions wasn't development related. Instead it was about presenting using powerpoint. Will post some more about that.

Published with Blogger-droid v1.6.3

Microsoft Sql Server - How big should your tempdb be?

2010-10-20T14:22:00.000-07:00

For those who wonder how big their tempdb should be or maybe just wondering why their indexes stay fragmented after running some rebuild/reorganisation scripts.

The tempdb needs to be larger than the largest index.

There's also a technique called Instant data file initialization that helps the tempdb expand more rapidly.

So if you have any rules / examples about this let us know.

Microsoft Sql Server 2005 Performance Analysis

2010-10-14T17:58:00.000-07:00

Note: This requires both Performance Monitor and Sql Profiler.
What this is essentially targeting is I/O Temp Db and Blocking/locking issues

Performance Monitor:
These are just aimed at the tempdb.....
Otherwise would have include some Physical Disk Counters as well.

As mentioned: Both this and the sql profiler need to be put on all the servers involved

SQL Server Transactions:Free Space in tempdb
SQL Server:Access Methods\Workfiles Created /Sec
SQL Server:Access Methods\Worktables Created /Sec
SQL Server:Access Methods\Mixed Page Allocations /Sec
SQL Server:General Statistics\Temp Tables Created /Sec
SQL Server:General Statistics\Temp Tables for destruction

Sql Profiler:
Database: Filter by database id = 2 (tempdb)
Data File Auto Grow
Log File Auto Grow

Errors and warnings:
Sort Warnings
ErrorLog
EventLog
Exception

Locks:
Deadlock
Deadlock Chain
Timeout
OLEDB:
OLEDB Errors

StoredProcedures:
SP:StmtCompleted
TSQL
SQL:BatchCompleted
SQL:StmtCompleted

Looking at DMVs won't work as there are too many loads overnight.

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

Excel Addin Support about code

2010-10-14T15:41:00.000-07:00

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

2010-10-14T15:17:00.000-07:00

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

2010-10-13T16:01:00.000-07:00

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

2010-09-15T23:54:00.001-07:00

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

2010-09-14T00:15:00.001-07:00

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

2010-09-06T23:58:00.000-07:00

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

2010-08-26T00:40:00.000-07:00

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

2010-08-12T21:46:00.000-07:00

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

2010-08-09T19:04:00.001-07:00

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

2010-07-29T00:10:00.001-07:00

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?

2010-04-06T05:19:00.001-07:00

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?

2010-02-02T13:48:00.000-08:00

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

2010-02-02T13:47:00.000-08:00

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

2010-01-28T14:29:00.001-08:00

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.

2010-01-13T15:31:00.001-08:00

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

2009-12-21T15:31:00.001-08:00

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

2009-10-22T21:42:00.001-07:00

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

2009-10-06T17:08:00.000-07:00

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

2009-06-18T19:21:00.001-07:00

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

2009-06-18T06:59:00.000-07:00

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

2009-05-24T04:49:00.000-07:00

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....

MacroViewWISDOM.com goes live

2009-05-17T18:39:00.000-07:00

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....

Dealing with large spreadsheets

2009-04-05T21:38:00.000-07:00

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

Error 2227 Issue with clipboard in Access 2007

2009-03-23T18:18:00.000-07:00

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

Re: the Dot Net User group on Wednesday night 18th March 2009

2009-03-19T18:46:00.000-07:00

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

Synchronizing lists in SharePoint with Excel

2009-03-15T20:23:00.001-07:00

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

SharePoint Designer Issues with local - remote screen

2009-03-15T20:23:00.000-07:00

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.

Startup options for Microsoft Access

2009-03-15T14:47:00.000-07:00

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:

/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

Sql Server User Group on Tuesday 10th March 2009

2009-03-13T04:49:00.000-07:00

Wow, a large crowd and many questions.

The topic was SharePoint for Sql Guys

Was trying to present three things but so many questions we barely got through the first bit.

The three parts were:
What you need to tell your SharePoint Administrator
What you need to do
What tools can help you.

The feedback was quite good with most liking the topic etc

Will now need to add my scripts and presentation

Would recommend you run the reports that monitor the server or use settings to check the fragmentation
See….http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

You are probably better having a quick look at:
http://technet.microsoft.com/en-us/library/cc964305.aspx

sql server reporting services 2008 windows 2008 and sharepoint can all live together

2009-03-13T04:47:00.000-07:00

Finally had the time to suss this out.

First you need to know the tricks to get to the hidden sharepoint admin settings and then there's one little config setting that will stop nearly every attempt to get it to work regardless of whether you use SharePoint integrated mode or native mode.

All I can say, is alot of people would strangle whoever created the sql server 2008 reporting services install because this one setting is just ridiculous.

And to think I thought it was IIS7 that was causing the issue!

Was getting errors like:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

and rsInvalidDataSourceReference

Regards,

Tom Bizannes

SharePoint and Sql Server

Sydney, Australia

http://www.macroview.com.au

Reporting Services and SharePoint Integration on Windows 2008 server

2009-03-13T04:19:00.000-07:00

Had fun trying this on a windows 2008 box.
Note this also applies for setting up on a vista box....
Everything worked after hitting each bit a few times!
So important to get permissions set, root site collections set properly and IIS7 configured right before installing anything.

So the steps to install in order are:
Set up IIS7 with all the correct settings:

Common HTTP Features:
Static Content
Default Document
HTTP Redirection
Directory Browsing

Application Development:
ASP.Net
ISAPI Extension
ISAPI Filters

Security:
Windows Authentication

Tools:
IIS Metabase Management
IIS 6 WMI Management

Then make sure you set tcp and named pipes in the sql server surface area configuration tool for the remote connections.

Then install service 2 for sql server and the service pack for the service pack if it isn't up to version 9.0.3054

Then run the sharepoint reporting services msi file that integrates them noting this doesn't run on a windows 208 server unless you install a reg hack to run as admin or turn off the uac.

Then you need to go into the reporting services configuration tool to configure and that is where many get mixed up.
Reporting Services needs to use the same pool as sharepoint to integrate.

Everything finally fell into place except the reporting services configuration settings didn't show in the sharepoint admin application page.
Had to go to the _layouts pages instead.

When deploying your reports, the pc with report designer should also be service packed and then you get an extra option regarding sharepoint in your deployment options wording for your target folders etc.

http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx
http://servername:portno/_layouts/ReportServer/ManageTrustedAccounts.aspx
http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx?Settings=RS

Regards,
Tom Bizannes
Sydney, Australia
Microsoft SQL Server and Microsoft Access Consulting

VBA Copy missing files

2009-02-23T19:52:00.000-08:00

Public Sub CopyMissingFiles(sProdFiles As String, sOfflineFiles As String, sFileExt As String)
Dim i As Integer
Dim sDestination As String
Dim iFiles As Integer
'FileExt = "*.pdf"

'Add \ at end if missing
If Not Right(sProdFiles, 1) = "\" Then sProdFiles = sProdFiles & "\"
If Not Right(sOfflineFiles, 1) = "\" Then sOfflineFiles = sOfflineFiles & "\"

iFiles = 0

With Application.FileSearch
.NewSearch
.LookIn = sProdFiles
.FileName = sFileExt
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count
sDestination = Replace(.FoundFiles(i), sProdFiles, sOfflineFiles)
If Not FileOrDirExists(sDestination) Then
'Debug.Print .FoundFiles(i)
FileCopy .FoundFiles(i), sDestination
iFiles = iFiles + 1
End If
Next i
End With

If iFiles > 0 Then
MsgBox "Copied " & iFiles & " pdf files to " & sOfflineFiles, vbInformation
End If

End Sub

Sub test_CopyMissingFiles()
Call CopyMissingFiles("D:\ProdTest\Files\", "D:\files\", "*.pdf")
End Sub

VBA Ado Example for Sql Server 2000/2005 etc...

2009-02-11T22:32:00.000-08:00

Just use {SQL Server} for most...
Note: Backward compatibility isn't set automatically on sql server 2005/2008!
So one can use Sql Server Native Client - note - you need to download and install this small component from codeplex.....on each client pc that will need it.
Or you use normal oledb connections which work fine, once named pipes is enabled on sql 2005/2008!

Public Function AdoExample() As Long
Dim AdoRs As ADODB.Recordset
Dim AdoCn As ADODB.Connection
Dim sCon As String
Dim sSql As String
Dim sServerName As String
Dim sDatabaseName As String

On Error GoTo err_AdoExample

sServerName = "Database Server"
sDatabaseName = "Database"

'sCon = "Driver=SQLNCLI.1;DataTypeCompatibility=80;Server=" & sServerName & ";database=" & sDatabaseName & ";trusted_connection=yes;"
sCon = "driver={SQL Server};Server=" & sServerName & ";Database=" & sDatabaseName & ";trusted_connection=Yes;"
'sCon = "driver={SQL Native Client};Server=" & sServerName & ";Database=" & sDatabaseName & ";trusted_connection=Yes;"
Set AdoCn = New ADODB.Connection
AdoCn.ConnectionString = sCon
AdoCn.Open
Set AdoRs = New ADODB.Recordset
sSql = "Select @Version as Version"
AdoRs.Open sSql, AdoCn ', adOpenDynamic, adLockOptimistic

debug.print AdoRs!Version

AdoRs.Close
AdoCn.Close

Exit Function

err_AdoExample:

MsgBox err.Description & vbNewLine & err.Number, vbInformation
Resume Next

End Function

Regards,
Tom Bizannes
Sydney, Australia
Database Development and Auditing

Re: Index / Match versus Vlookup macro

2009-01-05T18:13:00.000-08:00

Wow..

Vlookup is way too dodgy!

Index match returns the values looking up itmes in a column, but vlookup which should have had exact matches had issues with - in the names and came with with incorrect values....

=INDEX('Sheet2!C:E,MATCH(AG974,'Sheet2!C:C,0),3)

This checks for a match to cell AG974 in the other worksheet - 'Sheet2' and gets the value in column E...(The 3rd column in the columns selected)

Regards,
Tom Bizannes
Excel Macros and Development Sydney

Office Developer Conference 2008

2008-11-19T14:22:00.000-08:00

This was really good.

Full kudos to Graham Seach for Organising this.

Couldn't go to all the sessions...but learnt a few new tricks.....

I presented Real World Access and Real World Excel...Two rapid fire ones about migrations to Office 2007 where we helped clients to upgrade microsoft office and discovered a few gotchas..

Wow these really stretch you...It's easier to talk for an hour than for 10 minutes!

Got back and now designing an Access 2007 picture gallery/viewer for your pc...
Access can read and show pictures in a directory without having to load them into the database.

Regards,
Tom

Re: Michael Noel and SharePoint Virtualisation

2008-11-19T13:59:00.000-08:00

This was really good…

Michael Noel did a really good talk about virtualising SharePoint using either HyperV or Vmware. ( and he also mentioned no 3 in virtualisation - Cirtix Xen Server)

Still cannot believe Michael flew in from the states today and is flying out again today….just to talk to me and Peter regarding virtualising SharePoint…
Well actually there were a few others there….

He’s written a few SharePoint and isa server books and whitepapers for Microsoft etc…He knew his stuff.
One of his clients has 250,000 SharePoint Users!

Don’t virtualise Sql Server was one very important point…This is due to the high I/O of Sql Server
Next to go physical would be Index Server….
Other Virtuals only lose 2% to 3% of CPU performance so web servers do make a good case for Virtualisation.

Watch the NUMA boundaries…giving to much Ram for a virtual doesn’t equate to better performance if the NUMA boundary is hit..--> Too much paging etc...
Good rule of thumb … Ram per vm = Ram/CPUs…..
e.g two quad cores --> 8 cpus --> 64G of Ram --> 64/6 = 8G per virtual.

Hyper V guys need to get Ms Virtual Machine Manger 2008 – came out in Oct 2008…

Check out:
http://tinyurl.com/virtualsp
http://tinyurl.com/edgenoel2
http://tinyurl.com/edgenoel3

Virtual Machine Manager 2008 is out now….
System Centre Data Protection Manager 2007 great for shadow copies to restore sharepoint servers faster..

VMware has a cool tool P2V (free now!!) for copying physica to Virtual Machines…
Will need to email some people who asked me about this this year….

Big sites get vmware ESX or Citrix xen server 5 for vmotion or xen motion for great fail over if one server goes down.

Synergy and AvePoint have some SharePoint Replicators? … Will need to check these out…

Peter came along because apart from hyper v he is now playing with vmware---
It was good that Michael confirmed what I had hinted to the guys about -- don't virtualise SQL Server..That being said, up to 100 users should push sq server too far? Maybe not unless they are really using SharePoint by using an addon like Wisdom DMF where they can easily open and save their documents....Then take no chances...

Re: Sydney Sql Server User Group Tuesday 11th November 2008

2008-11-11T17:35:00.001-08:00

BI was the topic.

This was slightly different than a canned demo as open conversation and comments were the go..

The main things one got out of this would be that the more you know MDX, the better you can design reports, build cubes and anlyse the data....

There was a hint that one can do MDX in Excel 2007....Will try this out.....That would enable you to do more than just slice and dice.....

e.g. For power analytics...

Office Dev in two weeks time!

2008-11-02T14:11:00.000-08:00

The Office Development Conference is only two weeks away!

If you are thinking of going, you need to let them know via email so there will be enough Pizza!
Oh my gosh!...Will need to get my Cholesterol checked before this weekend!

Same as last year, cannot go to all the sessions I want to go to!

FYI – I am doing two quick rapid fire sessions, as I will be the only Excel Expert speaking apart from Grant at Angry Koala talking about data mining....
plus I couldn’t help myself and just had to do something on Access rather than just sit and correct people all the time!

Will have to hand around a few WISDOM cubes……

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

PC versus Mac - you tube sending up the mac

2008-09-18T16:15:00.000-07:00

They are cleverly done to send a certain message!

PC vs MAC - Money
The PC vs MAC war - from the PC's point of view.Written and directed by Laurie McGuinness. Watch the full series at www.lauriemcguinness.com

PC vs MAC - At work
The PC vs MAC war - from the PC's point of view.Written and directed by Laurie McGuinness. Watch the full series at www.lauriemcguinness.com

PC vs MAC - Music
The PC vs MAC war - from the PC's point of view. Written and directed by Laurie McGuinness. Watch the full series at www.lauriemcguinness.com

PC vs MAC - The girl
The PC vs MAC war - from the PC's point of view. Written and directed by Laurie McGuinness. Watch the full series at www.lauriemcguinness.com

Regards,

Tom Bizannes
Sydney Australia

Migrating to Office 2007

2008-09-15T03:01:00.000-07:00

Microsoft has made this so much fun!

Well I'm being sarcastic...This is not a simple process if you have vba enhanced applications.

Many clients are now going through the process and asking us to assist.

Excel linked workbooks and vba saveas are just two that come to mind immediately.
Was asked to knock up a bit of code to report on which workbooks had links and/or macros that could break.
The saveas issue will cause weird issues complaining of compatibility errors when there aren't any...but there are!

Word 2007 has other issues as some of the vba is just a little bit different so search and replace act a little different.

Outlook 2007 does weird things to your Microsoft Sql Reporting Services Scheduled html reports...Now that's a mouthful to say all at once!

Had to upgrade a 2007 access application application that created a menu on the fly depending on the user's status in a staff list and activated or de-activated certain items. All that because Microsoft blew away the object permission model.
But the new queries are nice with built in totals etc...

Regards,
Tom Bizannes
Microsoft Office Soutions
Sydney Australia
http://www.macroview.com.au

City to Surf Times

2008-09-15T02:56:00.000-07:00

Finally got my Certificate showing 86:53 as the 2008 time.

The City to surf guys emailed me the last few times as follows:
2000 - 124.26
2002 - 108.30
2006 - 104.39
2007 - 98.12

Now to dig up the other times when I actually ran it properly..

Sydney SQL Server User 9th September 2008

2008-09-09T18:57:00.000-07:00

Quite an interesting night based around SQL Server Reporting Services 2008…..

The pizza was plentiful…Cholesterol levels rose quite a bit last night.

Grant from Angry Koala, gave away some of his little koala bears for answers to some of his questions!
Interesting Marketing ploy!
Maybe MacroView needs to give away a lot more cubes!

The exciting news is that in October a SQL Server 2008 feature Pack will come out with lots more goodies such as report builder with a nice office 2007 look and being almost the same as the report designer!
Business users should get exciting.
There will be some other exciting new extras in this so will let you guys know…..
Hopefully will go through all this in the sql code camp in October etc…
There should be a lot more feature packs so that nice enhancements can be delivered faster and easier….

The new report designer is much nicer and changed quite a bit.
The tablix controls means smarter simpler and more powerful reporting without code gymnastics!

Big reports run smarter – eg. Only render the first page so they won’t bring the server to it’s knees!

Also the outputs to Excel are better along with the new output to Word. Nice!!!
Apparently one of the uses is to generate and send monthly invoicing etc….

The rich text stuff was only briefly flashed on the screen, but everything is so much nicer that you won’t want to do anything with the “old” stuff…

Two tips of the day:
1: Setting the interactive size for height and width to zero means it will fit the whole screen – e.g. no next page stuff.
The page size settings apply to printing…..

2: Name all reports starting with a number e.g. R001 – New Prospects.
That way every time a client ( or internal user ) calls about a report, it is really easy to figure out which one.
And what with the long names you use, many users will also get to know their favourite reports by number as well!

Coming up:
Next Wednesday at Microsoft, Adam Cogan at the .net user group will go over all the admin stuff..
I might go along and keep him in line and teach him a few tricks to do with incorporating this into SharePoint!

Also SQL Code Camp in Wagga Wagga is coming up this October so it should be quite interesting…

Tom Bizannes
Microsoft Certified Professional
Sql Server and Microsoft Office Development
Sydney, Australia
http://www.macroview.com.au/

MICR Enabled printers and on board printer calls.

2004-08-08T00:07:00.000-07:00

Just changed jobs recently.
My old company specialised in reconciliation systesm and data processing systems using Powerbuilder 7 and Sybase or MsSql Server at the backend.
They also tried to sell MICR enabled printers using the on-board simms sold by source tech from the USA which go into IBM and Lexmakr printers.
They didn't know how to read the manuals supplied and thought incorrectly it was very difficult to make calls to the simms.
Prior to leaving, I discovered in word that you can make printer calls!
All technology is easy if you have the time to read and absord all the relevent information.
Maybe I will sell my knowledge to people who want to use these bits of hardware, as it seems no-one thinks they are easy to use!
As for reconciliation systems, they are simple but there are few good ones out there!
Looking carefully at using foxpro and MSDE or SQL Server Express as the new tools to tackle this