export a query to excel, but seperate fields by pages

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AmyNeedsHelp - 14 Sep 2005 17:06 GMT

Hi I need to export a query into excel , in the query i am ordering by the
agencyID field and i need to put each agency on its own page so when an
agencyID changes it should export the upcoming data to a new page in excel-
does anybody know if thats even possible- im using a macro to export the
query- or is there some way to do this in code?

Reply to this Message

John Nurick - 15 Sep 2005 07:13 GMT

Hi Amy,

I'm not sure it's possible to do this in a macro, but it can certainly
be done using VBA.

The general idea is

1) open a recordset containing just a list of agencyIDs that need to be
exported

2) for each agencyID, export the records for just that agency.

The VBA could look vaguely like this:

Dim dbD as DAO.Database
Dim rsAgencies As DAO.Recordset
Dim strFilespec As String
Dim lngAgencyID As Long
Dim strSheet As String
Dim strSQL As String

Const SQL1 = "SELECT * INTO [Excel 8.0;HDR=Yes;Database="
Const SQL2 = "FROM qryRecordsToExport WHERE AgencyID="

Set dbD = CurrentDB()
Set rsAgencies = dbD.OpenRecordset("qryAgenciesToExport", _
dbOpenSnapshot)
strFilespec = "C:\Folder\File.xls"

Do Until rsAgencies.EOF
'Get AgencyID
lngAgencyID = rsAgencies.Fields("AgencyID").Value
strSheet = CStr(lngAgencyID)

'Assemble the SQL query to export one agency
strSQL = SQL1 & strFilespec & ";].[" & strSheet _
& SQL2 & lngAgencyID & ";"
'export it
dbD.Execute strSQL, dbFailOnError
rsAgencies.MoveNext
Loop

rsAgencies.Close

>Hi I need to export a query into excel , in the query i am ordering by the
>agencyID field and i need to put each agency on its own page so when an
>agencyID changes it should export the upcoming data to a new page in excel-
>does anybody know if thats even possible- im using a macro to export the
>query- or is there some way to do this in code?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

AmyNeedsHelp - 15 Sep 2005 13:17 GMT

Hey thanks for replying quickly- I've tried what u said and im still having
some trouble with the statement strSQL = SQL1 & strFilespec & ";].[" &
strSheet _

> & SQL2 & lngAgencyID & ";" the error i get is a runtime error that says [Excel 8.0;HDR=Yes;Database=U:\AmyS\GroupReports\testFile.xls;] is not a valid name, i was wondering if maybe the Excel 8.0 is throwing it off, i have office 2003 and im not sure if i need to put Excel 2003 or Excel with a different version number?

> Hi Amy,
>
[quoted text clipped - 50 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

John Nurick - 15 Sep 2005 21:40 GMT

The Excel 8.0 shouldn't be a problem - it's what I use in Access 2003.

I can't reproduce the error you're getting, unless it's saying "not a
valid path" rather than "not a valid name". If that's the case, it means
that the path you provided doesn't exist.

Try setting a breakpoint on the line
dbD.Execute strSQL, dbFailOnError
Then go to the Immediate pane and type
?strSQL
to see the SQL statement.

Maybe you'll spot the problem then; if not, paste the SQL statement into
your next post here.

>Hey thanks for replying quickly- I've tried what u said and im still having
>some trouble with the statement strSQL = SQL1 & strFilespec & ";].[" &
[quoted text clipped - 59 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

AmyNeedsHelp - 20 Sep 2005 15:29 GMT

heres my code-

Dim dbD As DAO.Database
Dim rsAgencies As DAO.Recordset
Dim strFilespec As String
Dim lngAgencyID As Long
Dim strSheet As String
Dim strSQL As String

Const SQL1 = "SELECT * INTO [Excel 8.0;HDR=Yes;Database="
Const SQL2 = "FROM orderData WHERE AgencyID="

Set dbD = CurrentDb()
Set rsAgencies = dbD.OpenRecordset("SELECT * FROM agencies WHERE GroupID =
31", _
dbOpenSnapshot)
strFilespec = "U:\AmyS\GroupReports\testFile.xls"

Do Until rsAgencies.EOF

'Get AgencyID
lngAgencyID = rsAgencies.Fields("AgencyID").value
strSheet = CStr(lngAgencyID)

'Assemble the SQL query to export one agency
strSQL = SQL1 & strFilespec & ";] " & SQL2 & lngAgencyID & ";"

'export it
dbD.Execute strSQL, dbFailOnError
rsAgencies.MoveNext

Loop

rsAgencies.Close

the strSQL is SELECT *
INTO[Excel8.0;HDR=Yes;Database=U:\AmyS\GroupReports\testFile.xls;] FROM
orderData WHERE AgencyID=78;

and im still getting the same error- if u see where my problem is, let me
know- thanks so much!

> The Excel 8.0 shouldn't be a problem - it's what I use in Access 2003.
>
[quoted text clipped - 79 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

John Nurick - 21 Sep 2005 07:25 GMT

The obvious problem is in this statement

> 'Assemble the SQL query to export one agency
> strSQL = SQL1 & strFilespec & ";] " & SQL2 & lngAgencyID & ";"

You're not providing a worksheet name, and you have to do so. In my
original post I said

> 'Assemble the SQL query to export one agency
> strSQL = SQL1 & strFilespec & ";].[" & strSheet _
> & SQL2 & lngAgencyID & ";"

having previously set

> strSheet = CStr(lngAgencyID)

but you could equally use a constant name, e.g.
strSheet = "Sheet1"

>heres my code-
>
[quoted text clipped - 121 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

AmyNeedsHelp - 21 Sep 2005 17:44 GMT

ok, I used the sql query you had in the original post and it gave me a syntax
error, this is what the statement looked like in the immediate window

SELECT * INTO [Excel
8.0;HDR=Yes;Database=U:\AmyS\GroupReports\testFile.xls;].[78FROM orderData
WHERE AgencyID=78;

which the sheet part looked funny to me and i thought maybe it was not
needed so i took that part out- however i figured it out now, thank you so
much, its very neat how stuff works- thanks again!!

> The obvious problem is in this statement
>
[quoted text clipped - 143 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

John Nurick - 21 Sep 2005 18:21 GMT

Glad you worked it out. What I originally posted wrote was "air code",
written rapidly into the message and never tested.

> ok, I used the sql query you had in the original post and it gave me a
> syntax
[quoted text clipped - 7 lines]
> needed so i took that part out- however i figured it out now, thank you so
> much, its very neat how stuff works- thanks again!!

Reply to this Message

AmyNeedsHelp - 21 Sep 2005 18:43 GMT

Hi-

i actually have another question, but not sure if its even possible to do-
i have some records that are the same all the way through except one field.

ex. field names: ordernumber name btn feature
ex. values: 123456789 jane 1234567890 call waiting
123456789 jane 1234567890 call forwarding

what they want in the excel sheet is this

ordernumber name btn feature
123456789 jane 1234567890 call waiting --- call forwarding

so it combines the records sort of, i was thinking of testing to see if
those fields that are the same to see if there are the same as it goes
through the loop and then if they are then concatenate the last field value
to the previous record field value, does that sound right or is there a
better way???

> Glad you worked it out. What I originally posted wrote was "air code",
> written rapidly into the message and never tested.
[quoted text clipped - 10 lines]
> > needed so i took that part out- however i figured it out now, thank you so
> > much, its very neat how stuff works- thanks again!!

Reply to this Message

John Nurick - 21 Sep 2005 20:31 GMT

Hi Amy,

It can be done. Get the fConcatFld() function from
http://www.mvps.org/access/modules/mdl0008.htm
and paste it into a module in your database. Then modify the code you
already have so that the queries you build include a calculated field
that calls fConcatFld().

Here's an example from my test database of a finished query:

SELECT
Firstname,
fConcatFld("AddrNew", "FirstName", "LastName", "String",
[FirstName]) AS Surnames
INTO [Excel 8.0;HDR=Yes;Database=C:\Temp\TestConcat.xls;].[Sheet1]
FROM AddrNew
WHERE City="London";

With your example field names, it would be more like

fConcatFld("AmysTable", "ordernumber", "feature", "Long",
[ordernumber]) AS Features

>Hi-
>
[quoted text clipped - 31 lines]
>> > needed so i took that part out- however i figured it out now, thank you so
>> > much, its very neat how stuff works- thanks again!!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message