Reference | yadUK - Effective Information System Solutions

Archive for 'Reference'

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)))

May200915

Hyperion Essbase – Excel – Auto-connect

A few folk have asked how to just connect automatically to Essbase from Excel VBA. Please find below a simple VBA sub stripped back to show this. Use the following to initialise with your own details in place.

Call initialise("xxx","xxx","xxx","xxx","xxx")

May200913

JavaScript GET URL Parameter GUP JQuery

Having used GUP from Netlobo http://www.netlobo.com/comments/url_query_string_javascript for some time to pull back parameters from the URL in JavaScript for fiddling with, I decided to create a handy JQuery extension for reuse.

Extending this further with the gpn section to get all parameters, which is also from their website, you’ll find within the below code the handy gpnv now too. The gpnv section pulls down all URL parameters and their values storing them in a handy 2D JavaScript array for manipulation.

/**
* @title	GUP"GET URL Parameter" - jQueried
* @author	John Smith (at) yaduk.co.uk
* @concept	http://www.netlobo.com/comments/url_query_string_javascript	
* @examples	value = $.gup("paramname");
*			-------------------------------------
*			value = $.gpn();
*			-------------------------------------
*			var test = new Array;
*			test = $.gpnv();
*			var oString ="";
*			for (var i=0;i < test.length;i++){
*				oString = oString + " " + test[i,i];
*			}
*			alert(oString);			
*/
jQuery.extend({
	gup: function(param){
		var param = param.replace(/[[]/,"[").replace(/[]]/,"]"); 
		var regexS = "[?&]"+param+"=([^&#]*)";  
		var regex = new RegExp(regexS);  
		var results = regex.exec(window.location.href);  
		if(results) { 
			return results[1];
		}
	},
	gpn: function(){
		var params = new Array( );
		var regex = /[?&]([^=]+)=/g;
		while((results=regex.exec(window.location.href)) != null)
			params.push(results[1]);
		return params;	
	},
	gpnv: function(){
		var oString = new Array();
		var values = new Array();
		values = $.gpn();
		for (var i=0;i < values.length;i++){
			oString[i] = new Array(i);
			oString[i,i] = [values[i],$.gup(values[i])];
		}
		return oString;
	}
});

Archive for 'Reference'

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

May200915

Hyperion Essbase – Excel – Auto-connect

May200913

JavaScript GET URL Parameter GUP JQuery

« Older Entries

Login

Recent Developments

Categories

Recent Comments

External Links

Archives

Most Popular Articles

Archive for 'Reference'

Jan201030

Aug200918

Aug200903

Jul200922

Jul200920

Jun200930

Jun200924

May200926

May200915

May200913

Tags

Login

Recent Developments

Categories

Recent Comments

External Links

Archives

Most Popular Articles

Subscribe to RSS