Using SharePoint Lists as a data source in … well Excel

I am being asked to provide weekly data updates and …. I hate paperwork, especially when no matter how many of these I create, no one looks at them but they keep telling me that thisor that column is missing. Well SharePoint Lists are a perfect place to keep the action log and the updates for those actions, but people want to keep using Excel. It  is mainly because they don’t know how or what sharepoint lists do – but Excel – they know. My question to be answered is “how can I work in sharepoint and do a query to get a report that they will [never] be happy with”. Lets explore this. I have office 2013 and SharePoint 2013 now.

Known Error when Creating SandCastle HTML and Fix

When I created the html only the ThisAddin Namespace was showing up. This is a known error detailed here. The workaround/fix was to check “Include root namespace container” in the “Help File” section. This was a bit hard to find, but if you select “Help File” you will see the check box right below “Conceptual content placement”. Once checked all namespaces are now shown in the html once the build is performed again.

VSTO to VBA Guideline to Compile and Test

Common Error when compiling the VBA using the VSTO is found here. In order to fix it, I changed the long parameter in the VSTO to Int32. Then the VBA compiled fine.

Have to close excel in order to build the project.

Don’t need a public interface. Only these lines of code before the public worksheet object:

[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]

In order to add a reference to the VSTO you must click on “Register for COM interop”. This creates a .tbl file that is the reference that you need to add in order to use the VSTO. The “Register for COM interop” is found i the Project Properties -> Build -> Output.

Once the reference is added (you may have to check it as well) to Excel, hit “F2” from the VBA code which opens the Object Browser. Navigate to the VSTO class and check that none of the Members has “Unknown” as a property. If so, then this is due to an improper variable type. See the Compatible Variable Types between VBA and VSTO link. Fix any unknown types in the VSTO code and recompile.

In VBA I received the following error:

capture

In order to fix this, instead of using a cast to (Int32), I used Convert.ToInt32.

Unable to find manifest signing certificate (Two Solutions)

Solution 1:

When I downloaded my VSTO program to a new computer from a github branch and attempted to build the project again I received the error: “Unable to find manifest signing certificate”.

I did some Google searching and found the stack overflow article with the proper solution.

Once I deleted the code lines I was able to build the project.

Solution 2:

Alternatively, you can create a Test Certificate from the Signing tab in the Project Properties since I was also unable to “Select from Store…” to get a signing certificate.

Deploy a VSTO addin (again…) and what we learned

I was moving computers and had to install Visual Studio again to keep working on my VSTO addin. The setup project was not recognized and I was not able to open it.

MSDN has a perfect article that helped me remember how to Get InstallShield Limited Edition installer file.

This article discusses the order of things going on and methods called in what order of your VSTO addin

Once installed I was able to view the setup project again.

Unifi upgrade nightmare

Well – if you are looking to make money – suggest the Ubiquity Networks Unifi product. You can spend hours configuring it and getting seemingly no where. Then… it works. ARGGGG. OK – here are some lessons learned. Once it is up – the system rocks out. $120 or $180 (5G) per device – can’t go wrong but the setup – still cheaper than the big guys and it is easy to configure once a device is adopted. The stats are out of this world awesome and you can lock people out, VLAN etc. Here are more links that I wrote.

First, save a backup. Of course mine didn’t work – so nightmare again. I installed this at the other campus of this customer and it too was a nightmare.  Here are the steps Continue reading

AutoCAD – Selection Sets are useless for command line from VBA but groups… that’s new …

First, did you know you can type VBA from the command line? Use the vbstmt command. Here is how to clear all groups. But getting back to the topic at hand. So named Selection Sets – you can’t use them. For example – using the CHSPACE command initiated by VBA (SendCommand) asks for a selection – well you cannot use the selection set name – you CAN group that selection set and name it with an UPPER CASE (did I say UPPERCASE?) name and when you are prompted for a selection – then give it the GROUP name. Then delete your group and you have what you need. You can even see the grips! Here is how I figured it out – these guys helped a lot.

Continue reading