export a query to excel, but seperate fields by pages

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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?
Click to expand...

John Nurick said:
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?
Click to expand...
Click to expand...
 
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 & ";].[" &
strSheet _
& SQL2 & lngAgencyID & ";" the error i get is a runtime error that
Click to expand...
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?

John Nurick said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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!

John Nurick said:
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 & ";].[" &
strSheet _
& SQL2 & lngAgencyID & ";" the error i get is a runtime error that
Click to expand...
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?

John Nurick said:
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



On Wed, 14 Sep 2005 09:06:26 -0700, AmyNeedsHelp

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?
Click to expand...
Click to expand...
Click to expand...
 
The obvious problem is in this statement
'Assemble the SQL query to export one agency
strSQL = SQL1 & strFilespec & ";] " & SQL2 & lngAgencyID & ";"
Click to expand...

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 & ";"
Click to expand...

having previously set
strSheet = CStr(lngAgencyID)
Click to expand...
but you could equally use a constant name, e.g.
strSheet = "Sheet1"


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!

John Nurick said:
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 & ";].[" &
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,

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



On Wed, 14 Sep 2005 09:06:26 -0700, AmyNeedsHelp

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?
Click to expand...
Click to expand...
Click to expand...
 
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!!

John Nurick said:
The obvious problem is in this statement
'Assemble the SQL query to export one agency
strSQL = SQL1 & strFilespec & ";] " & SQL2 & lngAgencyID & ";"
Click to expand...

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 & ";"
Click to expand...

having previously set
strSheet = CStr(lngAgencyID)
Click to expand...
but you could equally use a constant name, e.g.
strSheet = "Sheet1"


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!

John Nurick said:
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.



On Thu, 15 Sep 2005 05:17:10 -0700, AmyNeedsHelp

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,

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



On Wed, 14 Sep 2005 09:06:26 -0700, AmyNeedsHelp

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?
Click to expand...
Click to expand...
Click to expand...
 
Glad you worked it out. What I originally posted wrote was "air code",
written rapidly into the message and never tested.
 
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???

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



AmyNeedsHelp said:
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!!
Click to expand...
Click to expand...
 
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-

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???

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



AmyNeedsHelp said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top