Re: transfer a query to excel

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

This line of code is "fixing" the name of the query that then is used as the
name on the spreadsheet tab:

strMAP = DLookup("TXT_BUSRULESMAPID", "TBL_BUSRULESMAPPING", _
"TXT_BUSRULEID = " & rstMAP!TXT_BUSRULEID.Value)


I'm guessing that TXT_BUSRULESMAPID is a lookup field in your
TBL_BUSRULESMAPPING table? As such, although you see the desired value in
the table's field, the real value being stored in the field is the primary
key value corresponding to that value.

You need to change the above DLookup expression to get the real value from
the correct table. Open up your TBL_BUSRULESMAPPING table in design view,
click on the TXT_BUSRULESMAPID field, click on the Lookup tab, and get the
query that is there (assuming you are using a Lookup field). That is the
info that we need to assist you in changing the DLookup expression.

--

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




"Jason" <Jason@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9EFDC766-2FAC-4C38-AFE8-2973E64CEB13@xxxxxxxxxxxxxxxx
HI there Ken,

Thank you very much for the link. I managed to get it working with one
minor
challenge.

The temp query, is using the primary key value which results in the
spreadsheet tabs named as the primary key. I really need these tabs to be
named the value of the field. Have spent many hours trying to work out how
to
make this happen, with no success.

Below is the code that I have:

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMAP As DAO.Recordset
Dim strSQL As String, strTemp As String, strMAP As String

Const strFileName As String = "TestingChartMapping"

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT TBL_BUSRULES.TXT_SUNCODE FROM [" & strTemp & "] WHERE
1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT TXT_BUSRULEID FROM TBL_BUSRULES;"
Set rstMAP = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

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

strMAP = DLookup("TXT_BUSRULESMAPID", "TBL_BUSRULESMAPPING", _
"TXT_BUSRULEID = " & rstMAP!TXT_BUSRULEID.Value)

strSQL = "SELECT TBL_COADEPARTMENTS.TXT_SUNCODE AS DEPT,
TBL_COACHART.TXT_ACCOUNTCODE AS ACCOUNT, TBL_COACHART.TXT_ACCOUNTNAME AS
[ACCOUNT DESCRIPTION], TBL_COACHART.TXT_ACTYPE AS TYPE,
TBL_COAA6.TXT_SUNCODE
AS A6, TBL_COACHART.TXT_T0 AS T0, TBL_COACHART.TXT_T1 AS T1,
TBL_COACHART.TXT_T2 AS T2, TBL_COACHART.TXT_T3 AS T3, TBL_COACHART.TXT_T4
AS
T4, TBL_COACHART.TXT_T5 AS T5, TBL_COACHART.TXT_T6 AS T6,
TBL_COACHART.TXT_T7
AS T7, TBL_COACHART.TXT_T8 AS T8, TBL_COACHART.TXT_T9 AS T9 FROM
(TBL_COAA6
INNER JOIN (TBL_COACHART INNER JOIN TBL_BUSRULESMAPPING ON
TBL_COACHART.TXT_COAID = TBL_BUSRULESMAPPING.TXT_ACCOUNTCODE) ON
TBL_COAA6.TXT_A6ID = TBL_COACHART.TXT_A6) INNER JOIN (TBL_COADEPARTMENTS
INNER JOIN TBL_BUSRULES ON TBL_COADEPARTMENTS.TXT_DEPTID =
TBL_BUSRULES.TXT_SUNCODE) ON TBL_BUSRULESMAPPING.TXT_BUSRULEID =
TBL_BUSRULES.TXT_BUSRULEID WHERE " & _
"TBL_BUSRULESMAPPING!txt_BUSRULEID = " & rstMAP!TXT_BUSRULEID.Value
& ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = strMAP
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "D:\My Documents\!!!Projects\Financials\Global Back Office\" &
strFileName & ".xls"
rstMAP.MoveNext
Loop
End If

rstMAP.Close
Set rstMAP = Nothing

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

End Sub

Is there any chance you can help me identify where in the code, I call for
the dept code?

Thanks in advance

Jason

"Ken Snell [MVP]" wrote:

See
Create a Query and Export multiple "filtered" versions of a Query (based
on
data in another table) to separate Worksheets within one EXCEL file via
TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSameFile

--

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


"Jason" <Jason@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8F4C6911-A080-45E4-84D9-67C422688780@xxxxxxxxxxxxxxxx
Hi all

I have a query that I would like to export (transfer) to Excel.
However,
there is a requirment for the transfer to recognise changes in a
particular
field, and place the results in a separate tab.

The query returns 24,500 records, of which there can be more than 50
one
to
many combinations. So potentially, the export to excel will have 50+
tabs

Is this possible ?





.

Relevant Pages

  • Re: Exporting from Access 2002 to Excel 2002 into multiple tabs
    ... Dim qdf As DAO.QueryDef ... Dim strSQL As String, strTemp As String ... ' Create temporary query that will be used for exporting data; ...
    (microsoft.public.access.queries)
  • Re: transfer a query to excel
    ... use as the name of the spreadsheet tab. ... The next table is There is a lookup field in this ... query that is there. ... Dim strSQL As String, strTemp As String, strMAP As String ...
    (microsoft.public.access.externaldata)
  • Re: transfer a query to excel
    ... use as the name of the spreadsheet tab. ... The next table is There is a lookup field in this ... query that is there. ... Dim strSQL As String, strTemp As String, strMAP As String ...
    (microsoft.public.access.externaldata)
  • Re: Exporting from Access 2002 to Excel 2002 into multiple tabs
    ... I supposed to create an access query and put the code below into the SQL ... Dim qdf As DAO.QueryDef ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.queries)
  • Re: transfer a query to excel
    ... Dim strSQL As String, strTemp As String, strMAP As String ... Set qdf = dbs.CreateQueryDef ... Create a Query and Export multiple "filtered" versions of a Query (based on ...
    (microsoft.public.access.externaldata)