Reference | yadUK - Effective Information System Solutions

Archive for 'Reference'

Apr201001

“NOT” – Yaduk’s Music Profile – Users at Last.fm

How bizarre! From link searching I came across a profile on Last.fm with ‘Yaduk’ rather as titled yadUK. Some damn good google lurve on the seach term yaduk as one might expect!?

A quick wiggle of the ‘ol mouse finger and I’m in a strange parallel universe with a twist and an unknown music list. Now, whilst there’s some not bad music in that list … I categorically state that ain’t me! ;)

The Internet. What can one say?

Mar201016

Exporting SharePoint People & Groups

You can export the “People & Groups” section to an XL spreadsheet by hinging through the owssvr DLL. However, you’re going to need the SSID for both your ‘List’ and whichever ‘View’ also to get it to work.

To obtain both of these:

List SSID – Goto the “People & Groups” list you want to export and goto “Settings >> List Settings”. Then click on “Create Column”. From there you see in the URL fldNew.aspx?List=%7B blah %7D

^^ Take the long string between the %7B and %7D at either end. Do not take the %7B nor the %7D at either end <– important!

View SSID – just goto your “People & Groups” list and choose the “Detail View” which will initiate it and you’ll get in the URL blahblah&View={the SSID}

^^ Take the SSID without the curly braces this time and place them in the following URL string at the appropriate places with your SITEURL also:

http://[SITEURL]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl=1

From there you’ll get an export of the contents of your group from which you can copy ‘n paste the email addresses into your new site.

You’ll need to create a new XL column and concatenate the email address with a semi-colon after it prior to pasting the emails in.

Jan201030

BCS – Social Networking Article

Fantastic article in the latest ITNow magazine in regards to Social Networking ‘in the business’. Simon Morris of Pentura lays down some great pointers on how best to manage and secure such systems against their use in collaborative and strangely ‘Social Networking’ activities.

Do look back to a post here: Building an online community organisational Intranet where a discussion on ‘Social Networking’ within your own organisational Intranet establishes collaborative activites enabling your business rather as hindering!

Further articles are available at http://www.bcs.org/socialmedia

Aug200918

Restart haldaemon and dbus Commands for CentOS 5.2

/etc/init.d/messagebus restart
/etc/init.d/haldaemon restart

Aug200903

Javascript English Stoplists / Stopwords

Not listed verbs, adjectives nor articles but the below arrays are very handy should one be running language based queries over rendered pages.

Why? I dunno … say you’re trying to make a tag cloud or something but it’s full of filler words!

Articles are “a”, “an” and “the” only for English. Verbs and adjectives, well that’ll be down to what you’re up to I guess?!

var prepositions = ["aboard","about","above","across","after","against","along","amid","among","anti","around","as","at","before","behind","below","beneath","beside","besides","between","beyond","but","by","concerning","considering","despite","down","during","except","excepting","excluding","following","for","from","in","inside","into","like","minus","near","of","off","on","onto","opposite","outside","over","past","per","plus","regarding","round","save","since","than","through","to","toward","towards","under","underneath","unlike","until","up","upon","versus","via","with","within","without"]

var conjunctions = ["after","although","and","as","as far as","as how","as if","as long as","as soon as","as though","as well as","because","before","both","but","either","even if","even though","for","how","however","if","if only","in case","in order that","neither","nor","now","once","only","or","provided","rather than","since","so","so that","than","that","though","till","unless","until","when","whenever","where","whereas","wherever","whether","while","yet"]

var pronouns = ["all","another","any","anybody","anyone","anything","both","each","each other","either","everybody","everyone","everything","few","he","her","hers","herself","him","himself","his","I","it","its","itself","little","many","me","mine","more","most","much","myself","neither","no one","nobody","none","nothing","one","one another","other","others","ours","ourselves", "several","she","some","somebody","someone","something","that","theirs","them","themselves","these","they","this","those","us","we","what","whatever","which","whichever","who","whoever","whom","whomever","whose","you","yours","yourself","yourselves"]

Jul200922

Javascript Export HTML table to MS Excel (JScript)

Here’s a little code snippet to export an HTML table to MS Excel through utilising ActiveXObject(“Excel.Application”) from Internet Explorer.

The JavaScript (in this case utilising Microsoft’s JScript) runs over the table displayed pulling out the relevant data and exporting into the instantiated copy of Excel. This is a working example based loosely on Microsoft’s http://support.microsoft.com/kb/234774 Knowledgebase Article “How to automate Excel from an HTML Web page by using JScript”.

Worth noting is that the garbage collection between the JScript and MS Excel doesn’t work too well and more often than not one can be left with the instantiated copy of MS Excel still in memory (see it in the Task Manager). One must manually remove these instances.

Perhaps dynamically inputting a module to clean up from within MS Excel on the workbook deactivating might clear this up – not tried this though!?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Export to Excel Example</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="charset" content="iso-8859-1" />
<meta http-equiv="content-language" content="english" />
<meta http-equiv="expires" content="-1" />
<meta http-equiv="Pragma" content="no-cache" />
<meta http-equiv="imagetoolbar" content="false" />
<style>
body {
   margin: 0;
   font-family: verdana,arial,helvetica,sans-serif;
   font-size: .85em;
   padding: 3px;
}
th {background-color:#ccd;}
.odd{background-color:#DDDBDB;}
.even{background-color:#CECCCC;}

</style>
<script language="javascript" type="text/javascript">  
function ExportToExcel() {
	input_box=confirm("Export rows of table data to MS Excel?");
		if (input_box==true) {
			
			var xlApp = new ActiveXObject("Excel.Application");
			// Silent-mode:
			xlApp.Visible = true;
			xlApp.DisplayAlerts = false;
			var xlBook = xlApp.Workbooks.Add();
			xlBook.worksheets("Sheet1").activate;
			var XlSheet = xlBook.activeSheet;
			XlSheet.Name="JavaScript Export to Excel";
			
			// Store the sheet header names in an array
			var rows = tabletoexport.getElementsByTagName("tr");
			var columns = tabletoexport.getElementsByTagName("th");
			var data = tabletoexport.getElementsByTagName("td");
			
  			// Set Excel Column Headers and formatting from array
			for(i=0;i<columns.length;i++){
	   			XlSheet.cells(1,i+1).value= columns[i].innerText; //XlSheetHeader[i];
	   			XlSheet.cells(1,i+1).font.color="6";
	   			XlSheet.cells(1,i+1).font.bold="true";
	   			XlSheet.cells(1,i+1).interior.colorindex="45";
			}
			
			//run over the dynamic result table and pull out the values and insert into corresponding Excel cells
			var d = 0;
			for (r=2;r<rows.length+1;r++) { // start at row 2 as we've added in headers - so also add in another row!
				for (c=1;c<columns.length+1;c++) {
					XlSheet.cells(r,c).value = data[d].innerText;
					d = d + 1;
				}
			}
			
			//autofit the columns
			XlSheet.columns.autofit;
			
			// Make visible:
			xlApp.visible = true;
			xlApp.DisplayAlerts = true;
			CollectGarbage();
			//xlApp.Quit();
		}
}
</script>
</head>
<body>
	<input type="button" value="Export To Excel" onClick="ExportToExcel()">
	<table border="1" width="100%" id="tabletoexport">
		<tr>
			<th>Month</th>
			<th>Column 1</th>
			<th>Column 2</th>
			<th>Column 3</th>
			<th>Column 4</th>
		</tr>
		<tr class="even">
			<td>January</td>
			<td>123</td>
			<td>456</td>
			<td>798</td>
			<td>123</td>
		</tr>
		<tr class="odd">
			<td>February</td>
			<td>456</td>
			<td>798</td>
			<td>123</td>
			<td>456</td>
		</tr>
		<tr  class="even">
			<td>March</td>
			<td>798</td>
			<td>123</td>
			<td>456</td>
			<td>789</td>
		</tr>
		<tr class="odd">
			<td>April</td>
			<td>123</td>
			<td>456</td>
			<td>789</td>
			<td>123</td>
		</tr>
		<tr  class="even">
			<td>May</td>
			<td>456</td>
			<td>789</td>
			<td>123</td>
			<td>456</td>
		</tr>
		<tr class="odd">
			<td>June</td>
			<td>789</td>
			<td>123</td>
			<td>456</td>
			<td>789</td>
		</tr>
		<tr class="even">
			<td>July</td>
			<td>123</td>
			<td>456</td>
			<td>789</td>
			<td>123</td>
		</tr>
		<tr class="odd">
			<td>August</td>
			<td>456</td>
			<td>789</td>
			<td>123</td>
			<td>456</td>
		</tr>
		<tr  class="even">
			<td>September</td>
			<td>789</td>
			<td>123</td>
			<td>456</td>
			<td>789</td>
		</tr>
		<tr class="odd">
			<td>October</td>
			<td>123</td>
			<td>456</td>
			<td>789</td>
			<td>123</td>
		</tr>
		<tr class="even">
			<td>November</td>
			<td>456</td>
			<td>789</td>
			<td>123</td>
			<td>456</td>
		</tr>
		<tr class="odd">
			<td>December</td>
			<td>789</td>
			<td>123</td>
			<td>456</td>
			<td>789</td>
		</tr>
	</table>
</body>
</html>

Jul200920

Export Access to Excel with Custom Headers

Have you noticed how the “out the box” solution to export your dataset from MS Access to MS Excel doesn’t give much room to give custom headers to your worksheet?

Through utlising ActiveX you can instantiate a copy of MS Excel in memory to add in your own custom headers etc. prior to exporting your MS Access dataset into the worksheet.

The below code example shows you how to add 5 custom headers to your sheet. Just add in your own SQL query where it is indicated.

Private Sub btn_export_excel_Click()
   
    Dim mySQL, thepos, theleft, theright, test As String
    Dim i, iNumCols  As Integer
    Dim DB As DAO.Database
    Dim rs As DAO.Recordset
    
    Set DB = CurrentDb
    
    With Me.yourformname.Form.RecordsetClone
        If .RecordCount > 0 Then .MoveLast
        test = .RecordCount
    End With
    
    If test < 65535 Then
        'create output table and output
        If MsgBox("Export selected data?", vbOKCancel, "Export . . ?") = vbOK Then
            
            Dim oApp As New Excel.Application
            Dim oBook As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            Dim oRange As Excel.Range
			
			mySQL = "enter in whatever youSQL query is here"
                  
            Set rs = DB.OpenRecordset(mySQL, dbOpenSnapshot)
            Set oBook = oApp.Workbooks.Add
            Set oSheet = oBook.Worksheets(1)
            
            oSheet.Cells(1, 1).Value = "HEADER 1"
            oSheet.Cells(2, 1).Value = "HEADER 2"
            oSheet.Cells(3, 1).Value = "HEADER 3"
            oSheet.Cells(4, 1).Value = "HEADER 4"
            oSheet.Cells(5, 1).Value = "HEADER 5"
            
            'Format the header
            oSheet.Cells(1, 1).Font.Bold = True
            
            With oSheet
                Set oRange = .Range("A5:AL5")
            End With
            
            With oRange
                .MergeCells = True
            End With
           
            'Add the field names in row 7
            iNumCols = rs.Fields.count
            For i = 1 To iNumCols
                oSheet.Cells(7, i).Value = rs.Fields(i - 1).Name
            Next
            
            'Add the data starting at cell A8
            oSheet.Range("A8").CopyFromRecordset rs
            
            'Format the header row as bold and autofit the columns
            With oSheet.Range("a7").Resize(1, iNumCols)
                .Font.Bold = True
                .EntireColumn.AutoFit
            End With
            oSheet.Columns("A:A").ColumnWidth = 10.14
            
            oApp.Visible = True
            oApp.UserControl = True
            
            'make sure we clean up the instantiated Excel app!!
            'oApp.Quit
            Set oApp = Nothing
            Set oBook = Nothing
            Set oSheet = Nothing
            rs.Close
            DB.Close
            
        End If
    Else
        MsgBox "Sorry - your export is larger than what can be held within Excel!" & vbCrLf & vbCrLf & " Please consider revising your dataset.", vbCritical, "Export Error"
    End If
    
End Sub

Jun200930

Script to set IE – Internet Explorer homepage

Stuck in a restrictive environment where you can’t alter your homepage settings? Check out the below WSH shell script to alter your homepage in Internet Explorer via the registry:

Set WSHShell = WScript.CreateObject(“WScript.Shell”)
‘ home page URL
StartPage = “http://www.yaduk.co.uk”
WSHShell.RegWrite “HKLM\Software\Microsoft\Internet Explorer\Main\Start Page”, StartPage
WSHShell.RegWrite “HKCU\Software\Microsoft\Internet Explorer\Main\Start Page”, StartPage

Stick in a text file with the extension .vbs

Jun200924

XHR Connector for Internet Explorer

Below is a great code snippet to process XML dynamically through an XMLHttpRequest (XHR) request. From this further XSLT processing can be applied dynamically utilising Internet Explorers “fragment” transformations.

/* XHR Connector */
XMLReq = function() {
	this.adapter = false;
	/*@cc_on @*/
	/*@if (@_jscript_version >= 5)
		try {
			this.adapter = new ActiveXObject("Msxml2.XMLHTTP");
		} catch (e) {
			try {
				this.adapter = new ActiveXObject("Microsoft.XMLHTTP");
			} catch (E) {
				this.adapter = false;
			}
		}
	@end @*/
	if (!this.adapter && typeof(XMLHttpRequest) != 'undefined') {
		this.adapter = new XMLHttpRequest();
	}
};
XMLReq.prototype.adapter = null;
XMLReq.prototype.getDocument = function(sUrl) {
	this.adapter.open("GET", sUrl, false);
	this.adapter.send(null);
	//alert("Status = " + this.adapter.status + "n"+this.adapter.getAllResponseHeaders()+"nParse Error = " +this.adapter.responseXML.parseError.errorCode);
	// if XML sent as application/octet-stream!! you can't use responseXML in this instance instead must use responseBody
	return this.adapter.responseBody;
};

/*  The actual  */
var yourprocessorfunction = function() {};
var nameofcallvariable = new yourprocessorfunction();
yourprocessorfunction.prototype.data = null;
yourprocessorfunction.prototype.init = function() {
	// init vars
	this.data = this.XMLReq.getDocument("http://whereeveryourxmldocis");
};
/* Init */
function start() {
	nameofcallvariable.init();
}
window.onload = start;

May200926

Excel Split Firstname Lastname 2 Columns

Excellent post in the MSDN microsoft.public.excel.programming community regarding the splitting of a firstname and lastname into two seperate columns. The honours go to someone called “Bam”.

Last Name
=IF(ISERROR(LEFT(B2,FIND(“,”,B2,1)-1)),RIGHT(B2,LEN(B2)-(FIND(” “,B2,1))),LEFT(B2,FIND(“,”,B2,1)-1))

First Name
=IF(ISERROR(RIGHT(B1,LEN(B1)-(FIND(“,”,B1,1)+1))),LEFT(B1,FIND(” “,B1,1)+1-1),RIGHT(B1,LEN(B1)-(FIND(“,”,B1,1)+1)))

Archive for 'Reference'

Apr201001

“NOT” – Yaduk’s Music Profile – Users at Last.fm

Mar201016

Exporting SharePoint People & Groups

Jan201030

BCS – Social Networking Article

Aug200918

Restart haldaemon and dbus Commands for CentOS 5.2

Aug200903

Javascript English Stoplists / Stopwords

Jul200922

Javascript Export HTML table to MS Excel (JScript)

Jul200920

Export Access to Excel with Custom Headers

Jun200930

Script to set IE – Internet Explorer homepage

Jun200924

XHR Connector for Internet Explorer

May200926

Excel Split Firstname Lastname 2 Columns

« Older Entries

Login

Recent Developments

Categories

Recent Comments

External Links

Archives

Most Popular Articles

Archive for 'Reference'

Apr201001

Mar201016

Jan201030

Aug200918

Aug200903

Jul200922

Jul200920

Jun200930

Jun200924

May200926

Tags

Login

Recent Developments

Categories

Recent Comments

External Links

Archives

Most Popular Articles

Subscribe to RSS