Monthly archives for April, 2009

Access to the VB project is not trusted

Apr24
2009 2 Comments Written by admin

I was trying to enable an add-in for Excel 2007, and I kept getting this error:

Access to the VB project is not trusted

You can turn this message off by going to Developer tab in the ribbon and then clicking on the Macro security button, and checking the check box for “Trust access to the VBA project object model.”

A word of caution: be wary of the add-ins/projects that need VBA access. Don’t allow any project/add-in with VBA access, unless you know its exact purpose or the author of that project.

Posted in Error messages - Tagged , ,

Count Text in a Range

Apr24
2009 Leave a Comment Written by admin

Counting text in a range is easy. For example, if you would like count how many times “text” is in a specified range (named data), you can write something like this:
=COUNTIF(data,"*text*")

This formula will give you the count of all the cells where Excel found “text” at any place.

But let’s say you want to count wildcard characters in a range, then you can write something like this:
=SUM(IF(NOT(ISERR(FIND("*",data))),1,0))

This formula should be entered as an array formula with Ctrl + Shift + Enter. Usually, wildcard can be “escaped” using a tilde (~), but the FIND function doesn’t understand wildcard characters like the SEARCH function does.

Posted in String Operations - Tagged , , ,

The data on the Clipboard is damaged, so Microsoft Office Access can’t paste it.

Apr06
2009 6 Comments Written by admin

This is a new one for me. One good thing working with Excel and Access is the Copy/Paste ease between them. I usually paste data from Excel to Access for these two reasons:

  1. Create tables in Access (for small tables)
  2. Paste Field names: either in a table or in the import specs form

When I am importing data of the web, usually they have field names and data types given that page, so it makes sense to import that table in Excel (using Import from Web option), and then copy the field names and directly paste them the import specs form in Access. However, I kept running in this error today, which MS acknowledged and provided a solution, caused due to a security patch.

I used a workaround to paste those field names: copy and paste the data in a text file, and the again copy and paste it in Excel.

Solution Update: Colleen provided one more MS KB number to fix this problem. So, there are two KB articles which acknowledge this problem:

KB967699: http://support.microsoft.com/kb/967699

KB958437: http://support.microsoft.com/kb/958437

Posted in Error messages - Tagged