Convert database table to excel format

[discusmania]

Hello guys!!!

1. I want to write a program that can convert a table in my database, into an Microsoft Excel file.

2. Then, I want to email the file to vendor.

How to do that? Does anyone have idea?

Thanks A lot

 

[caf]

Hi

I can show you a couple of ways of doing this

1) VBScript/ASP
2) Javascript/ASP
3) VBA (Access)
4) Visual Basic (If you have the software installed and if
you require more power than what scripting can provide)
*I'll only post the source upon request for this solution.*

Let's explore the solutions
1)ASP/VBScript

<%
   Dim ACC	'Access Object
   Dim sXLDestination   
   Dim sTableName       
   Dim sDB              

   sTableName = &quot;Table1&quot;
   sXLDestination = &quot;d:\Book1.xls&quot;
   sDB = &quot;d:\db1.mdb&quot;

   Set ACC = Server.CreateObject(&quot;Access.Application&quot;)

   With ACC
      .OpenCurrentDatabase sDB
      .DoCmd.TransferSpreadsheet 1, 8, _
      sTableName, sXLDestination, -1
      .Quit
   End With

   Set ACC = Nothing
   Response.Write &quot;Done!&quot;
%>

Obviously you can convert that to client-side by including it (if you want to) in a function and calling it on a button click,page load - replacing Server.CreateObject with just CreateObject and Response.Write with maybe a Msgbox as a sort of indication that the function is completed.
2)ASP/JavaScript

<%@ LANGUAGE = &quot;JavaScript&quot; %>
<%
var acExport = 1;
var acSpreadsheetTypeExcel97 = 8;
var BoolTrue = -1;

var ACC = new ActiveXObject(&quot;Access.Application&quot;
var ExcelDestination = &quot;/excel/Book1.xls&quot;;
var TableName = &quot;Table1&quot;;
var Database = &quot;access/db1.mdb&quot;;

with (ACC) {
OpenCurrentDatabase(Database);
DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel97, TableName, ExcelDestination, BoolTrue);
Quit();
}

Response.Write(&quot;Done&quot;
%>

3)Microsoft Access
This can done two ways
a) Macro
b) VBA

With a macro here's a screen shot that will (hopefully) give you an idea of how to proceed.

- Open the database that holds the data
- Select the Macros TAB
- New Macro
- Select TransferSpreadSheet from the Action drop down
- Fill out the Action arguments as required (@ the bottom)
to match (or not) with the screen shot

VBA within Access

For those who don't know, VBA is Visual Basic for Applications and is found in all MS Office products.

- Open the database that holds the data
- Select the Modules TAB
- New Module
- Paste the following code

Function Macro1()
On Error GoTo Macro1_Err

    DoCmd.TransferSpreadsheet acExport, _
            8, "Table1", "d:\Book1.xls", True, ""


Macro1_Exit:
    Exit Function

Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit

End Function

By the way, once you have your Macro, you have the option to save it as Visual Basic source.
The above code was generated by Access from a Macro I created.

4)Visual Basic
The last method is the most powerful of all the methods presented above and (when compiled) the fastest to execute.

The code, as mentioned in the beginning of this reply, will only be posted upon request.

These are not the only ways of achieving this since it can be done in alot more programming languages. For those solutions however, you'd have to visit the appropriate forum.

Hope this helps
caf

 

[Deadline]

Apart from Caf's neat solution, here is another option you may want to try...

http://support.microsoft.com/support/kb/articles/Q189/1/98.ASP

The title reads &quot;HOWTO: Use ASP to Query and Display Database Data in Excel&quot;

Real good one. Thanks!

RR