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)
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.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
		Set oSheet = oExcel.ActiveSheet
		oSheet.name = "T" + ctext(iTableCount)


		sPage = ""
		iTableCount = iTableCount + 1
	End If

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

oExcel.Application.ScreenUpdating = True


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

4 thoughts on “Speeding up your Excel Exports”

  1. How do I set “oClip” to clipboard object in mrScriptBasic… Need the syntax, tried to find on net, but no luck.

    Also, I guess the above code should work in an mrs file, is it correct?


  2. Its a very good point as i have neglected to give you that declaration in the script. You need to have

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

    I have updated the post. Does it work for you know in your mrs file?

  3. Yes it is working, thanks a lot!!

    May I please ask, what that object is “{1C3B4210-F441-11CE-B9EA-00AA006B1A69}” and how do you experts find all these codes.. 🙂


  4. Google is and Experts best friend. 90% of the code that you want to write you can find on the net and all you need to do is to convert it to DataCollection VBA and then add your twist to it. The 1C3B4210-F441-11CE-B9EA-00AA006B1A69 object is the clipboard object.

Leave a Comment

%d bloggers like this: