Saving recordsets and exporting to Excel

[Michael]

This is probably very basic, but I would appreciate any
advice. I have created a recordset which is a selection
of records based on user input. I now want to save that
recordset as an excel file. I thought the best was to do
this was to first save the recordset as as one of my
Access tables and then export it to excel. However I
cannot find a way of saving the recordset. I would have
thought it was a relatively straight forward function.
If anyone knows how to do this I would be grateful for the
help

 

[Van T. Dinh]

How do you create the Recordset in the first place?

If your Recordset is based on a Query, then you can simply
export the Query to an Excel file. The Excel file should
then have the rows of the Query / Recordset.

HTH
Van T. Dinh
MVP (Access)

 

[Michael]

Thanks for that

I am just unclear. I select the recordset using a string
that makes a SQL ststement as per the following


SQLtext = "SELECT [Link Table].SLA_Main, etc"
Set rstSLA = dbsData.OpenRecordset(SQLtext, dbOpenDynaset)

Does this create a query?

If it does how do you refer to it in the export to Excel

Thanks again for your help

Cheers

Michael

 

[Guest]

you are creating an object by inferrence

your "set rstSLA =" statement creates an instance of a recordse

"dim rstSLA as recordset

would be the explicit dimensioning of the recordset you then add records to using

set rstSLA = dbsData.OpenRecordset(SQLtext, dbOpenDynaset

Use 'Option Explicit' in the code header instead of 'option compare db' to force the identification of all variables explicitl
This can help you to understand where you are using system variables or local ones you are creatin


----- Michael wrote: ----

Thanks for tha

I am just unclear. I select the recordset using a string
that makes a SQL ststement as per the followin


SQLtext = "SELECT [Link Table].SLA_Main, etc
Set rstSLA = dbsData.OpenRecordset(SQLtext, dbOpenDynaset

Does this create a query

If it does how do you refer to it in the export to Exce

Thanks again for your hel

Cheer

Michae

 

[Michael]

Thanks for that

I have used the Option Explicit declaratiuon and declared
rstSLA as a recordset.

I am still having problems when I get to the last line of
this code (DoCmd)

SQLtext = "SELECT [Link Table].SLA_Main, [Link
Table].SLA_Name, etc"
Set rstSLA = dbsData.OpenRecordset(SQLtext) ' ,
dbOpenDynaset)

DoCmd.TransferSpreadsheet acExport,
8, "rstSLA", "C:\data\temp1.xls"

Is this the right method to use?? The error message I
get is that the Jet engine could not find the
object 'rstSLA'

-----Original Message-----
you are creating an object by inferrence:

your "set rstSLA =" statement creates an instance of a recordset

"dim rstSLA as recordset"

would be the explicit dimensioning of the recordset you then add records to using:

set rstSLA = dbsData.OpenRecordset(SQLtext, dbOpenDynaset)

Use 'Option Explicit' in the code header instead

of 'option compare db' to force the identification of all
variables explicitly

This can help you to understand where you are using

system variables or local ones you are creating

----- Michael wrote: -----

Thanks for that

I am just unclear. I select the recordset using a string
that makes a SQL ststement as per the following


SQLtext = "SELECT [Link Table].SLA_Main, etc"
Set rstSLA = dbsData.OpenRecordset(SQLtext, dbOpenDynaset)

Does this create a query?

If it does how do you refer to it in the export to Excel

Thanks again for your help

Cheers

Michael

-----Original Message-----
How do you create the Recordset in the first place?

can

simply
export the Query to an Excel file. The Excel file should
then have the rows of the Query / Recordset.
Van T. Dinh
MVP (Access)

save

that was to
do for
the

.

 

[JimB]

Michael,
see my posted reply to Ross, subject: Copy Excel Sheet
From Access. I put two sample modules that might help.

-----Original Message-----
Thanks for that

I have used the Option Explicit declaratiuon and declared
rstSLA as a recordset.

I am still having problems when I get to the last line of
this code (DoCmd)

SQLtext = "SELECT [Link Table].SLA_Main, [Link
Table].SLA_Name, etc"
Set rstSLA = dbsData.OpenRecordset(SQLtext) ' ,
dbOpenDynaset)

DoCmd.TransferSpreadsheet acExport,
8, "rstSLA", "C:\data\temp1.xls"

Is this the right method to use?? The error message I
get is that the Jet engine could not find the
object 'rstSLA'

-----Original Message-----
you are creating an object by inferrence:

your "set rstSLA =" statement creates an instance of a recordset

"dim rstSLA as recordset"

would be the explicit dimensioning of the recordset you then add records to using:

set rstSLA = dbsData.OpenRecordset(SQLtext, dbOpenDynaset)

Use 'Option Explicit' in the code header instead

of 'option compare db' to force the identification of all
variables explicitly

This can help you to understand where you are using

system variables or local ones you are creating

----- Michael wrote: -----

Thanks for that

I am just unclear. I select the recordset using a string
that makes a SQL ststement as per the following


SQLtext = "SELECT [Link Table].SLA_Main, etc"
Set rstSLA = dbsData.OpenRecordset(SQLtext, dbOpenDynaset)

Does this create a query?

If it does how do you refer to it in the export to Excel

Thanks again for your help

Cheers

Michael

-----Original Message-----
How do you create the Recordset in the first place?
If your Recordset is based on a Query, then you

can

simply
export the Query to an Excel file. The Excel file should
then have the rows of the Query / Recordset.
HTH
Van T. Dinh
MVP (Access)
-----Original Message-----
This is probably very basic, but I would appreciate any
advice. I have created a recordset which is a selection
of records based on user input. I now want to

save

that
recordset as an excel file. I thought the best was to
do
this was to first save the recordset as as one of my
Access tables and then export it to excel. However I
cannot find a way of saving the recordset. I would have
thought it was a relatively straight forward function.
If anyone knows how to do this I would be

grateful

for
.

.

 

[Van T. Dinh]

You have to create a Query since the TransferSpreadsheet only accepts Table
Name or Query name as the Source.

You can create just a dummy saved Query "qryForExport". Anything will do
since you will modify the SQL String in code.

Add DAO Library into your reference and use code like:

****Code snippets only***
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs("qryForExport")
qdf.SQL = "Your SQL String"
qdf.Close
DoEvents

DoCmd.TransferText, acExport, , "qryForExport" ....

Set qdf = Nothing
Set db = Nothing
****

 

[Michael]

Thanks Heaps

-----Original Message-----
You have to create a Query since the TransferSpreadsheet only accepts Table
Name or Query name as the Source.

You can create just a dummy saved Query "qryForExport". Anything will do
since you will modify the SQL String in code.

Add DAO Library into your reference and use code like:

****Code snippets only***
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs("qryForExport")
qdf.SQL = "Your SQL String"
qdf.Close
DoEvents

DoCmd.TransferText, acExport, , "qryForExport" ....

Set qdf = Nothing
Set db = Nothing
****

--
HTH
Van T. Dinh
MVP (Access)

Thanks for that

I have used the Option Explicit declaratiuon and declared
rstSLA as a recordset.

I am still having problems when I get to the last line of
this code (DoCmd)

SQLtext = "SELECT [Link Table].SLA_Main, [Link
Table].SLA_Name, etc"
Set rstSLA = dbsData.OpenRecordset(SQLtext) ' ,
dbOpenDynaset)

DoCmd.TransferSpreadsheet acExport,
8, "rstSLA", "C:\data\temp1.xls"

Is this the right method to use?? The error message I
get is that the Jet engine could not find the
object 'rstSLA'

.

 

[Michael]

Thanks for your help

It was just what I needed

-----Original Message-----
Michael,
see my posted reply to Ross, subject: Copy Excel Sheet
From Access. I put two sample modules that might help.

-----Original Message-----
Thanks for that

I have used the Option Explicit declaratiuon and declared
rstSLA as a recordset.

I am still having problems when I get to the last line of
this code (DoCmd)

SQLtext = "SELECT [Link Table].SLA_Main, [Link
Table].SLA_Name, etc"
Set rstSLA = dbsData.OpenRecordset(SQLtext) ' ,
dbOpenDynaset)

DoCmd.TransferSpreadsheet acExport,
8, "rstSLA", "C:\data\temp1.xls"

Is this the right method to use?? The error message I
get is that the Jet engine could not find the
object 'rstSLA'

-----Original Message-----
you are creating an object by inferrence:

your "set rstSLA =" statement creates an instance of a recordset

"dim rstSLA as recordset"

would be the explicit dimensioning of the recordset you then add records to using:

set rstSLA = dbsData.OpenRecordset(SQLtext, dbOpenDynaset)

Use 'Option Explicit' in the code header instead

of 'option compare db' to force the identification of all
variables explicitly

This can help you to understand where you are using

system variables or local ones you are creating

----- Michael wrote: -----

Thanks for that

I am just unclear. I select the recordset using a string
that makes a SQL ststement as per the following


SQLtext = "SELECT [Link Table].SLA_Main, etc"
Set rstSLA = dbsData.OpenRecordset(SQLtext, dbOpenDynaset)

Does this create a query?

If it does how do you refer to it in the export to Excel

Thanks again for your help

Cheers

Michael


-----Original Message-----
How do you create the Recordset in the first place?
If your Recordset is based on a Query, then you can
simply
export the Query to an Excel file. The Excel

file
should

then have the rows of the Query / Recordset.
HTH
Van T. Dinh
MVP (Access)
-----Original Message-----
This is probably very basic, but I would appreciate any
advice. I have created a recordset which is a
selection
of records based on user input. I now want to save
that
recordset as an excel file. I thought the best was to
do
this was to first save the recordset as as one

of
my

Access tables and then export it to excel. However I
cannot find a way of saving the recordset. I would have
thought it was a relatively straight forward function.
If anyone knows how to do this I would be

grateful

for
the
help
.
.

.

.

.

 

[Michael]

Thanks for that suggestion. It looks like a neat
solution. I have one more question.

I have added the DAO 3.6 Object library into reference (It
was already there)
I have created a dummy saved query called 'ExcelQuery'

and the code I have is

Dim dbsData As DAO.Database
Dim GeogQuery As DAO.QueryDef
Set dbsData = DBEngine.Workspaces(0).Databases(0)
Set GeogQuery = dbsData.QueryDefs("ExcelQuery")

Am I doing something wrong bcause it gets to the last line
and I get an error message "Item not found in this
collection". Is there someting I have left out?

Cheers

Michael

-----Original Message-----
You have to create a Query since the TransferSpreadsheet only accepts Table
Name or Query name as the Source.

You can create just a dummy saved Query "qryForExport". Anything will do
since you will modify the SQL String in code.

Add DAO Library into your reference and use code like:

****Code snippets only***
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs("qryForExport")
qdf.SQL = "Your SQL String"
qdf.Close
DoEvents

DoCmd.TransferText, acExport, , "qryForExport" ....

Set qdf = Nothing
Set db = Nothing
****

--
HTH
Van T. Dinh
MVP (Access)

Thanks for that

I have used the Option Explicit declaratiuon and declared
rstSLA as a recordset.

I am still having problems when I get to the last line of
this code (DoCmd)

SQLtext = "SELECT [Link Table].SLA_Main, [Link
Table].SLA_Name, etc"
Set rstSLA = dbsData.OpenRecordset(SQLtext) ' ,
dbOpenDynaset)

DoCmd.TransferSpreadsheet acExport,
8, "rstSLA", "C:\data\temp1.xls"

Is this the right method to use?? The error message I
get is that the Jet engine could not find the
object 'rstSLA'

.

 

[Jamie Collins]

...

I have created a recordset which is a selection
of records based on user input. I now want to save that
recordset as an excel file.

No recordset required.

To insert data into an existing Excel table:

INSERT INTO
[Excel 8.0;database=C:\Tempo\db.xls;].MyExcelTable
(ColA, ColB)
SELECT MyCol1 AS ColA, MyCol2 AS ColB
FROM MyTable

To create a new Excel table:

SELECT
MyCol1 AS ColA, MyCol2 AS ColB
INTO
[Excel 8.0;database=C:\Tempo\db.xls;].MyNewTable
FROM MyTable

Jamie.

--

 

[Van T. Dinh]

The code looks fine.

Which line of code errored out?