Exporting Access Data To Excel

[DALIEN51]

Hi all,

I am using Access 97 & Excel 97 for this problem. I have a Access query which takes the contents of three tables and exports them to Excel. However, the query has now reach 69000+ records and increases by about 1000+ records ever month. So what I need to do is create as many WORKSHEETS within a single Excel WORKBOOK as necessary to accomodate all of my Access data. I have written a piece of code which will create seperate WORKBOOKS for each 65000+ of records but then what I want to do is code the almagamation of these WORKBOOKS into 1.

In short, after the first WORKBOOK is created I use code to make that the active WORKBOOK and then I want to import into that the other WORKSHEETS in the other WORKBOOKS.

I am using the folowing DIM's:

Dim X As New Excel.Application
Dim WkBook As Excel.WorkBook
Dim WkSheet As Integer
Dim ExcelSheet As Excel.Worksheet

ExcelSheet therefore is the current WORKSHEET within the Excel spreadsheet I want to import into.

Any advice on the command to perform a transfer of WORKSHEET data between Excel WORKBOOKS?

Regards,

DALIEN51

 

[Jibbadiah]

Take a look at the following...

Function Export_To_Excel()

Dim dbs As Database
Dim rst_WrkShtName
Dim WrkShtName As String
Dim qdfTemp As QueryDef

DoCmd.SetWarnings False

Set dbs = CurrentDb

'-- Use this query to find a field to be used as names for the worksheets
Set rst_WrkShtName = dbs.OpenRecordset("SELECT <Tb_Fieldname> FROM <Access_Tb> GROUP BY <Tb_Fieldname> ORDER BY <Tb_Fieldname> ASC;")

With rst_WrkShtName

.MoveFirst
Do While Not rst_WrkShtName.EOF
WrkShtName = (rst_WrkShtName("<Tbl_Fieldname>"))

With dbs

' Creates a query named as the current Tb_Fieldname, query produces results for each worksheet
' The worksheet is created with the same name as the Query
Set qdfTemp = .CreateQueryDef(WrkShtName, "SELECT * FROM <Access_Tb> WHERE <Tbl_Fieldname> = '" & WrkShtName & "';")

' Transfers this query to a spreadsheet named as WrkShtName in the same file
DoCmd.TransferSpreadsheet 1, 8, WrkShtName, "C:\temp\<Excel_Filename.xls>", True

' Deletes the query after exporting to excel
.QueryDefs.Delete WrkShtName

End With

.MoveNext
Loop

End With

End Function