Export to excel in different tabs depending on criteria

K

KHogwood-Thompson

Hi,

I have a query called "CVR Movement", this contains various transactions
against various [JOB REF] codes. I want to export the data to Excel and would
like the data put into different tabs on the file which is retrieved from the
[JOB REF] field. Can this be done in code? If so, where does this need to go?
I have Office 2000 if it makes a difference at all.

Thanks
 
P

pietlinden

Hi,

I have a query called "CVR Movement", this contains various transactions
against various [JOB REF] codes. I want to export the data to Excel and would
like the data put into different tabs on the file which is retrieved fromthe
[JOB REF] field. Can this be done in code? If so, where does this need togo?
I have Office 2000 if it makes a difference at all.

Thanks
Click to expand...

maybe this will give you some ideas:
http://www.mvps.org/access/modules/mdl0035.htm
 
K

KHogwood-Thompson

Thanks for the link, however I am not sure how to use the information there
for what I need to do?? It talks about named ranges, but the workbook that I
want to copy to will be a new workbook each time I export and so will not
have named ranges. Also, the sheets that I want named in the workbook, copied
from the [JOB REF] field are likely to be dynamic too.
--
K Hogwood-Thompson


[email protected] said:
Hi,

I have a query called "CVR Movement", this contains various transactions
against various [JOB REF] codes. I want to export the data to Excel and would
like the data put into different tabs on the file which is retrieved from the
[JOB REF] field. Can this be done in code? If so, where does this need to go?
I have Office 2000 if it makes a difference at all.

Thanks
Click to expand...

maybe this will give you some ideas:
http://www.mvps.org/access/modules/mdl0035.htm
Click to expand...
 
K

Ken Snell \(MVP\)

Here is code that was written for a slightly different scenario, but it
shows how to do what you seek. If you are unable to modify the code to work
for your scenario, post back with details about the table and field names,
and we'll help you modify the code.

Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------

'Start of code
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 strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code

--

Ken Snell
<MS ACCESS MVP>


KHogwood-Thompson said:
Hi,

I have a query called "CVR Movement", this contains various transactions
against various [JOB REF] codes. I want to export the data to Excel and
would
like the data put into different tabs on the file which is retrieved from
the
[JOB REF] field. Can this be done in code? If so, where does this need to
go?
I have Office 2000 if it makes a difference at all.

Thanks
Click to expand...
 
K

KHogwood-Thompson

Many thanks Ken, managed to adapt the code that you posted and it works
perfectly!!

Many thanks again ....
--
K Hogwood-Thompson


Ken Snell (MVP) said:
Here is code that was written for a slightly different scenario, but it
shows how to do what you seek. If you are unable to modify the code to work
for your scenario, post back with details about the table and field names,
and we'll help you modify the code.

Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------

'Start of code
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 strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code

--

Ken Snell
<MS ACCESS MVP>


KHogwood-Thompson said:
Hi,

I have a query called "CVR Movement", this contains various transactions
against various [JOB REF] codes. I want to export the data to Excel and
would
like the data put into different tabs on the file which is retrieved from
the
[JOB REF] field. Can this be done in code? If so, where does this need to
go?
I have Office 2000 if it makes a difference at all.

Thanks
Click to expand...
Click to expand...
 
A

Adnan

Ken,

Is there anyway I can make this code work to where it lists records into one
sheet insted of multiple?


This is my code:

'=== Start of code ===
Sub cmdExport_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 strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- CRFNumber and qrySearch need to
' *** be changed to your table and field names
' Get list of CRFNumber values -- note: replace my generic table and
field names
' with the real names of the qrySearch table and the CRFNumber field
strSQL = "SELECT DISTINCT CRFNumber FROM qrySearch;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of CRFNumber values and create a query for each
CRFNumber
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- AOR, tblCRF, and
' *** CRFNumber need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("AOR", "tblCRF", _
"CRFNumber = " & rstMgr!CRFNumber.Value)

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- CRFNumber and qrySearch need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM qrySearch WHERE " & _
"CRFNumber = " & rstMgr!CRFNumber.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\MyFileName.xls"

' DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\MyFileName.xls" '"C:\" & strMgr &
Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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


End Sub
'=== End of code ===


Thank you
Adnan



Ken Snell (MVP) said:
Here is code that was written for a slightly different scenario, but it
shows how to do what you seek. If you are unable to modify the code to work
for your scenario, post back with details about the table and field names,
and we'll help you modify the code.

Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------

'Start of code
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 strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code

--

Ken Snell
<MS ACCESS MVP>


KHogwood-Thompson said:
Hi,

I have a query called "CVR Movement", this contains various transactions
against various [JOB REF] codes. I want to export the data to Excel and
would
like the data put into different tabs on the file which is retrieved from
the
[JOB REF] field. Can this be done in code? If so, where does this need to
go?
I have Office 2000 if it makes a difference at all.

Thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exporting Data to Multiple Tabs in Excel 1
Export Table in Excel in separated tab 4
Exporting several queries into Excel 1
Export Many Unrelated Access (2000) Data Tables to Excel 6
Exporting to a template in Excel 1
Export to excel with multiple tabs 8
How do you Export Data from Access into Multiple Tabs in Excel from one query 8
Exporting from Access to Excel keeping EXCEL formatting 2

Top