open access recordset as an excel spreadsheet

G

Guest

I have a query by form where I would like to open the recordset that
comprises the subform in Excel if the user wishes to further analyze the data.

I have some code from elsewhere that will open Excel and label the columns
but I am not getting the recordset into the spreadsheet.

The code follows, any help will be appreciated. Thanks:

Option Compare Database



Public mobjXl As Excel.Application







Public Function ExportToExcel(strqrySQL As String)
Dim rst As ADODB.Recordset
Dim intCount As Integer



' Create the Excel object

Set mobjXl = New Excel.Application
Set rst = New ADODB.Recordset




' Fetch the recordset
With rst
.ActiveConnection = CurrentProject.Connection.ConnectionString
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.MaxRecords = 65000 ' Approx max number of rows Excel can handle
.Open strqrySQL

End With

With mobjXl
' Add a workbook and turn off Excel updates
.ScreenUpdating = True
.Visible = False
.Workbooks.Add
.DisplayAlerts = False

' Add the column headers
For intCount = 0 To rst.Fields.Count - 1
.Cells(1, intCount + 1).Value = rst.Fields(intCount).name
Next intCount



' Dump the recordset to Excel

.Range("A2").CopyFromRecordset rst



.Visible = True

End With


' Add your error handler


End Function
 
N

Norman Goetz

On Fri, 18 Mar 2005 18:03:02 -0800, tmort wrote:
Option Compare Database

Public mobjXl As Excel.Application

Public Function ExportToExcel(strQrySQL As String)
Dim rst As ADODB.Recordset
Dim intCount As Integer
Set mobjXl = New Excel.Application
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
'.ConnectionString
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open strQrySQL
End With
With mobjXl
.ScreenUpdating = True
.Visible = False
.Workbooks.Add
.DisplayAlerts = False
For intCount = 0 To rst.Fields.Count - 1
.Cells(1, intCount + 1).Value = rst.Fields(intCount).Name
Next intCount
.Range("A2").CopyFromRecordset rst, 65000, 255
.Visible = True
End With
End Function

Norman Götz
 
V

Van T. Dinh

Have you checked to make sure that the Recordset is not empty with the
supplied SQL String?
 
T

tmort

Van said:
Have you checked to make sure that the Recordset is not empty with the
supplied SQL String?
Click to expand...
I know the SQL string gets me a recordset because I use it to populate
the recordset for a subform when another command button is pressed. I
alsl know that this sql string gets passed to my function.

What I'm not sure of though is whether I'm applying it correctly so
that my recordset is associated with this sql statement.

I was thinking that the section of the code

' Fetch the recordset
With rst
.ActiveConnection = CurrentProject.Connection.ConnectionString
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.MaxRecords = 65000 ' Approx max number of rows Excel can
handle
.Open strqrySQL

Where I say to open strqrySQL will make my rst be based on that sql
statement.

I set the subform on the other button with

Me.Child0.Form.RecordSource = strqrySQL
 
N

Norman Goetz

On 31 Mar 2005 22:08:35 -0800, (e-mail address removed)-rapids.mi.us wrote:
You can't assign a Object Connection to a string !
I wrote a working version earlier in this thread

With rst
.ActiveConnection = CurrentProject.Connection.ConnectionString
Click to expand...
<snip>
Norman Götz
 
G

Guest

I've copied the code you wrote and I get the same results - titles for the
columns but not the data.
 
V

Van T. Dinh

In my code, I use Cells of the Worksheet rather than Range. Here the actual
code from one of my databases:

WSheet.Cells(9, 1).CopyFromRecordset rst

In your code, you used the Range Object of the Application. I think they
meant the same thing as Cells returns a Range Object and there is only 1
Range Object in an Excel application.

Another difference is that I used DAO Recordset rather than ADO Recordset
but I don't think this matters.

Perhaps, you can try using Cells instead of range

Also add the following code after .Open strqrySQL

DoEvents
Debug.Print .RecordCount

and check the Debug windows to make sure that the Recordset is not empty.

--
HTH
Van T. Dinh
MVP (Access)

P.S. Norman is correct about the Connection.
 
G

Guest

I guess it works OK but the recordset is empty. The debug window returns zero.

So my question has changed to how do I set the recordset to the results of
the SQL string?

Thanks
 
V

Van T. Dinh

THe statement .Open strqrySQL is supposed to create / open the Recordset.

It sounds to me the problem is in your SQL String which we got no ideas as
you haven't posted it. Since you wrote that it works for the Subform (in
databases, Forms use internal processing similar to DAO and not ADO), you
may need to change SQL slightly to use ADO code, especially when you use
wild cards since wild cards are different in DAO and ADO.
 
T

tmort

The actual SQL statement depends on values from the form. The
statement is:

strFinalSQLStatement = strDefaultSQLString + " " + strSQLClause1 + " "
+ strSQLClause2 + " " + strSQLClause3 + " " + strSQLClause4 + " " +
strSQLClause5 + " " + strSQLClause6 + " " + strSQLClause7 + " " +
strSQLClause8 + " " + strOrderByClause

strqrySQL = strFinalSQLStatement


'set the new recordsource
Me.Child0.Form.RecordSource = strFinalSQLStatement

End Sub


Private Function strFixedSQLClause(strSQLClause As String) As String

If fFirstSQLClause = True Then
fFirstSQLClause = False
strFixedSQLClause = "WHERE " + strSQLClause
Else
strFixedSQLClause = "AND " + strSQLClause
End If

End Function

I'm not sure what I would have to change in the SQL string to go from
DAO to ADO but since it is passed from another fuction I'm wondering if
it I should creaate the equivalent ADO string in this function and pass
it on rather than trying to convert or maybe change the open as Excel
to DAO??

Please advise. Thanks.
 
V

Van T. Dinh

You only posted these variables strSQLClauseX which only you know.

Post the final SQL String strqrySQL as it is passed to the Open method in
your first post.
 
G

Guest

Here's a string that isn't working right in regards to getting data into Excel:

SELECT [qry Analyzed Results].[Outfall Number], [qry Analyzed
Results].[Outfall Name], [qry Analyzed Results].[Collection Date], [qry
Analyzed Results].Analyte, [qry Analyzed Results].Result, [qry Analyzed
Results].[Result Number], [qry analyzed results].[units], [qry Analyzed
Results].[Compliance Sample], [qry Analyzed Results].[Sample Type], [qry
Analyzed Results].Sampler FROM [qry Analyzed Results] WHERE [qry Analyzed
Results].Analyte = "Copper, total" ORDER BY [qry Analyzed
Results].[Collection Date]
 
V

Van T. Dinh

Copy the posted String into the SQL View of a new Query and run it and see
if it returns an empty Recordset or not. What you posted looks fine but
then I don't know your query [qry Analyzed Results].

Check also the return of [qry Analyzed Results].
 
G

Guest

I pasted it in SQL view and it returns the specified results

Van T. Dinh said:
Copy the posted String into the SQL View of a new Query and run it and see
if it returns an empty Recordset or not. What you posted looks fine but
then I don't know your query [qry Analyzed Results].

Check also the return of [qry Analyzed Results].

--
HTH
Van T. Dinh
MVP (Access)



tmort said:
Here's a string that isn't working right in regards to getting data into Excel:

SELECT [qry Analyzed Results].[Outfall Number], [qry Analyzed
Results].[Outfall Name], [qry Analyzed Results].[Collection Date], [qry
Analyzed Results].Analyte, [qry Analyzed Results].Result, [qry Analyzed
Results].[Result Number], [qry analyzed results].[units], [qry Analyzed
Results].[Compliance Sample], [qry Analyzed Results].[Sample Type], [qry
Analyzed Results].Sampler FROM [qry Analyzed Results] WHERE [qry Analyzed
Results].Analyte = "Copper, total" ORDER BY [qry Analyzed
Results].[Collection Date]
Click to expand...
Click to expand...
 
V

Van T. Dinh

Post the SQL String of [qry Analyzed Results]. My guess is that there are
wild cards in this query (see my 3rd last post before this one).
 
T

tmort

Qry Analyzed Results does have a wild card in it. It also references
another query, samples and results, which I also am posting.

Samples and Results

SELECT Results.[Company Name], Results.[Outfall Number],
Results.[Outfall Name], Results.[Collection Date],
Samples.CollectionEndDate, Results.[Sample Type], Results.Sampler,
Results.[Date Lab Received], Results.[Analysis Date], Results.[Method
ID], Results.[Method Description], Results.Analyte, Results.Result,
Results.Units, Results.[Reporting Limit], Results.[Lab Sample ID],
Results.[Lab Name], Results.[Compliance Sample], Results.ID,
Samples.[Permit Number], Samples.[Sample Location], Samples.autonumber,
Samples.[Sample Notes]
FROM Samples INNER JOIN Results ON (Samples.[Collection Date] =
Results.[Collection Date]) AND (Samples.Sampler = Results.Sampler) AND
(Samples.[Company Name] = Results.[Company Name]) AND (Samples.[Outfall
Number] = Results.[Outfall Number]) AND (Samples.[Compliance Sample] =
Results.[Compliance Sample]);

Qry Analyzed Results

SELECT [samples and results].[Company Name], [samples and
results].[Permit Number], [samples and results].[Outfall Number],
[samples and results].[Collection Date], [samples and
results].CollectionEndDate, [samples and results].[Sample Type],
[samples and results].[Compliance Sample], [samples and
results].Sampler, [samples and results].[Date Lab Received], [samples
and results].[Analysis Date], [samples and results].[Method ID],
[samples and results].[Method Description], [samples and
results].Analyte, [samples and results].Result, [samples and
results].Units, [samples and results].[Reporting Limit], [samples and
results].[Lab Sample ID], [samples and results].[Lab Name], nz([Results
and Limits].[Result Number],0) AS [Result Number], nz([Results and
Limits].[Daily Lower Limit],0) AS [Daily Lower Limit], nz([Results and
Limits].[Daily Upper Limit],0) AS [Daily Upper Limit], nz([Results and
Limits].[Monthly Lower Limit],0) AS [Monthly Lower Limit], nz([Results
and Limits].[Monthly Upper Limit],0) AS [Monthly Upper Limit], [Results
and Limits].[Compliance Status], [Results and Limits].[Monthly Upper
String], [Results and Limits].Expr2, [Results and Limits].[Violation
Comments], [Results and Limits].[Corrective Actions], [samples and
results].[Outfall Name], [samples and results].[Permit Number],
[samples and results].[Sample Notes], [samples and results].ID AS
[results ID], Count([results ID]) AS cnt
FROM [Results and Limits] LEFT JOIN [samples and results] ON [Results
and Limits].ID = [samples and results].ID
GROUP BY [samples and results].[Company Name], [samples and
results].[Permit Number], [samples and results].[Outfall Number],
[samples and results].[Collection Date], [samples and
results].CollectionEndDate, [samples and results].[Sample Type],
[samples and results].[Compliance Sample], [samples and
results].Sampler, [samples and results].[Date Lab Received], [samples
and results].[Analysis Date], [samples and results].[Method ID],
[samples and results].[Method Description], [samples and
results].Analyte, [samples and results].Result, [samples and
results].Units, [samples and results].[Reporting Limit], [samples and
results].[Lab Sample ID], [samples and results].[Lab Name], nz([Results
and Limits].[Result Number],0), nz([Results and Limits].[Daily Lower
Limit],0), nz([Results and Limits].[Daily Upper Limit],0), nz([Results
and Limits].[Monthly Lower Limit],0), nz([Results and Limits].[Monthly
Upper Limit],0), [Results and Limits].[Compliance Status], [Results and
Limits].[Monthly Upper String], [Results and Limits].Expr2, [Results
and Limits].[Violation Comments], [Results and Limits].[Corrective
Actions], [samples and results].[Outfall Name], [samples and
results].[Permit Number], [samples and results].[Sample Notes],
[samples and results].ID
HAVING ((([samples and results].[Collection Date]) Like "*"))
ORDER BY [samples and results].[Collection Date];


How do you recommend I proceed? Thanks
Van said:
Post the SQL String of [qry Analyzed Results]. My guess is that there are
wild cards in this query (see my 3rd last post before this one).

--
HTH
Van T. Dinh
MVP (Access)



tmort said:
I pasted it in SQL view and it returns the specified results
Click to expand...
Click to expand...
 
V

Van T. Dinh

The problem lies in the criterion

((([samples and results].[Collection Date]) Like "*"))

because it is using the wild card "*" which is not recognised when you use
ADO code. The equivalent wild card in ADO is "%".

OTOH, I am not sure why you used Like + wild card on a DateTime Field since
Like is really a String comparison operator. This (most likely) requires
type-casting to String/Text on DateTime values and uses the CPU time
unnecessarily. If you don't want to select Null value for [Collection
Date], you can set the criterion to:

((([samples and results].[Collection Date]) Is Not Null))

--
HTH
Van T. Dinh
MVP (Access)



Qry Analyzed Results does have a wild card in it. It also references
another query, samples and results, which I also am posting.

Samples and Results

SELECT Results.[Company Name], Results.[Outfall Number],
Results.[Outfall Name], Results.[Collection Date],
Samples.CollectionEndDate, Results.[Sample Type], Results.Sampler,
Results.[Date Lab Received], Results.[Analysis Date], Results.[Method
ID], Results.[Method Description], Results.Analyte, Results.Result,
Results.Units, Results.[Reporting Limit], Results.[Lab Sample ID],
Results.[Lab Name], Results.[Compliance Sample], Results.ID,
Samples.[Permit Number], Samples.[Sample Location], Samples.autonumber,
Samples.[Sample Notes]
FROM Samples INNER JOIN Results ON (Samples.[Collection Date] =
Results.[Collection Date]) AND (Samples.Sampler = Results.Sampler) AND
(Samples.[Company Name] = Results.[Company Name]) AND (Samples.[Outfall
Number] = Results.[Outfall Number]) AND (Samples.[Compliance Sample] =
Results.[Compliance Sample]);

Qry Analyzed Results

SELECT [samples and results].[Company Name], [samples and
results].[Permit Number], [samples and results].[Outfall Number],
[samples and results].[Collection Date], [samples and
results].CollectionEndDate, [samples and results].[Sample Type],
[samples and results].[Compliance Sample], [samples and
results].Sampler, [samples and results].[Date Lab Received], [samples
and results].[Analysis Date], [samples and results].[Method ID],
[samples and results].[Method Description], [samples and
results].Analyte, [samples and results].Result, [samples and
results].Units, [samples and results].[Reporting Limit], [samples and
results].[Lab Sample ID], [samples and results].[Lab Name], nz([Results
and Limits].[Result Number],0) AS [Result Number], nz([Results and
Limits].[Daily Lower Limit],0) AS [Daily Lower Limit], nz([Results and
Limits].[Daily Upper Limit],0) AS [Daily Upper Limit], nz([Results and
Limits].[Monthly Lower Limit],0) AS [Monthly Lower Limit], nz([Results
and Limits].[Monthly Upper Limit],0) AS [Monthly Upper Limit], [Results
and Limits].[Compliance Status], [Results and Limits].[Monthly Upper
String], [Results and Limits].Expr2, [Results and Limits].[Violation
Comments], [Results and Limits].[Corrective Actions], [samples and
results].[Outfall Name], [samples and results].[Permit Number],
[samples and results].[Sample Notes], [samples and results].ID AS
[results ID], Count([results ID]) AS cnt
FROM [Results and Limits] LEFT JOIN [samples and results] ON [Results
and Limits].ID = [samples and results].ID
GROUP BY [samples and results].[Company Name], [samples and
results].[Permit Number], [samples and results].[Outfall Number],
[samples and results].[Collection Date], [samples and
results].CollectionEndDate, [samples and results].[Sample Type],
[samples and results].[Compliance Sample], [samples and
results].Sampler, [samples and results].[Date Lab Received], [samples
and results].[Analysis Date], [samples and results].[Method ID],
[samples and results].[Method Description], [samples and
results].Analyte, [samples and results].Result, [samples and
results].Units, [samples and results].[Reporting Limit], [samples and
results].[Lab Sample ID], [samples and results].[Lab Name], nz([Results
and Limits].[Result Number],0), nz([Results and Limits].[Daily Lower
Limit],0), nz([Results and Limits].[Daily Upper Limit],0), nz([Results
and Limits].[Monthly Lower Limit],0), nz([Results and Limits].[Monthly
Upper Limit],0), [Results and Limits].[Compliance Status], [Results and
Limits].[Monthly Upper String], [Results and Limits].Expr2, [Results
and Limits].[Violation Comments], [Results and Limits].[Corrective
Actions], [samples and results].[Outfall Name], [samples and
results].[Permit Number], [samples and results].[Sample Notes],
[samples and results].ID
HAVING ((([samples and results].[Collection Date]) Like "*"))
ORDER BY [samples and results].[Collection Date];


How do you recommend I proceed? Thanks
Click to expand...
 
G

Guest

Thanks for all the help!!!! I thought of the is not null about two minutes
after I sent the post.

One last thing regarding the Excel sheet; I've figured out how to set the
column widths in the code but I haven't figured out how to set the alignment
of all of the titles and cell contents to centered.

Van T. Dinh said:
The problem lies in the criterion

((([samples and results].[Collection Date]) Like "*"))

because it is using the wild card "*" which is not recognised when you use
ADO code. The equivalent wild card in ADO is "%".

OTOH, I am not sure why you used Like + wild card on a DateTime Field since
Like is really a String comparison operator. This (most likely) requires
type-casting to String/Text on DateTime values and uses the CPU time
unnecessarily. If you don't want to select Null value for [Collection
Date], you can set the criterion to:

((([samples and results].[Collection Date]) Is Not Null))

--
HTH
Van T. Dinh
MVP (Access)



Qry Analyzed Results does have a wild card in it. It also references
another query, samples and results, which I also am posting.

Samples and Results

SELECT Results.[Company Name], Results.[Outfall Number],
Results.[Outfall Name], Results.[Collection Date],
Samples.CollectionEndDate, Results.[Sample Type], Results.Sampler,
Results.[Date Lab Received], Results.[Analysis Date], Results.[Method
ID], Results.[Method Description], Results.Analyte, Results.Result,
Results.Units, Results.[Reporting Limit], Results.[Lab Sample ID],
Results.[Lab Name], Results.[Compliance Sample], Results.ID,
Samples.[Permit Number], Samples.[Sample Location], Samples.autonumber,
Samples.[Sample Notes]
FROM Samples INNER JOIN Results ON (Samples.[Collection Date] =
Results.[Collection Date]) AND (Samples.Sampler = Results.Sampler) AND
(Samples.[Company Name] = Results.[Company Name]) AND (Samples.[Outfall
Number] = Results.[Outfall Number]) AND (Samples.[Compliance Sample] =
Results.[Compliance Sample]);

Qry Analyzed Results

SELECT [samples and results].[Company Name], [samples and
results].[Permit Number], [samples and results].[Outfall Number],
[samples and results].[Collection Date], [samples and
results].CollectionEndDate, [samples and results].[Sample Type],
[samples and results].[Compliance Sample], [samples and
results].Sampler, [samples and results].[Date Lab Received], [samples
and results].[Analysis Date], [samples and results].[Method ID],
[samples and results].[Method Description], [samples and
results].Analyte, [samples and results].Result, [samples and
results].Units, [samples and results].[Reporting Limit], [samples and
results].[Lab Sample ID], [samples and results].[Lab Name], nz([Results
and Limits].[Result Number],0) AS [Result Number], nz([Results and
Limits].[Daily Lower Limit],0) AS [Daily Lower Limit], nz([Results and
Limits].[Daily Upper Limit],0) AS [Daily Upper Limit], nz([Results and
Limits].[Monthly Lower Limit],0) AS [Monthly Lower Limit], nz([Results
and Limits].[Monthly Upper Limit],0) AS [Monthly Upper Limit], [Results
and Limits].[Compliance Status], [Results and Limits].[Monthly Upper
String], [Results and Limits].Expr2, [Results and Limits].[Violation
Comments], [Results and Limits].[Corrective Actions], [samples and
results].[Outfall Name], [samples and results].[Permit Number],
[samples and results].[Sample Notes], [samples and results].ID AS
[results ID], Count([results ID]) AS cnt
FROM [Results and Limits] LEFT JOIN [samples and results] ON [Results
and Limits].ID = [samples and results].ID
GROUP BY [samples and results].[Company Name], [samples and
results].[Permit Number], [samples and results].[Outfall Number],
[samples and results].[Collection Date], [samples and
results].CollectionEndDate, [samples and results].[Sample Type],
[samples and results].[Compliance Sample], [samples and
results].Sampler, [samples and results].[Date Lab Received], [samples
and results].[Analysis Date], [samples and results].[Method ID],
[samples and results].[Method Description], [samples and
results].Analyte, [samples and results].Result, [samples and
results].Units, [samples and results].[Reporting Limit], [samples and
results].[Lab Sample ID], [samples and results].[Lab Name], nz([Results
and Limits].[Result Number],0), nz([Results and Limits].[Daily Lower
Limit],0), nz([Results and Limits].[Daily Upper Limit],0), nz([Results
and Limits].[Monthly Lower Limit],0), nz([Results and Limits].[Monthly
Upper Limit],0), [Results and Limits].[Compliance Status], [Results and
Limits].[Monthly Upper String], [Results and Limits].Expr2, [Results
and Limits].[Violation Comments], [Results and Limits].[Corrective
Actions], [samples and results].[Outfall Name], [samples and
results].[Permit Number], [samples and results].[Sample Notes],
[samples and results].ID
HAVING ((([samples and results].[Collection Date]) Like "*"))
ORDER BY [samples and results].[Collection Date];


How do you recommend I proceed? Thanks
Click to expand...
Click to expand...
 
S

Steven Britton via AccessMonster.com

There are two ways you could either set the referecenes in your Access
Database to have Excel constants or manually type there values into your
code.

Examples:

xlsApp.Columns("A:C").Select
xlsApp.Selection.HorizontalAlignment = -4108

or with references set


xlsApp.Columns("A:C").Select
xlsApp.Selection.HorizontalAlignment = xlcenter

You can record a Macro in Excel to do the formating and just paste it into
the VBA code of Access. If you don't want to add the reference set to find
out the Excel Constants just open the macro in excel and use Ctrl+G to get
an Immediate Window then you can type ?xlcenter and hit enter.

that will give you the -4108 actual value.
 
T

tmort

I recorded a macro and copied the code onto the end of my code that
cneters the text, however, I find that if I close Excel and go back to
Access and then either specify new criteria or just rerun the code to
open the access dataset as Excel that it gets the data but does not
execute this code. On closing Excel again I get the error saying
"Method Columns of object _global failed.

The code I added was:

Columns("A:J").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Range("A1").Select

I guess I have to reset something

Any thougts?

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't pass Recordset? 0
adOpenStatic Recordset still changing my table 5
So disconnected means recordset can only be connected in same rout 1
Send data from Access to Excel 1
How do you set a recordset's bookmark? 5
Need help opening ADO recordset. 14
Exporting Access table using a button click in exce 4
Bulk Email From Access 1

Top