Reference | yadUK - Effective Information System Solutions

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

Apr200928

Use Javascript to find which render mode browser is in

When designing pages utilising CSS for their styling and splitting the semantic content away in an attempt to move legacy systems into the dot com era it might be necessary to show why kicking a browser into CSS Compatability mode might be a good idea. Not least that you can position elements as you see fit and can move away finally from table based layouts.

Though how are you physically able to show a change having taken place to management other than through overkill style demo pages or pointing with your finger ..? Using the below code by simply pasting it into the URL address bar and launching by pressing the enter key, you will receive a dialog box detailing which CSS rendering mode you are in.

BackCompat = Backwards Compatibility (quirks mode)
CSS1Compat = Standards Compliance mode.

Quick and simple! This hidden Javascript browser gem and further great insights can be found at quirksmode.org “for all your browser quirks”. A fantastic resource on all things browser based!

Apr200926

Interactive BATCH file with DEBUG script

Still sometimes need to use batch files to do stuff even in this day and age? Yes, is the usual answer but, unfortunately many of the standard errorlevel checking tools such as CHOICE etc. have either been removed or cannot be used without administrator access in Enterprise environments.

It’s not always entirely wise either to install compiled errorlevel tools into an Enterprise environment. This will potentially go against an application or network policy or you’re just plain unable to.

The below batch file instead is self-contained outputting a DEBUG script before compiling it and utilising it to check for user input. This it utilises within the demo. Save the text down to a textfile and give it the extension .bat

It is an extension of and use of the following Microsoft support site example - Accepting Keyboard Input in Batch Files. Note that there should be a backslash after %temp% and before the word REPLY which is being stripped out by this parser.

@echo OFF
	if not exist %Temp%keyc.COM GOTO CREATETOOL
:START
	cls
	echo Dynamic Key Checker .bat
	echo .................................
	echo.
	echo Do you want to continue?
	echo (Y) Yes
	echo (N) No
	echo (X) eXit
	echo (y/n/x)?
	keyc
	if errorlevel 121 if not errorlevel 122 GOTO YES
	if errorlevel 89 if not errorlevel 90 GOTO YES
	if errorlevel 110 if not errorlevel 111 GOTO NO
	if errorlevel 78 if not errorlevel 79 GOTO NO
	if errorlevel 88 if not errorlevel 89 GOTO EXIT
	if errorlevel 120 if not errorlevel 121 GOTO EXIT
	GOTO START
:YES
	echo You pressed "Yes"
	GOTO END
:NO
	echo You pressed "No"
	GOTO END
:EXIT
	echo You pressed "eXit"
	GOTO END
:CREATETOOL
:: This is a simple errorlevel tool created in DEBUG
	>> %TEMP%~keyc echo A 100 
	>> %Temp%~keyc echo MOV AH,08 
	>> %Temp%~keyc echo INT 21 
	>> %Temp%~keyc echo CMP AL,0 
	>> %Temp%~keyc echo JNZ 010A 
	>> %Temp%~keyc echo INT 21 
	>> %Temp%~keyc echo MOV AH,4C 
	>> %Temp%~keyc echo INT 21 
	>> %Temp%~keyc echo. 
	>> %Temp%~keyc echo RCX 
	>> %Temp%~keyc echo E 
	>> %Temp%~keyc echo N keyc.COM 
	>> %Temp%~keyc echo W 
	>> %Temp%~keyc echo Q 
	echo. 
	DEBUG < %Temp%~keyc
	if not errorlevel 0 GOTO DEBUGERROR
	GOTO START
:DEBUGERROR
	set error=DEBUG ERROR !!
	GOTO ERROR
:END
	echo Press any key to exit . . . 
	pause > nul

Apr200925

Hyperion Essbase -> Excel -> Access -> automagic

Accessing Hyperion Essbase is no problem directly from their MS Excel plugins. Though what happens if one wants to import on-the-fly and put that into MS Access so you can deliver your MI: Management Information, alongside current data without having to analyse and alter Hyperion Essbase output in Excel before exporting to textfile format or CSV before manipulation?

Well, why not try the below code!

The below function run within MS Access will write an “Essbase connection plugin” before instantiating a copy of MS Excel to run Essbase from. Unfortunately if you don’t write out and pull in the textfile to create a module in Excel as is within the code Microsoft’s security permissions now deny access. Give them their due – it was a good move for certain issues.

After the “plugin” is pulled into the MS Excel workbook it fires a connection to Essbase and in this case draws down and partitions the relevant info to worksheets. Note the code will require setting up against whatever Essbase fields you require from your system.

The code as you will see at the bottom will clear down any previous retrieval before using MS Access’ TransferSpreadsheet import functionality to pull in the retreived workbook. The code also requires a table within MS Access to hold your Hyperion Essbase login details. In this case the table is called tbl_essbase_credentials and can be seen within the code. The Excel workbook is cleared down and you are left with your Hyperion Essbase MS Excel retrieval in your MS Access database.

Way to go!

So the process from MS Access is to run function and -> create a module for import into MS Excel -> start a virtual copy of MS Excel -> import the module into the virtual copy of MS Excel -> run virtual module to connect to Hyperion Essbase and download required data -> save MS Excel workbook and transfer the spreadsheet into MS Access -> clean up. All from within MS Access!

Function Essbase()
    Dim sql, test, row, theyear As String
    Dim retval As Long
    Dim a, i, iNumCols, counter As Integer
    Dim fld As Field
    Dim db As DAO.database
    Dim rs As DAO.Recordset
    
    'Start a new workbook in Excel - note db reference "Microsoft Excel 11.0 Object Library" included
    Dim oApp As New Excel.application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    
    DoCmd.SetWarnings False

Set oBook = oApp.Workbooks.Add
        Set db = CurrentDb
        
        'set control of Excel
        oApp.Visible = True
        oApp.UserControl = True
              
        'create module on filesystem to import Essbase macros into the dynamic workbook
        Open pathname & "essbase.txt" For Output As #1
            Print #1, "Declare Function EssMenuVRetrieve Lib ""ESSEXCLN.XLL"" () As Long"
            Print #1, "Declare Function EssMenuVConnect Lib ""ESSEXCLN.XLL"" () As Long"
            Print #1, "Declare Function EssVConnect Lib ""ESSEXCLN.XLL"" (ByVal sheetName As Variant, ByVal userName As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long"
            Print #1, "Declare Function EssVSetGlobalOption Lib ""ESSEXCLN.XLL"" (ByVal item As Long, ByVal globalOption As Variant) As Long"
    
            Print #1, "Public Sub startEssbase()"
            Print #1, "Dim sts As Long"
            
                'set Essbase credentials
                Set rs = db.OpenRecordset("SELECT tbl_essbase_credentials.variable, tbl_essbase_credentials.data FROM tbl_essbase_credentials;")
                Do While Not rs.EOF
                    row = rs.Fields(0) & " = """ & rs.Fields(1) & """"
                    Print #1, row
                rs.MoveNext
                Loop
    
            Print #1, "     sts = EssVSetGlobalOption(6, False)"
            Print #1, "     sts = EssVConnect(Null, User, password, server, appname, dbname)"
            Print #1, "     sts = EssVSetGlobalOption(6, True)"
            Print #1, "End Sub"
            Print #1, "Public Sub retrieveEssbase()"
            Print #1, "     x = EssMenuVRetrieve()"
            Print #1, "End Sub"
            Print #1, "Public Sub transposer()"
            Print #1, "Dim nom, res As String"
            Print #1, "Dim valN As Double"
            Print #1, "Dim counter, i As Integer"
            Print #1, ""
            Print #1, "application.ScreenUpdating = False"
            Print #1, "    Worksheets(""Sheet2"").Cells.ClearContents"
            Print #1, "    Sheets(""P1"").Select"
            Print #1, "        headercol = Cells(1, application.Columns.Count).End(xlToLeft).Column"
            Print #1, "        Cells(1, headercol).End(xlDown).Select"
            Print #1, "        headerend = CInt(ActiveCell.row) + 2"
            Print #1, "        finalrow = Range(""E65536"").End(xlUp).row - headerend"
            Print #1, "        finalcol = Cells(headerend, application.Columns.Count).End(xlToLeft).Column"
            Print #1, "        counter = 0"
            Print #1, "        For j = headercol To finalcol"
            Print #1, "            Sheets(""P1"").Select"
            Print #1, "            counter = counter + 1"
            Print #1, "            nom = Cells(headerend, headercol).Offset(0, counter - 1).Value"
            Print #1, "            danext = (finalrow) * counter"
            Print #1, ""
            Print #1, "            For i = 0 To finalrow"
            Print #1, "                    Sheets(""P1"").Select"
            Print #1, "                    res = Cells(i + headerend, headercol + counter - 1).Offset(1, -1 - counter).Value"
            Print #1, "                    valN = Cells(i + headerend, headercol + counter - 1).Offset(1, 0).Value"
            Print #1, "                    Sheets(""Sheet2"").Select"
            Print #1, "                    Range(""a"" & i + headerend).Offset(danext - finalrow, 0).Value = nom"
            Print #1, "                    Range(""b"" & i + headerend).Offset(danext - finalrow, 0).Value = res"
            Print #1, "                    Range(""c"" & i + headerend).Offset(danext - finalrow, 0).Value = valN"
            Print #1, "            Next i"
            Print #1, "            "
            Print #1, "        Next j"
            Print #1, ""
            Print #1, "    Sheets(""Sheet2"").Select"
            Print #1, "    Range(""A3"").Select"
            Print #1, "    Range(Selection, Selection.End(xlDown)).Offset(-1, 0).Select"
            Print #1, "    Range(Selection, Selection.End(xlToRight)).Select"
            Print #1, "    Selection.Delete Shift:=xlUp"
            Print #1, "    Range(""A1"").Value = ""nominal"""
            Print #1, "    Range(""B1"").Value = ""responsibility"""
            Print #1, "    Range(""C1"").Value = ""value"""
            Print #1, "application.ScreenUpdating = True"
            Print #1, "End Sub"
        Close #1
        
        'import Essbase module
        oApp.VBE.ActiveVBProject.VBComponents.Import pathname & "essbase.txt"
        'delete the text file
        Kill pathname & "essbase.txt"
        'start Essbase
        Shell ("C:Hyperionessbasebinessexcln.xll")
        'run Essbase macro to connect
        oApp.Run "startEssbase"
        theyear = "2008"
        counter = 1
    
        'For counter = 1 To 12
    
            Set oSheet = oBook.Worksheets(counter)
            oSheet.Activate
            oSheet.Name = "P" & counter
            'lets set the time period
            oSheet.Cells(8, 4).Value = "'P" & counter & " " & theyear & " YTD"
            
            'lets add the Essbase headers
            Set rs = db.OpenRecordset("SELECT tbl_essbase_header.data FROM tbl_essbase_header;")
            For Each fld In rs.Fields
                Do Until rs.EOF = True
                    oSheet.Cells(rs.AbsolutePosition + 1, 4).Value = "'" & fld
                rs.MoveNext
                Loop
                rs.MoveFirst
            Next fld
            
            'lets add the nominals
            sql = "SELECT tbl_nominal.nominal FROM tbl_nominal;"
            Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
            For Each fld In rs.Fields
                Do Until rs.EOF = True
                    oSheet.Cells(10, rs.AbsolutePosition + 4).Value = "'" & fld
                rs.MoveNext
                Loop
                rs.MoveFirst
            Next fld
            
            'lets add the responsibilities
            sql = "SELECT tbl_responsibility.responsibility FROM tbl_responsibility;"
            Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
            For Each fld In rs.Fields
                Do Until rs.EOF = True
                    oSheet.Cells(rs.AbsolutePosition + 11, 2).Value = "'" & fld
                rs.MoveNext
                Loop
                rs.MoveFirst
            Next fld
            
            'run Essbase macro to retrieve
            oApp.Run "retrieveEssbase"
            oApp.Run "transposer"
            
            'clean out previous retrieval
            DoCmd.RunSQL ("DELETE tbl_retrieval.* FROM tbl_retrieval;")
            oApp.ActiveWorkbook.SaveAs (pathname & "tmp.xls")
            
            'clean up
            'oApp.Quit
            Set oApp = Nothing
            Set oBook = Nothing
            Set oSheet = Nothing
            
            'transfer in
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tbl_retrieval", pathname & "tmp.xls", True, "Sheet2!A1:IV65536"
            
            'clean up
            DoCmd.RunSQL ("DELETE tbl_retrieval.nominal, tbl_retrieval.responsibility, tbl_retrieval.value FROM tbl_retrieval WHERE (((tbl_retrieval.responsibility) Is Null));")
            Kill pathname & "tmp.xls"
            
    DoCmd.SetWarnings True
            
    'Close the Database and Recordset
    rs.Close
    db.Close

End Function

Feb200920

Task Bar Glomming (grouping)

Found yourself in a strictly locked down Windows desktop environment? Hate those sodding grouped windows on the taskbar and can’t get rid of them? Try out the below code by saving in a .vbs file and double-clicking to ungroup your windows. To group again, just re-launch the script.

There are other more complex variations out there such as those found at http://www.kellys-korner-xp.com/xp_tweaks.htm but this script simply hits the nail on the head.

Feb200920

Export Excel to HTML

Having found on more than a few occasions that MS Excel’s output to HTML is more than a little bloated and not very editable I needed a way to output clean HTML markup to a simple shared folder area.

This was originally used in Excel 97 and has not been tested in later versions though will most likely work fine.

Sub ExportToHTML()

Dim ws As Worksheet
Dim DocDestination 'name of the result file
Dim MyRange 'cells to be exported as html

xthLine = 1
CL1 = "#FFFF99"
CL2 = "#DADADA"
whichsheet = 0

If MsgBox("Export all sheets from this workbook:" & vbCrLf & Name, vbOKCancel, "E x p o r t") = vbOK Then

For Each ws In ActiveWorkbook.Worksheets
        whichsheet = whichsheet + 1

Sheets(whichsheet).Select
        MyRange = "$A$1:$J$58" 'set the range to export
        DocDestination = "whereyouwanthtmltoexport" & ActiveSheet.Name + ".html" 'set name of output file to the activesheet

ColCount = Range(MyRange).Columns.Count
        RowCount = Range(MyRange).Rows.Count
        Calculate

Open DocDestination For Output As 1

'Create HTML
        Print #1, "<!DOCTYPE html PUBLIC " & Chr(34) & "-//W3C//DTD XHTML 1.0 Transitional//EN" & Chr(34) & " " & Chr(34) & "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" & Chr(34) & ">" & Chr$(13)
        Print #1, "<html xmlns=" & Chr(34) & "http://www.w3.org/1999/xhtml" & Chr(34) & ">" & Chr$(13)
        Print #1, "<head>" & Chr$(13)
        Print #1, "<title>" & ActiveSheet.Name & "</title>" & Chr$(13)
        Print #1, "<meta http-equiv=" & Chr(34) & "content-type" & Chr(34) & " content=" & Chr(34) & "text/html; charset=iso-8859-1" & Chr(34) & " />" & Chr$(13)
        Print #1, "<meta http-equiv=" & Chr(34) & "Charset" & Chr(34) & "content=" & Chr(34) & "iso-8859-1" & Chr(34) & " />" & Chr$(13)
        Print #1, "<meta http-equiv=" & Chr(34) & "expires" & Chr(34) & "content=" & Chr(34) & "-1" & Chr(34) & " />" & Chr$(13)
        Print #1, "<meta http-equiv=" & Chr(34) & "Pragma" & Chr(34) & "content=" & Chr(34) & "no-cache" & Chr(34) & " />" & Chr$(13)
        Print #1, "<style type=" & Chr(34) & "Text/css" & Chr(34) & ">td{padding:0px 10px 0px 10px; align:center;}</style>" & Chr$(13)
        Print #1, "</head>" & Chr$(13)
        Print #1, "<body bgcolor=" & Chr(34) & "#9F9F9F" & Chr(34) & " >" & Chr$(13)
        Print #1, "<center>" & vbCrLf & "  <table>" & Chr$(13)

I = Val(Evryxlines) ' initalize i

Row = 0
        BGC = "#3333FF" 'Title colour
        While Row < RowCount

Row = Row + 1
            'DoEvents
            If (Not Range(MyRange).Rows(Row).Hidden) Then
                MV = ""
                Col = 0
                Colour = 0
                While Col < ColCount
                    Col = Col + 1
                    If (Not Range(MyRange).Columns(Col).Hidden) Then

'************************************************************************************
                        CellV = Range(MyRange).Cells(Row, Col).Text
                        If CellV = "" Then CellV = " "
                        Hza = Range(MyRange).Cells(Row,Col).HorizontalAlignment
                        If Hza = xlLeft Then CellA = ""
                        If Hza = xlCenter Then CellA = " align=" & Chr(34) & "center" & Chr(34) & ""
                        If Hza = xlRight Then CellA = " align=" & Chr(34) & "right" & Chr(34) & " "
                        If Range(MyRange).Cells(Row, Col).Font.Bold Then CellV = "<b>" & CellV & "</b>"
                        If Range(MyRange).Cells(Row, Col).Font.Italic Then CellV = "<i>" & CellV & "</i>"
                    '************************************************************************************

If BGC = "#3333FF" Then 'White font for the title bar
                        MV = MV & "        <td bgcolor=" & Chr$(34) & BGC & Chr$(34) & CellA & "><font color=" & Chr$(34) & "white" & Chr$(34) & "><b>" & CellV & "</b></font></td>"
                    Else
                        MV = MV & "        <td bgcolor=" & Chr$(34) & BGC & Chr$(34) & CellA & ">" & CellV & "</td>" & vbCrLf
                    End If
                    Else
                        MV = MV & "        <td>" & CellV & "</td>"
                    End If
                Wend
                Print #1, "    <tr>" & vbCrLf & MV & "    </tr>"
            End If

If Row = 2 Then
                BGC = "#9F9F9F" 'grey
            End If

If Row = 3 Then
                BGC = "#FFFFFF" 'white
            End If

If Row = 0 Or Row = 1 Or Row = 4 Then
                BGC = "#3333FF" 'blue
            End If

If Row > 3 Then
                If I = Val(xthLine) Then
                    I = 0
                        If BGC = CL1 Then
                            BGC = CL2
                        Else
                            BGC = CL1
                        End If
                End If
                I = I + 1
            End If

Wend

Print #1, "  </table>" & vbCrLf & "</center>" & vbCrLf
        Print #1, "<p>Updated: " & Date & vbCrLf
        Print #1, "</body>" & vbCrLf
        Print #1, "</html>" & Chr$(13)

Close 'Close the exported file

End Sub

May200517

Edinburgh Flat – 2005 – experientia docet

Thinking that there was a hold up at the back of the site I decided to dig up some of the original ‘turn of my hand’ development from way back yesteryear.

Here is an incarnation of the Edinburgh Flat site from 2005 onwards?!

Visit EdinburghFlat

Oct200424

Edinburgh Flat – 2004 – experientia docet

Thinking that there was a hold up at the back of the site I decided to trip around the WayBackMachine and dig up some of the original ‘turn of my hand’ development from way back yesteryear.

Here is an incarnation of the Edinburgh Flat site from 2004:

Visit the WayBackMachine

NOTE: images are no longer hosted hence the ommision of elements of the website from the WayBackMachine

Dec200320

Puffin Music 2003/4 – experientia docet

Thinking that there was a hold up at the back of the site I decided to trip around the WayBackMachine and dig up some of the original ‘turn of my hand’ development from way back yesteryear.

Here is an incarnation of the Puffin Music site from 2003/4:

Check out the WayBackMachine!

NOTE: images are no longer hosted hence the ommision of elements of the website from the WayBackMachine

Archive for 'Reference'

May200915

Hyperion Essbase – Excel – Auto-connect

May200913

JavaScript GET URL Parameter GUP JQuery

Apr200928

Use Javascript to find which render mode browser is in

Apr200926

Interactive BATCH file with DEBUG script

Apr200925

Hyperion Essbase -> Excel -> Access -> automagic

Feb200920

Task Bar Glomming (grouping)

Feb200920

Export Excel to HTML

May200517

Edinburgh Flat – 2005 – experientia docet

Oct200424

Edinburgh Flat – 2004 – experientia docet

Dec200320

Puffin Music 2003/4 – experientia docet

« Older Entries Recent Entries »

Login

Recent Developments

Categories

Recent Comments

External Links

Archives

Most Popular Articles

Archive for 'Reference'

May200915

May200913

Apr200928

Apr200926

Apr200925

Feb200920

Feb200920

May200517

Oct200424

Dec200320

Tags

Login

Recent Developments

Categories

Recent Comments

External Links

Archives

Most Popular Articles

Subscribe to RSS