SELECT * FROM table WHERE tablefield IN ( SELECT tablefield FROM table GROUP BY tablefield HAVING (COUNT(tablefield ) > 1) )
Code signing VB Macros
To digitally sign your VB macros you can of course use a self signed certificate, but that will not allow other users to use your macros unless they install your certificate as a trusted root certificate.
Certum.eu offers open source code signing certificates for free. Just apply here https://sklep.unizeto.pl/ankieta/form.php and make sure you use ‘Open Source Devloper’ as the company name.
To prevent your signature to become invalid after the Code Signing Certificate has expired (requiring you to re-sign the VB macro) you might want to include a timestamp in the signature. To do so, import the following Registry Key to enable automatic timestamping every time you save the Macro.
Key Name: HKEY_CURRENT_USER\Software\Microsoft\VBA\Security Class Name: <NO CLASS> Last Write Time: 5-11-2012 - 12:00 Value 0 Name: TimeStampURL Type: REG_SZ Data: http://time.certum.pl
Value 1 Name: TimeStampRetryCount Type: REG_DWORD Data: 0x5
Value 2 Name: TimeStampRetryDelay Type: REG_DWORD Data: 0xa
How do you generate unique identifiers with vbscript?
Simple, most of the hard work has already been taken care of, all you need to do is call this code when you need to:
Function GetGuid()
Set TypeLib = CreateObject("Scriptlet.TypeLib")
GetGuid = Left(CStr(TypeLib.Guid), 38)
Set TypeLib = Nothing
End Function
dim SomeValue : SomeValue = GetGuid()
The above code will create a GUID object, shorten it to 38 chars and then give you the result! All you have to do is save it in the database.
While some databases allow you to create these values automatically, this simple function provides a way for those that do not (such as simple xml).
Enable Excel developer mode
Show the Developer tab or run in developer mode
You should display the Developer tab or run in developer mode when you want to write macros, run macros that you previously recorded, or create applications to use with Microsoft Office programs.
Do the following in these Microsoft Office programs:
Excel, PowerPoint, or Word
- Click the Microsoft Office Button
, and then click Excel Options, PowerPoint Options, or Word Options. - Click Popular, and then select the Show Developer tab in the Ribbon check box.
Note The Ribbon is part of the Microsoft Office Fluent user interface
Outlook
- On the Tools menu, click Options.
- On the Other tab, click Advanced Options, and then select the Show Developer tab in the Ribbon check box.
Visio
- On the Tools menu, click Options.
- On the Advanced tab, under Advanced Options, select the Run in developer mode check box.
Link to document in edit mode from email
Today I found out that if you want to link to a document in Sharepoint from an e-mail (like a workflow e-mail) and don’t want the document to be downloaded when the link is clicked (standard behaviour), you need to set the versioning settings of that document library to always require check-out. That will force the users to open the documents in edit mode.
Link to ‘edit item’ view in Sharepoint 2007
If you are using a workflow to send emails to users and want to include a direct link to the ‘edit item’ view of the current item, include a link like this:
For document libraries:
http://servername/Test%20Doc%20Lib/Forms/EditForm.aspx?ID=1
For lists:
http://servername/Test%20Doc%20Lib/EditForm.aspx?ID=1
If you want a link to the ‘view properties’ screen, replace EditForm.aspx by DispForm.aspx.
Howto use the People Picker to add recepients to an e-mail sent by a workflow in Sharepoint 2007
Today I was facing the question of how to allow members of a Sharepoint site to pick specific recipients while initiating a workflow.
Using Sharepoint Designer 2007 it is possible to show a workflow initiation form to collect information to be used in the workflow, but it does not come with an option to pick certain recipients. A workaround this problem is to insert a variable in the ‘Initiation’ settings of a workflow. The type of this variable should be set to ‘single line of text’ and in the workflow action where you are sending the e-mail, you set the recipient to be looked up from the ‘workflow data’ and then select the variable name you defined in the workflow initiation settings.
After finishing the workflow you will have 3 files that together make up the workflow. One file ends with .aspx, this is your initiation form (which is shown to the user when the workflow is started). We are going to edit this form to include the people picker, but remember, if you edit the initiation settings of the workflow again, the form will be overwritten, so save this action to the last, after the workflow is finished.
- Open the .aspx file of the initialization form
- Include the following code on the top of the page (before <asp:content id=”content2″…)
<asp:Content ID="content1" runat="server" ContentPlaceHolderID="PlaceHolderAdditionalPageHead">
<script type="text/javascript">
function SetPeopleValues(txtbox, pplbox)
{
txtbox = 'ctl00_PlaceHolderMain_InitiationForm_' + txtbox;
pplbox = 'ctl00_PlaceHolderMain_InitiationForm_' + pplbox + '_downlevelTextBox';
var people = document.getElementById(pplbox).value;
document.getElementById(txtbox).value = people;
}
</script>
</asp:Content>
- Copy the following code before the textbox that you want to complete with the people-picker (By clicking on the box in the split-viewer the curser goes to the associated place in the code)
<SharePoint:PeopleEditor
ID="pplPicker"
runat="server"
AllowEmpty="True"
Rows="1"
MultiSelect="False"
onvaluechangedclientscript="SetPeopleValues('ff1_1', 'pplPicker')">
</SharePoint:PeopleEditor>
- AllowEmpty= specifies if the field is required or not
- Rows defines the size of the people picker field
- MultiSelect defines if multiple recipients can be selected
- Give the ID of the copied code a unique name
- Update the line “SetPeopleValues (‘…’,’…’) with the registration name of the textbox (of the type ffX_1) and with the chosen ID name
- Including style=”visibility:hidden” as a parameter on the text field hides the field