OK, this may get a little complicated:
Follow these instructions to create the test, then you can
go back and change all the names later.
1. Open Excel. Click on the sheet names at the bottom and
rename them My_Sheet_1, My_Sheet_2. File > Save As >
Template. Save this to C:\My_Template.xlt
2. Create a new module and copy and paste the following
code into it:
Public Sub sCopyToExcel()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim intLastCol As Integer
Dim sheet_name As String
Const conMAX_ROWS = 20000 'copy first 20000 rows
Const conWKB_NAME = "C:\My_Template.xlt" 'Excel template
file - includes headers
Set db = CurrentDb
Set objXL = New Excel.Application
'Use query to retrieve data
Set qdf = db.CreateQueryDef("")
' MS Excel Object
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
'****** Sheet 1 **************
With qdf
.SQL = "SELECT * FROM Table1"
Set rs = .OpenRecordset()
rs.MoveFirst
End With
sheet_name = "My_Sheet_1" 'name of your worksheet
Set objSht = objWkb.Worksheets(sheet_name)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.name = sheet_name
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
'because 1st row of template contains headers,
'Start inserting from cell A2
.Range("A2").CopyFromRecordset rs
End With
'****** Sheet 2 **************
rs.Close
qdf.Close
Set rs = Nothing
Set qdf = Nothing
'Use query to retrieve data
Set qdf = db.CreateQueryDef("")
With qdf
.SQL = "SELECT * FROM Table2"
Set rs = .OpenRecordset()
rs.MoveFirst
End With
sheet_name = "My_Sheet_2" 'name of your worksheet
Set objSht = objWkb.Worksheets(sheet_name)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.name = sheet_name
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range("A2").CopyFromRecordset rs
End With
End With
rs.Close
qdf.Close
db.Close
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
Exit Sub
End Sub
3. For testing purposes, copy and paste 1 of your tables
and rename it Table1, then create a Table2 the same way.
4. Create a form. Add a command button (Cancel the wizard).
5. From the menu click View > Code.
6. Copy and paste the following code:
Private Sub Command0_Click()
sCopyToExcel
End Sub
If it is still open, close Excel.
Run the form and click the button. It should
launch Excel and place Table1 data on My_Sheet_1,
Table2 data on My_Sheet_2.
Once you've got it working go back and change the names.
Good Luck.
-----Original Message-----
Please advise how to output different tables in one excel
file? The output should be saves in one excel file but
will have different sheet names based on the table names.
For example, the database have A, B, and C tables. I
would like to saved these into 1 excel file having 3
sheets namely A, B and C.
Please advise how to do this in access?
Help is very much appreciated.
arni :]
.
