Exporting Query to Excel - Multiple Files

  • Thread starter Thread starter andrewbecks
  • Start date Start date
A

andrewbecks

Hello. I am looking for some help in exporting a query to Excel.

Currently, I have a query that has four columns:

-District_Manager
-Store_Number
-Fiscal_Week
-Sales_Dollars

There are approx. 200 different District Managers, and each District
Manager has about 10 lines of data. What I would love to do is have a
seperate excel file generated for each District Manager, that includes
only his or her information. I'd like the Excel files to have the name
of the District Manager.

So, for District Manager Joe Example, an excel file named [Joe
Example.xls] would be created that includes the above query but only
includes row where the value for District Manager is Joe Example.

Any assistance with this would be GREATLY appreciate.

Andrew
 
Here's some generic code that will get you started:

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>


andrewbecks said:
Hello. I am looking for some help in exporting a query to Excel.

Currently, I have a query that has four columns:

-District_Manager
-Store_Number
-Fiscal_Week
-Sales_Dollars

There are approx. 200 different District Managers, and each District
Manager has about 10 lines of data. What I would love to do is have a
seperate excel file generated for each District Manager, that includes
only his or her information. I'd like the Excel files to have the name
of the District Manager.

So, for District Manager Joe Example, an excel file named [Joe
Example.xls] would be created that includes the above query but only
includes row where the value for District Manager is Joe Example.

Any assistance with this would be GREATLY appreciate.

Andrew
Click to expand...
 
Here's some generic code that will get you started:

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>




Hello. I am looking for some help in exporting a query to Excel.
Click to expand...
Currently, I have a query that has four columns:

There are approx. 200 different District Managers, and each District
Manager has about 10 lines of data. What I would love to do is have a
seperate excel file generated for each District Manager, that includes
only his or her information. I'd like the Excel files to have the name
of the District Manager.
Click to expand...
So, for District Manager Joe Example, an excel file named [Joe
Example.xls] would be created that includes the above query but only
includes row where the value for District Manager is Joe Example.
Click to expand...
Any assistance with this would be GREATLY appreciate.
Click to expand...
Andrew- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi. Thanks so much for your help with this code. I've spent a couple
of hours playing around with it and I keep getting stuck around this
section:

strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)

Anyway, I was wondering if you could provide any additional
assistance. In the code you provided, it appears as if it wants the
Manager name to be sitting in a seperate table. What if, I simply want
it to find the manager name from the existing table.

Let's say that there is only one table, called sample_table. And in
sample_table, there are three fields:
maanger_name, store_number, and sales_amount

Now, let's say that there are 20 different managers whose names appear
within the manager_name field through out the table and that each
manager (represented by manager_name), had 15-20 records. Is it
possible to filter for each manager_name and export all records to a
spreadhseet, and then move onto the next manager and do the same
thing. Each time, I'd like the XLS file to be named manager_name value
+ .xls.

Thanks again for your help.
 
In that case, the code would be changed slightly so that you do not need to
use the DLookup function. That step was there because the original question
by a poster, for which I'd written this code, included the desire to use the
full name of the manager in the filename, but the data being filtered
contained a field for the manager ID value, so it was necessary to look up
the full name for the export.

Post the code that you have "created" from the code example, and I will
assist in making the additional modifications to it.
 
Here is something that I created that could work, if you don't mind making
temptables.

I create a temptable with all of our buyers and then in the export use a
similar recordset and loop to export and it works like a charm. You could
also add a delete
temp table to the loop after the export in order to save space. My export
is under a different sub, but if you were constantly exporting to the same
filepath or wanted to add an input box for the file path you could include it
all under one sub.

This is one of my first offers to assist, so if you don't mind, please
respond back if it was helpful or I should keep my ideas to myself.

Thanks!
Roger

Private Sub btnCreateICRIF_Files_Click()
Dim strSql As String, Booisit As Boolean, dbs As Database, rst As Recordset,
strBuyer As String

'Turn Warnings off

DoCmd.SetWarnings False
DoCmd.Hourglass (True)

Me.txtInvFillPlsWait.Visible = True
DoCmd.RepaintObject

'Remove old tables ICRIFPt1 & ICRIFPt2

Call Subs.TableisThere(Booisit, "ICRIFPt1")

If Booisit = True Then DoCmd.DeleteObject acTable, "ICRIFPt1"

Call Subs.TableisThere(Booisit, "ICRIFPt2")

If Booisit = True Then DoCmd.DeleteObject acTable, "ICRIFPt2"

'Create the ICRIF Part 1 table

strSql = "SELECT tblICRIF.Whse, tblICRIF.Product, tblICRIF.Description,
tblICRIF.Buyer, tblICRIF.[Vendor #], tblICRIF.[Vendor Name]," _
& " tblICRIF.[Replenishment Source], tblICRIF.Class, tblICRIF.[$ Ordered],
tblICRIF.[$ Shipped], tblICRIF.[$ Fill Pct]," _
& " tblICRIF.[Qty Ordered], tblICRIF.[Qty Shipped], tblICRIF.[Fill Pct],
tblICRIF.[# Lines], tblICRIF.[# Complete Lines]," _
& " tblICRIF.[Line Fill Pct] INTO ICRIFPt1 FROM tblICRIF WHERE
(((tblICRIF.Whse)<='TAMP')) ORDER BY tblICRIF.Whse;"

DoCmd.RunSQL (strSql)

Set dbs = CurrentDb()

strSql = "SELECT ICRIFPt1.Buyer FROM ICRIFPt1 GROUP BY ICRIFPt1.Buyer;"

Set rst = dbs.OpenRecordset(strSql)

Do Until rst.EOF = True

strBuyer = rst!Buyer

'Create the temp tables for export by buyer sorted in descending Fill
percent

strSql = "SELECT ICRIFPt1.Whse, ICRIFPt1.Product, ICRIFPt1.Description,
ICRIFPt1.Buyer, ICRIFPt1.[Vendor #]," _
& " ICRIFPt1.[Vendor Name], ICRIFPt1.[Replenishment Source],
ICRIFPt1.Class, ICRIFPt1.[$ Ordered], ICRIFPt1.[$ Shipped]," _
& " ICRIFPt1.[$ Fill Pct], ICRIFPt1.[Qty Ordered], ICRIFPt1.[Qty
Shipped], ICRIFPt1.[Fill Pct], ICRIFPt1.[# Lines]," _
& " ICRIFPt1.[# Complete Lines], ICRIFPt1.[Line Fill Pct] INTO temp" &
strBuyer & " FROM ICRIFPt1" _
& " WHERE (((ICRIFPt1.Buyer) = '" & strBuyer & "')) ORDER BY
ICRIFPt1.[Line Fill Pct];"

DoCmd.RunSQL (strSql)

rst.MoveNext

Loop

Me.txtInvFillPlsWait.Visible = False

'Turn Warnings on

DoCmd.SetWarnings True
DoCmd.Hourglass (False)

MsgBox "The ICRIF table has been created and is ready to export.", vbOKOnly,
msglbl

End Sub


andrewbecks said:
Hello. I am looking for some help in exporting a query to Excel.

Currently, I have a query that has four columns:

-District_Manager
-Store_Number
-Fiscal_Week
-Sales_Dollars

There are approx. 200 different District Managers, and each District
Manager has about 10 lines of data. What I would love to do is have a
seperate excel file generated for each District Manager, that includes
only his or her information. I'd like the Excel files to have the name
of the District Manager.

So, for District Manager Joe Example, an excel file named [Joe
Example.xls] would be created that includes the above query but only
includes row where the value for District Manager is Joe Example.

Any assistance with this would be GREATLY appreciate.

Andrew
Click to expand...
 
Back
Top