Export table(s) to Excel

[DaveShmave]

I did a couple of searches but was unable to get my question answered. Maybe someone can help me with this.

I want to export 3 tables to Excel but have the first table as "Sheet1", the second table as "Sheet2" and the third as "Sheet3" within the same spreadsheet. Can this be done and if so, how exactly.

TIA

Dave X-)

 

[Lightning]

Read the "TransferSpreadsheet Action" topic in the on-line Help. This should give you all the information you need to do this. A quote from this topic:

"Microsoft Access creates a new spreadsheet when you export data from Microsoft Access. If the file name is the same as the name of an existing spreadsheet, Microsoft Access replaces the existing spreadsheet, unless you're exporting to a Microsoft Excel version 5.0, 7.0, or Excel 97 workbook. In that case, Microsoft Access copies the exported data to the next available new worksheet in the workbook.
If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, or Excel 97 spreadsheet, you can specify a particular worksheet by using the Range argument."

HTH
Lightning

 

[DaveShmave]

Actually I had looked that up, but since I'm using Office 2000 (sorry I didn't mention it before), I didn't think that would work. Well, I played around with it for a bit but didn't get the desired results.

Any other ideas?

Thanks,

Dave X-)

 

[GeekGirlau]

I haven't tried the Transfer Spreadsheet in 2000, but another method is to write the data to the spreadsheet from a recordset. If you like, you could create a template for the data and specify the name of the template when you use .Workbooks.Add.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim objExcel as Excel.Application
Dim intSheet as Integer
Dim i as Integer


On Error GoTo ExcelErr

Set db = CurrentDB
Set rs = db.OpenRecordset("MyTable", dbOpenSnapshot)

If rs.EOF Then
Exit Sub
End If

On Error Resume Next

Set objExcel = GetObject(, "Excel.Application&quot

If err.Number <> 0 Then
Set objExcel = CreateObject(&quot;Excel.Application&quot
End If

With objExcel
.Workbooks.Add

For intSheet = 1 to 3
.Sheets(&quot;Sheet&quot; & intSheet).select
.Range(&quot;A1&quot.select

' number of fields - 1 in recordset
For i = 0 To 8
.ActiveCell.Offset(0, i).Formula = rs.Fields(i)
Next i

.ActiveCell.Offset(1, 0).select
rs.MoveNext
Next intSheet

.Visible = True
End With

Set objExcel = Nothing

 

[DaveShmave]

Oops! I re-tried your suggestion Lightning and it works great.

Thanks for the help.

Dave :-0

 

[DaveShmave]

Thanks again for the help, Lightning and GeekGirlau.

I have one other question that I'm struggling with. Because I will run these queries over and over again, I want to be able to start with a fresh spreadsheet (the TransferSpreadsheet Method doesn't replace the existing spreadsheet) and then place the newest data in the first three sheets.

Does that make any sense? How do I delete a spreadsheet from Access?

TIA

Dave s-)

 

[DaveShmave]

Okay I think I finally have it figured out. I used the OutputTo Method to overwrite the older spreadsheet. Here's how:


DoCmd.OutputTo acQuery, &quot;Sheet1&quot;, &quot;MicrosoftExcel(*.xls)&quot;, &quot;C:\MyDocuments\MySpreadsheet.xls&quot;, False, &quot;&quot;

DoCmd.TransferSpreadsheet acExport, 8, &quot;Sheet2&quot;, &quot;C:\MyDocuments\MySpreadsheet.xls&quot;, False, &quot;&quot;

DoCmd.TransferSpreadsheet acExport, 8, &quot;Sheet3&quot;, &quot;C:\MyDocuments\MySpreadsheet.xls&quot;, False, &quot;&quot;


Dave