How do you dump the contents of a query into an Excel speadsheet?

[Douglas J. Steele]

Take a look at my July, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for a sample of how to use Automation to format
a spreadsheet. You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug-

That did the trick!!!

All I need to figure out now is how to have access "auto size to fit" the
columns and center all the fields in my spread sheets and I will be off
the
the races.

Thank you to everyone who contributed... your help is appreciated!

If AEEG is supposed to be the value for which you're looking, that should
be

SELECT *
FROM Product_tbl
WHERE (((Product_tbl.ProdFam)='AEEG'));

That means you need

strSQL = "SELECT * FROM Product_tbl WHERE " & _
"ProdFam= '" & rstProduct!product.Value & "'"

Exagerated for clarity, that's


strSQL = "SELECT * FROM Product_tbl WHERE " & _
"ProdFam= ' " & rstProduct!product.Value & " ' "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the info Doug...

This worked and I then changed this to the following in order to
name each worksheet by the name of the Product:

strProd = Nz(DLookup("ProdFam", "Product_tbl", "Product = '" &
rstProduct!product.Value & "'"), rstProduct!product.Value)

Also, I found that in order to get any data from my product table
that I needed to change the following (where "ProdFam" now replaces
"Product"):

strSQL = "SELECT * FROM Product_tbl WHERE " & _
"ProdFam= " & rstProduct!product.Value & ";"

There still is one thing to resolve though as my query is not pulling
the data that I want it to for the following reason:

When the following line of code is executed:

qdf.SQL = strSQL

The query called AEEG (Prodfam of the first record found) is created
with
the following SQL code:

SELECT *
FROM Product_tbl
WHERE (((Product_tbl.ProdFam)=[AEEG]));

The problem is that I need the "WHERE" parameter to be created without
the
brackets Like this:

SELECT *
FROM Product_tbl
WHERE (((Product_tbl.ProdFam)=AEEG));

But I am unsure how to correct this...

Here is a portion of my code:
-----
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstProduct As DAO.Recordset
Dim strSQL As String, strTemp As String, strProd As String

Const strFileName As String = "Product Part Numbers"
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 DISTINCT Product FROM Productname_tbl;"
Set rstProduct = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstProduct.EOF = False And rstProduct.BOF = False Then
rstProduct.MoveFirst

Do While rstProduct.EOF = False

' the following line of code specifies the names of worksheets ProdFam
strProd = Nz(DLookup("ProdFam", "Product_tbl", "Product = '" &
rstProduct!product.Value & "'"), rstProduct!product.Value)

strSQL = "SELECT * FROM Product_tbl WHERE " & _
"ProdFam= " & rstProduct!product.Value & ";"

Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = strProd
strTemp = qdf.Name
qdf.SQL = strSQL

qdf.Close
Set qdf = Nothing
-----

any ideas how to get those brackets out of the query criteria?

This is getting so close....thanks again


--------------------------------
:

That means that there isn't a value in the table corresponding to the
value
for which you're searching.

When no record is found, DLookup returns Null, and String variables
(which
I'm assuming strProd is because of its name) cannot store Null values.
The
only data type that can store Null values is the Variant.

Try:

strProd = Nz(DLookup("ProdFam", "Product_tbl", _
"Product = '" & rstProduct!product.Value & "'"), "Not found")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks John-

I tried both:

strProd = DLookup("ProdFam", "Product_tbl",
"Product = '" & rstProduct!product.Value & "'")

and

strProd = DLookup("ProdFam", "Product_tbl",
"Product = " & Chr(34) & rstProduct!product.Value & Chr(34))

and received a run time error '94' Invalid Use of Null.

What am I doing wrong?


:

Your third argument (as posted) is missing an apostrophe before the
quote.

strProd = DLookup("ProdFam", "Product_tbl",
"Product = '" & rstProduct!product.Value & "'")

You could write that this way - using the Chr(34) to add the needed
quote marks.

strProd = DLookup("ProdFam", "Product_tbl",
"Product = " & Chr(34) & rstProduct!product.Value & Chr(34))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jeff wrote:
I still can't figure out why this is giving me a 3075 syntax
error
on
the line:

strProd = DLookup("ProdFam", "Product_tbl", "Product = " &
rstProduct!product.Value & "'")

When I paste the SQL code that I am using for rstProduct (SELECT
DISTINCT
Product FROM Productname_tbl into a query I get the following
result:

Product
AEEG
CEEG
LTM
NEEG
VEEG

A couple of entries from the Product_tbl table are:

ProdFam Part No Description Type
AEEG 1234 widget 1 1
CEEG 2344 widget 2 3


and the error that I am receiving is:
Run-time error '2075'; Syntax error in string in query expression
'Product =
AEEG".

I have verified that there are no additional spaces in the fields
of
either
table but am not sure what else I can do to solve this problem...

My intent is to pull all records out of the Product_tbl that
match
the
Product field from the ProductName_tbl and then dump each record
into
an
individual worksheet in excel (that is named by the Product
field)

I am at my wit's end on this.... Any ideas?

thanks
=======================

Here is my code which I modified from your example:

Private Sub Command0_Click()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstProduct As DAO.Recordset
Dim strSQL As String, strTemp As String, strProd As String

Const strFileName As String = "Product Part Numbers"
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 DISTINCT Product FROM Productname_tbl;"
Set rstProduct = dbs.OpenRecordset(strSQL, dbOpenDynaset,
dbReadOnly)

If rstProduct.EOF = False And rstProduct.BOF = False Then
rstProduct.MoveFirst

Do While rstProduct.EOF = False
strProd = DLookup("ProdFam", "Product_tbl", "Product= "
&
rstProduct!product.Value & "'")

strSQL = "SELECT * FROM Product_tbl WHERE " & _
"Product= " & rstProduct!product.Value & ";"

Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strProd
strTemp = qdf.Name
qdf.SQL = strSQL141


qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, "C:\" & strFileName & ".xls"
rstProduct.MoveNext
Loop
End If

rstProduct.Close
Set rstProduct = Nothing

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


End Sub
===============================



:

The error that you're getting suggests that the ' delimiters
are
not
in the
code step that uses the DLookup function -- notwithstanding
what
you've
posted.

Check your code again to be sure. And then copy the code from
the
procedure
and post it here so that we can review it.