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
Code:
<%
Dim ACC 'Access Object
Dim sXLDestination
Dim sTableName
Dim sDB
sTableName = "Table1"
sXLDestination = "d:\Book1.xls"
sDB = "d:\db1.mdb"
Set ACC = Server.CreateObject("Access.Application")
With ACC
.OpenCurrentDatabase sDB
.DoCmd.TransferSpreadsheet 1, 8, _
sTableName, sXLDestination, -1
.Quit
End With
Set ACC = Nothing
Response.Write "Done!"
%>
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 = "JavaScript" %>
<%
var acExport = 1;
var acSpreadsheetTypeExcel97 = 8;
var BoolTrue = -1;
var ACC = new ActiveXObject("Access.Application"
;
var ExcelDestination = "/excel/Book1.xls";
var TableName = "Table1";
var Database = "access/db1.mdb";
with (ACC) {
OpenCurrentDatabase(Database);
DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel97, TableName, ExcelDestination, BoolTrue);
Quit();
}
Response.Write("Done"
;
%>
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
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
