Export Table in Excel Workbook with many sheets.

[nathanmav]

Hi everyone just want to ask if anybody know how to export ms access table into excel workbook with separate sheet based on a value of field?

For Example:

I have One Table with three fields
Name Address Company
Steve a Apple
John b Apple
Josh c Dell
Pete d Dell
Pat e HP
Jacob f HP

Output in Excel(list for Employee by company):
Sheet 1 Sheet 2 Sheet 3
Apple Dell HP
Name Address Name Address Name Address
Steve a Josh c Pat e
John b Pete d Jacob f

I Hope someone will understand my logic.. thanks in advanced

 

[llkhoutx]

The Treadsheet function is what you need.

 

[Isskint]

You could not automatically send the contents of one table to multiple tabs without interogating the data first to determine which tab you wanted it to go to.

You could have a series of queries to select data for each tab (company?) You could then use the TransferSpreadsheet method (i think that is what llkhoutx was suggesting). There are several ways to go about this, have a look at the 4 examples Bob Larson suggests here http://www.btabdevelopment.com/ts/default.aspx?PageId=10

EDIT: I have produced a procedure to do this for you, however it is bugging out, so until i (or someone here) can figure out why, it may not work for you, but you are welcome to give it a try. See my post here http://www.access-programmers.co.uk/forums/showthread.php?p=1207676#post1207676

 

[nathanmav]

thanks you for the reply.. i found and code and modify a little bit.. it will generate a one workbook with many sheet but the problem is it will show only 1 record for each worksheet.. but i have many records in the field with equal of the tenderproject field.. and also the code makes infinite.. thanks again for the reply



Private Sub cmdExportToExcel_Click()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String


Const strFileName As String = "TNDRMonitoring"
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name

strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"

Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName


strSQL = "SELECT * FROM Monitoring WHERE TenderProject=TenderProject"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False


strMgr = DLookup("TenderProject", "Monitoring", _
"ID = " & rstMgr!ID.Value)

strSQL = "SELECT * FROM Monitoring WHERE " & _
"ID = " & rstMgr!ID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\Documents and Settings\My Documents\" & strFileName & ".xls"

rstMgr.MoveNext

Loop

End If

rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

End Sub



and also i notice that the record will generate in excel is the last record in my table but rest is not generated..