Open SAV file and create an XL map of contents

Needed to write some code today to open up an Statistics sav file and create an XL map file of the metadata. The next step in the process will be to write a IBM Statistics syntax script to change the content in the SAV file based on the information in the XL file. Here is the first bit of the code , lets call it stage 1. If you want stage 2 let me know.

So lets declare some vars

CONST sDataPath = "c:\temp"
CONST sDataName = "IBV_A069_EPH.sav"
CONST sXLMap = "SAV_MAP.xls"


Dim oDSCs, oDSC, oMDM, oVar,oElement
Dim oExcel,oSheet

Dim iVarCount,iRow

And now lets go ahead and open up the SAV file using the Statistics DCS

Set oDSCs = CreateObject("MRDSCReg.Components")
Set oDSC = oDSCs["mrSavDsc"]
Set oMDM = oDSC.Metadata.Open(sDataPath + sDataName)

Then lets open up our XL file. I am assuming it is already made and contains a tab called Labels in it. We are also going to give the columns some names

Set oSheet = oExcel.Sheets["Labels"]
oSheet.Activate()
	
iRow = 1
oSheet.cells[1][1].value = "Variable Name"
oSheet.cells[1][2].value = "New Variable Name"
oSheet.cells[1][3].value = "Type"
oSheet.cells[1][4].value = "Value"
oSheet.cells[1][5].value = "Label"

Next we need to loop the variables in the file outputting the data we find to the relevant places in our sheet

	for iVarCount = 0 to oMDM.Variables.count-1
		iRow = iRow + 1
		Set oVar = oMDM.Variables[iVarCount]	
		
			oSheet.cells[iRow][1].value = oVar.Name
			oSheet.cells[iRow][3].value = "L"
			oSheet.cells[iRow][5].value = TextOnly(oVar.Label)
		
			oSheet.cells[iRow][6].value = oVar.dataType

			For each oElement in oVar.Elements.Elements
	   			iRow = iRow + 1
	   			oSheet.cells[iRow][3].value = "C"
	   			oSheet.cells[iRow][4].value = oElement.Element.NativeValue
	   			oSheet.cells[iRow][5].value = TextOnly(oElement.Label)	   			
   			Next
   		
	Next
oMDM.Close()	

and finally we need out TextOnly Function. This will allow us to strip out HTML codes that may be in our labels

Function TextOnly(sValue)
	Dim sNew,iCount,bStart
	For iCount = 0 to len(sValue) -1
		
		if ( find( "<",mid(sValue,iCount,1) )>-1 ) then 
			bStart = true
		End if
			
		if ( bStart = true ) Then
		else
			sNew = sNew + mid(sValue,iCount,1)
		End If
			
		if ( find( ">",mid(sValue,iCount,1) )>-1 ) then 
			bStart = false
		End if
	Next
	TextOnly = sNew
End Function

JustCode : Code to open up an exisiting excel workbook

This code shows us how to open up an existing excel file and select the active workbook.

 
' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 5th July 2009
' Code to open up an exisiting excel workbook
' ****************************************

Dim oExcel,oWorkBook

Set oExcel = createobject("Excel.Application")

oExcel.Workbooks.Open("C:\TEMP\ExcelExport.xls")

oExcel.Visible = True

oWorkBook = oExcel.ActiveWorkbook

JustCode : Loop columns in an excel sheet

This code will open up an existing excel workbook and select the active sheet then loop through the first ten cells of the first row showing the cell contents

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 5th July 2009
' Code to loop columns in an excel sheet
' ****************************************

Dim oExcel,oWorkBook,oSheet
dim iCel,oCell,iStartCol,iEndCol

Set oExcel = createobject("Excel.Application")

oExcel.Workbooks.Open("C:\TEMP\ExcelExport.xls")

oExcel.Visible = True

oWorkBook = oExcel.ActiveWorkbook

Set oSheet = oWorkBook.ActiveSheet

iStartCol = 1
iEndCol = 10

For iCel = iStartCol to iEndCol
     Set oCell = oSheet.Cells[1][iCel]
     Debug.Log(oCell.value)
Next

Testing Unicom Intelligence with Office 2016

Just the other day I was required to upgrade to office 2016. I have set this post up to record any issues I find and record what I have tested. If you have done any testing on 2016 then please feel free to leave a comment and we can add it to the list of things that may need to be looked at by the Unicom Team.

Product Test Outcome Date
Excel Can I use my existing VBA code to manipulate XL files Yep , no issues here everything currently still works 5/06/2017
Excel Can I export an MTD to an XL file Yep , no issues here everything currently still works 21/06/2017
Excel Can I export data file to a csv file that can be loaded into XL Yep , no issues here everything currently still works 21/06/2017
Powerpoint Can I use my existing VBA code to manipulate ppt/pptx files Yep , no issues here everything currently still works 5/06/2017
Powerpoint Can export my tables to a PPT/PPTX still using my old chart templates Yep , no issues here everything currently still works 5/06/2017
Powerpoint create a batch file and run an MRS that holds a TOM export to PPT Excel seams to hang at end of export ( I think when it is saving the PPT ) Added code to kill process “TASKKILL /F /IM excelcnv.exe” to see if I can kill it 21/06/2017

Speeding up your Excel Exports

With speed about the mrExcelExport always a hot topic when you write your TOM scripts i thought i would take a look at another possability of getting your tables into Excel. In this article i will show you how you can use the “mrTextExport” to export your tables , and also sugest something you could do to the exising Excel Export to speed it up.

Read moreSpeeding up your Excel Exports

Quick Post : Xceed Technologies

This is just to let you know that Xceed Technologies are very soon to release a new Excel ¬†export for TOM tables. At this stage we don’t know much more about it but we are told , like the old Verge Excel Export, it can export 100’s of tables in a very short space of time. What we do know is that they would like you to sign up to their website so that you be notified when they go live. Their website can be found at http://eexport.xceed.co.za/¬†and when they do go live we would love to hear what you have to say about the export.

Customising GetMrData.xls ( Split Data Across Sheets )

In a recent coment we where asked by Nathan to change the

 so that when the get data section is run it spreads the data across multiple sheets. Having the data all on one sheet can be a problem in older versions of excel, as you are limited to a certain amount of columns. This article provides you with this updated 
 and explains how it is done.

Read moreCustomising GetMrData.xls ( Split Data Across Sheets )

How to use GetMrData.xls

GetMrData.xls has been around for as long as we can remember, but how many of you know about it ? In the article we will show you how to use it and get your data into an excel file. Yes you can export directly out of Data collection to do this directly, but with this file and some customization you could make it do allot more than just that.

Read moreHow to use GetMrData.xls

%d bloggers like this: