Archive

Archive for April, 2009

Access to the VB project is not trusted

April 24th, 2009 2 comments

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.

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Error messages Tags: , ,

Count Text in a Range

April 24th, 2009 No comments

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.

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: String Operations Tags: , , ,

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

April 6th, 2009 6 comments

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

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Error messages Tags: