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.

Ok , so this article is not going to chat about creating a table its all about the export and as you can see in the example we will be using the Text Export.

Dim sOutFile,oClip

Set oClip = CreateObject("{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

sOutFile = "MyFile.txt"

With oTableDoc.Exports["mrTextExport"]
    SetProperty(.Properties, "Destination", sOutFile)
    SetProperty(.Properties, "Interactive", True)
    SetProperty(.Properties, "ShowHelp", True)
    SetProperty(.Properties, "TableDelimiter", 63)
    .Export()
End With

Once we have the file we need to open up excel and add a workbook,

Debug.Log(ctext(now()) + "#Convert To Excel : ")
Set oExcel = createobject("Excel.Application")

oExcel.Application.ScreenUpdating = false

oExcel.Workbooks.Add()
oExcel.Visible = false

Then we need to open up our text file for read.

   
   Set oFile = oFSO.OpenTextFile(sOutFile,1,false,-1)
   iTableCount = 1
   sPage = ""

Next we loop each line in the file and copy the line to a string variable. Also , we check the contents of the line and if it is the fieled delimiter we copy and past the content of the string to the current sheet in excel using the clipboard and then we add another sheet.

   Do While oFile.AtEndOfStream = false
 	sLine = oFile.ReadLine()
	sPage = sPage + sLine + mr.newline

	If sLine = "?" Then
		
		oExcel.ActiveWorkbook.Sheets.Add(,oExcel.Sheets[oExcel.Sheets.Count])
		Set oSheet = oExcel.ActiveSheet
		oSheet.name = "T" + ctext(iTableCount)

		oSheet.Cells[1][1].Activate()
	        oClip.SetText(sPage)
    	        oClip.PutInClipboard()

		oSheet.Paste()
		sPage = ""
		iTableCount = iTableCount + 1
		
	End If
	
   Loop

and once we have read all the lines of the text file we close it and the excel file.

oExcel.Application.ScreenUpdating = True
oExcel.ActiveWorkbook.SaveAs(sOutXL)

oFile.Close()
oExcel.Quit()

Set oTabledoc = null


Debug.Log("Done : " + ctext(now()))

And there you have it , your tables output is now in excel , a excel tab for each table , but no formatting. No for me when I run tables , this is ok as I always use this output as raw data for use in other excel files so it works and its fast. Very Fast , try it ! Now I am sure any next question would be , well what happens if you want the tables formatted. well to me the answer would be , just write a set of macros to format the sheets once the export is finished. Or you can use something like the Exceed export component that will give you the speed and retain some of the formatting.

In the front of this article I mentioned that you would be able to do something with the “Excel Export” and if you have not guessed it already , what you can do is to strip out the code that does all the formatting and you will get the same effect as the above script and I suspect it will be slightly quicker again. There is information in the DDL about creating / editing your own Tom exports so take a look at it here.

To find out about this URL item and more look here
http://publib.boulder.ibm.com/infocenter/spssdc/v6r0m1/index.jsp?topic=%2Fcom.spss.ddl%2Fmrtables_modify_export.htm