vbScript to Export to Excel 2007

    Home

Export to Excel is one of the most important features that business users request. This requirement is quite frequently observed in reporting world. The requirement could be that we click on any button/link and after processing the request on server open up an Excel with desired data on the client machine. On the other hand, the requirement could be that we first display the data in the desired format to the users on the client tool like IE for the web application, and on clicking any link/button, we export that data to Excel.

Well, honestly speaking this is less of an article or code snippet but actually an attempt to put across the solution for a situation that troubled me for quite some time. I am using client side VBScript to Export to Excel mechanism to export the data of the DataGrid. The problem started when our clients upgraded their machines from Office 2003 to Office 2007 and my standard script stopped working. Here we are going to briefly understand the server side and client approaches for Export to Excel and then we will focus on the VBScript that can be used to Export DataGrid data into Excel.


The script that we are going to use for Export To Excel when users have upgraded to Office 2007 is as follows. Let us first look at the script and then we will see how it is working for us.

    Function Export(objToExport)

        ON ERROR RESUME NEXT
        DIM sHTML, oExcel, fso, filePath

        sHTML = document.all(objToExport).outerHTML

        SET fso = CreateObject("Scripting.FileSystemObject")
        filePath = fso.GetSpecialFolder(2) & "\MyExportedExcel.xls"
        fso.CreateTextFile(filePath).Write(sHTML)

        DIM i
        SET i = 0

        DO WHILE err.number > 0
            err.Clear()
            filePath = fso.GetSpecialFolder(2) & "\MyExportedExcel" & i & ".xls"

            i = i + 1
        LOOP

        SET oExcel = CreateObject("Excel.Application")
        IF err.number>0 OR oExcel =NULL THEN
msgbox("You need to have Excel Installed and Active-X Components Enabled on your System.")
            EXIT FUNCTION
        END IF

        oExcel.Workbooks.open(filePath)
        oExcel.Workbooks(1).WorkSheets(1).Name = "My Excel Data"
        oExcel.Visible = true
        Set fso = Nothing

    End Function


Believe me it is not as complex as it looks. Let us see what we are doing here. In nutshell, the above code creates a file with filename MyExportedExcel.xls and saving the same in TEMP folder of Windows and then opens and serves the same to the user. Now let us get into details of important code pieces of this script.

  • fso = CreateObject("Scripting.FileSystemObject") is used to return a TextStream object that can be read from or written to. Click here for more details.
  • fso.GetSpecialFolder(2) gets the complete folder path of TEMP folder. Click here for more details.
    fso.CreateTextFile(filePath).Write(sHTML) creates the file with the desired file name. Click here for more details.
  • oExcel.Workbooks.open Opens the specified file. It expects path of the file to open.
  • The DO WHILE Loop. If the user has open one instance of the file but still clicks once again for Export, then CreateTextFile Errors out. To avoid such issues, we first see if we can create and save a new file; If not, then we append the name of the file with number and then save it. We store the reference of the name in the variable filepath and use it in later part of the code. We repeatedly try the same in the loop to ensure that even if user has opened several instance including some with number as part of its name, then also it should not error out.

Well, this mechanism should work for prior versions of Excel as well. This script has been tested on machines where the machines were earlier having Office 2003 and later on upgraded to Office 2007. It is highly advised to test the script on the desired machines on which office 2007 is freshly installed.