Document Level

Also sometimes referred to as Code Behind.
Consists of a single assembly associated with a single workbook, document or template.
The code is inside an assembly that is then linked to the particular office file.
The assembly is loaded when the associated document is opened.


Important Facts

All projects are created in the following folder by default: My Documents > Visual Studio XX > Projects
Does not require a registry entry
Full trust is not granted automatically when creating 2003 documents on your development PC.
The link between the two can be found in the Custom properties for that workbook.
To deploy and run a Microsoft Office solution you must grant full trust to the assembly in the security policy of each end user or cimputer.
The workbook or document must be loaded into a trusted location
A fully qualified document path must be added to the list of trusted locations in Office when a document level solutions are stored on a network drive or server.
http://www.codeproject.com/KB/showcase/IMSLcsWithExcelAndVSTO.aspx


Creating Document Level Solutions

The very first time you create a VSTO project for Excel or Word the following prompt will appear telling you that the project must have access to the Visual Basic for Applications project
SS
You must click OK to create the VSTO project.
VSTO does not actually use Visual Basic for Applications but it requires access to the VBA Project in order to support controls on the document.


Every host item and host control has a field called Tag
This Tag field has been designed for developers to store data associated with a particular control



Developer Tab - Macros and Controls groups are not displayed inside the VS Editor
What happens when you close the designer - "Excel designer cannot be activated"


Could try - regsvr32 c:\windows\system32\urlmon.dll
Could try - reinstalling Visual Studio Tools for Office Runtime-SE (vstor.exe)
http://msdn2.microsoft.com/en-us/library/ms178739.aspx
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/e4f5d62d-411d-47a6-a616-89cdb96e1f7a/
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/19d0aade-c576-4ac9-89a1-94bb16500fd0/


Deployment to a shared folder:
\\londonfs1\adminfiles\InvestOneSheets\Marketing Factsheets\Deployment\
Running the setup.exe wil automatically prompt and install :
1) .NET Framework 3.5
2) Visual Studio Tools for Office Runtime 3.0
(then then require a reboot)



Folder Path not a Microsoft Office trusted location
Running a solution from a folder which is different to the debug folder where the project was built from.

customization does not have permissions required to create an application domain
http://msdn.microsoft.com/en-us/library/6exya3kf(VS.80).aspx


The setup project takes care of everything except document security. If a document is opened from a location other than the local hard disk then it needs to be in the Office Trusted Folder list.



When trying to create a new workbook and link it to an existing Assembly
Add the 2 document properties
Add the button control onto the worksheet


Adding Customisation to an Existing File

You can attach a VSTO solution assembly to an existing 2007 Excel or Word document.
If you attach a solution assembly to a document that is not already associated with a VSTO customisation then the VSTO runtime will automatically create a Runtime Storage Control in the document.
You can attach a VSTO assembly to an uncustomised file in two ways:
1) manually add the two document properties. Add a value of asterisk to the _AssemblyName. Add the location of the deployment manifest to the _AssemblyLocation, save, close and reopen the file. This works in 2007.
2) Using the ServerDocument class using the AddCustomisation method.
For more details, please refer to the ServerDocument page.



Deploying

The default behaviour is to have the assembly in the same directory as the office file.



Creating

Open Visual Studio
(File > New Project)
Select Visual Basic Projects
Select the Office Category
Select Excel Workbook


Notice how it creates the ThisWorkbook and Globals classes


Globals -
ThisApplication -
ThisWorkbook -



In these added modules you cannot reference the ThisWorkbook object directly.
You will need to declare a variable in your Globals class.



Soluton Folder

This contains two elements
Another folder -
a .sln file for your solution


A second folder holds three subfolders
bin - includes the .xls and a .dll
my project - has all the files you see in the Solution Explorer
obj - stores a debug section and a release section


If you want to re-open the solution to work on it you can double click the Project file (.vbproj)
It is usually better though to open the solution (since a solution can contain more than one project)


Creating New Workbooks


You can interact with the Excel application at design time directly from within Visual Studio.
This is because VSTO is integrated with Visual Studio.



 



Solution Files


The solution contains at least two source files
AssemblyInfo.vb - which stores all the assembly level meta-data
ThisWorkbook.vb - containing a single class. contains important events such as Startup and Shutdown.



 


One way to view the new objects is to look at the ClassDiagram.cd file


You need to the Globals class when you want to refer to ThisWorkbook from "outside" the ThisWorkbook.vb file.




 




Replacing a Workbook

When you create a document level Excel solution an Excel workbook (.xls) is created in the main project directory.
This is the workbook that is displayed in the Visual Studio Designer.
It is this workbook that is distributed when the solution is installed.
It is this workbook that is loaded when you are debugging.


If you need/want to update this workbook you can by following these steps:
1) Get the new workbook
2) Remove the customisations from this new workbook either manually or programmatically using the RemoveCustomisation methods provided by the VSTO Runtime
3) Make sure the project is closed
4) Replace the current workbook with this new one.


Which method you use depends on whether you want to do it at runtime or from a file on server that doesn't have Office installed.



Application Manifest

This is embedded into the Excel file.




Removing VSTO assembly from 2007 customisations

You can programmatically remove the VSTO customisation assembly from a document or workbook that is part of a 2007 document-level solution.
This can be achieved by using one of the RemoveCustomisation methods provided by the VSTO runtime.
== there is a article on MSDN for this

Workbook.RemoveCustomisation() 
ServerDocument.RemoveCustomisation("C:\temp\name.xlsx")



Permissions


You will want to set the user's security policy so that the solution is fully trusted.
Right click the solution and add a CustomSetup project
Select Properties and change the output type to a Class Library.
(Add > New Item > Installer Class)


Adding Custom Actions


Imports System.Collections 
Imports System.ComponentModel
Imports System.Configuration.Install
Imports System.Security
Imports System.Security.Policy
Imports Microsoft.VisualStudio.Tools.Applications.Runtime

Public Class SecurityInstaller
  Inherits Installer

  Public Sub New()
    MyBase.New()

'This call is required by the Component Designer.
    InitializeComponent()
  End Sub

  Public Overrides Sub Install( _
    ByVal stateSaver As System.Collections.IDictionary)

    Dim enterprisePolicyLevel As PolicyLevel
    Dim machinePolicyLevel As PolicyLevel
    Dim userPolicyLevel As PolicyLevel
    Dim assemblyGroup As CodeGroup
    Dim assemblyCondition As UrlMembershipCondition
    Dim policyStatement As PolicyStatement
    Dim fullTrust As PermissionSet
    Dim assemblyPath As String = Me.Context.Parameters("custassembly")

' Obtain the three policy levels:
    Dim policyEnumerator As IEnumerator
    policyEnumerator = SecurityManager.PolicyHierarchy()

    policyEnumerator.MoveNext()
    enterprisePolicyLevel = CType(policyEnumerator.Current, PolicyLevel)

    policyEnumerator.MoveNext()
    machinePolicyLevel = CType(policyEnumerator.Current, PolicyLevel)

    policyEnumerator.MoveNext()
    userPolicyLevel = CType(policyEnumerator.Current, PolicyLevel)

' Create a new group by combining a permission set with a
' membership condition:
    fullTrust = userPolicyLevel.GetNamedPermissionSet("FullTrust")
    policyStatement = New PolicyStatement(fullTrust, PolicyStatementAttribute.Nothing)
    assemblyCondition = New UrlMembershipCondition(assemblyPath)
    assemblyGroup = New UnionCodeGroup(assemblyCondition, policyStatement)

' Add the new policy to the root:
    userPolicyLevel.RootCodeGroup.AddChild(assemblyGroup)
    SecurityManager.SavePolicy()

    MyBase.Install(stateSaver)
  End Sub

End Class



Rolling Back


How to role back a document level customisation deployed with publish wizard


Open the installation location of the solution
Delete the current deployment manifest (.vsto)
Open the application files directory and open the folder of the version you want to role back to
Copy the deployment manifest (.vsto) file from this folder and paste it in the installation folder
The next time the solution is opened it will check for updates/changes and the earlier version will be used.


Important

For example the hidden classes behind Document VSTO solutions use partial classes as a way of seperating the auto-generated code from the code you write.
The value of a custom document property can only contain 255 characters. If the entry needs to be longer than 255 characters the create another property called _AssemblyLocation0. Set this value to the first 255 characters and then create another property called _AssemblyLocation1 for the remaining characters.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext