Re: Export multiple versions of a query to separate Excel files

Tech-Archive recommends: Fix windows errors by optimizing your registry

OK - This code should do what you seek. It uses two separate Do loops for
the two different tables.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

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 -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By Transaction
Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type]
WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, strPathFile

rstMgr.MoveNext
Loop
End If



' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr1 = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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 rstMgr1.EOF = False And rstMgr1.BOF = False Then
If rstMgr1.EOF = False Then
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [CC Adj Totals] WHERE " & _
"Subinventory = '" & rstMgr1!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, strPathFile, , "CC Adj Totals"

rstMgr1.MoveNext

Loop
End If
rstMgr.Close
rstMgr1.Close
Set rstMgr = Nothing
Set rstMgr1 = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

MsgBox "Individual Analysis Files Created.", vbOK, "Status"


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Erick C" <boricua2793@xxxxxxxxx> wrote in message
news:c319f44e-7e8c-4ccf-85da-8b1a4e2816eb@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Ken -
Thank you for your reply. I apologize, I should have worded my
previous post a bit differently. Currently the code is taking one
table and splitting it up by subinventory and creating an individual
file. What I am attempting to do is once the file is created from the
temp query for "Subinventory #1" then the process repeats itself for
another table, finds the same subinventory number that was used to
create the file for a new temp query, and export that table
information into a new work*** in the file that was just created.
Once the new tab has been added to the file then the process loops and
moves to "Subinventory #2" and creates a new file. I tried to modify
the existing code, basically repeating the process from the first
strSQL point. I think you will understand what I tried to do once you
see the code below.
I tried to use the code as it is written below but I get a compile
error that says "Do without loop."

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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

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 -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpread*** acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile

' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [CC Adj Totals] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.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, strPathFile, ,"CC Adj Totals"
rstMgr.MoveNext

Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

MsgBox "Individual Analysis Files Created.", vbOK, "Status"



On Jul 27, 8:53 pm, "Ken Snell [MVP]"
<kthsneisll...@xxxxxxxxxxxxxxxxxx> wrote:
The easiest way to do the export to a specific work*** is to export a
query that is named the same as the name you want to have on the
work***.

The original code that you posted in this thread was doing that in the
part
where it set the properties of the qdf object:

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL

The qdf.Name step is the one that names the query so that you could export
it in the TransferSpread*** action. You'd use strTemp instead of
strTable.

To give you more specific assistance, I'd need to have some specific
examples, along with the "final" code that you now are using, to show me
what you want to do. And there is no problem with more questions.
--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

"Erick C" <boricua2...@xxxxxxxxx> wrote in message

news:5a3d36ce-5cba-4e5b-a699-e50fabc61e3a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello Ken -
Again you have saved the day! I made the changes that you outlined
and the export is now working.
I do have one more question regarding this code. I really do not know
if it is possible, but there was a code that you helped me with
previously that exported data to particular worksheets in a workbook.
Is there a way to combine these two codes so as the individual files
are being created other worksheets are being exported as well.
So with the code that is now working, can I add another DoCmd at the
end before it loops that will export information to a particular
work***?
I was thinking about something like this code at the bottom

strTable = "Subinventory Item Crosstab"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, ,"Subinv Quantities"

Also, in the current code that was just completed there is a portion
at the end of DoCmd that says rstMgr.MoveNext. Does this cause the
quert to go to the next store and export? Would this have to be moved
to the last DoCmd in the list?

I apologize for all of my questions, and I greatly appreciate all of
the help that you have given to me and it is greatly appreciated. If
I start asking too many questions feel free to let me know and I will
stop.

Thanks.


.

Quantcast