MS Excel

If your output data file is quite large or complex, then getting a client computer to do all the work may not be the best option. You can also produce the file server side and then either provide a link to it or begin downloading the file automatically on completion of its creation, this allows you to utilise the server to process the file which is likely to be a more powerful computer.

For this example we will create a Comma Separated Values (CSV) file with our information in, and in the first case simply provide a link to this file and in the second we will automatically trigger a download of this file to the local computer.

So, we need to get all of our data out of the datasource. for this example I am going to use the getString function which returns a recordset as a single string, although for more complex processes you may need to extract each element of the recordset in turn to perform an action on it, such as adding to a total, formatting etc.

Dim dbConn
Dim SQLStatement
Dim results

sub db_connect
    ' Create a connection object
    set dbConn = server.createobject("ADODB.connection")
    ' Open a connection using the following criteria - DSN Name, UserName, Password
    dbConn.open "myDSN", "username", "password"
end sub

sub db_disconnect
    dbConn.close
end sub

db_connect
SQLStatement="SELECT * FROM myTable"
set results=dbConn.execute(SQLStatement)

' if no results found
if results.EOF then
    ' no results
    Response.Write("No Data Found")
' otherwise
else
    Dim ResultsString
    Dim objFileSystemObject
    Dim objTextStream
    Dim filename

    ' grab the string, using a comma as a record separator and a tab as a record separator
    ResultsString = results.GetString(2, , ",", vbTab)

    ' create a link to the local file system on the server
    Set objFileSystemObject = Server.CreateObject("Scripting.FileSystemObject")

    ' Create a text file and dump the contents of the string to the file,
    ' using the file name export with the current date and time as a way to seperate out differing users
    filename="export" & CLng(Now)
    Set objTextStream = objFileSystemObject.CreateTextFile(filename & ".csv", 2, True)
    objTextStream.Write(ResultsString)

    ' Clean up
    objTextStream.Close
    Set objTextStream = Nothing
    Set objFileSystemObject = Nothing

    ' return a link to the file to the browser
    Response.Write("The file <a href='" & filename &".csv'>" & filename & ".csv</a> is now available to download")
end if

This gives us the file created on the server and allows us to return a link to that file to the user, a demonstration of this is available to demonstrate this. One of the possible downsides with this approach is that the user is required to run the code to produce the file and then click on a link to begin the download of their file. The other main problem with this approach is that it will, over time, fill up disk space on your server, so you need to either manually clean up the files at some point or create a batch job to do this for you.

If you are using MS Access as your database, from which you are creating the files, you have several different options for placing the data into the server side Excel / CSV files. A review of some of these, including their pro's, con's and some metrics can be found on this website.

In the final section of this article, we summarise the different approaches and provide links for futher reading on the subject.

Creating the Excel file Server Side