Solved

Export Query recordset to Excel Template using ADO

Posted on 2010-11-24
16
60 Views
Last Modified: 2016-04-11
I am desparately trying to export a query recordset to an excel template without success. The query is has parmeters linked to form objects, which is where I might be going wrong but need some pointers.

So in my vba code , I need to set a recordset connection to the access query "qryHFAuditsDue1"

and then create instance of excel workbook from the template and paste recordset from cell range a2 down (using 'copyfromrecordset' ?)

I have attached my code which has been round the houses and am currently getting an invalid SQL Statement Error.

Please help
Public Sub AuditsDue1_xlFile()
     '   Late Binding (Needs no reference set)
    Dim oXL As Excel.Application
    'Dim oExcel As Object
    Dim sFullPath As String
    'Dim sPath As String
    
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Set rst = New ADODB.Recordset
    Set conn = CurrentProject.Connection

rst.Open "qryHFAuditsDue1", conn, adOpenForwardOnly, adLockReadOnly
     
     '   Create a new Excel instance
    'Set oXL = CreateObject("Excel.Application")
    Set oXL = New Excel.Application
     
     
     '   Only XL 97 supports UserControl Property
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0
     
     
     '   Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "C:\Temp\AuditsDue.xlt"
     
     
     '   Open it
    With oXL
        .Visible = True
        .Workbooks.Add (sFullPath)
        .Cells(2, 1).CopyFromRecordset rst
    End With
    
rst.Close

Set rst = Nothing
     
     
ErrExit:
    Set oXL = Nothing
    Exit Sub
     
ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit
End Sub

Open in new window

0
Comment
Question by:jezskill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +3
16 Comments
 
LVL 7

Expert Comment

by:shaydie
ID: 34209239
Have you tried building your sql statement in the code rather than use the query?

strSQL = "Select............" '(whatever your sql statement should be)
rst.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly
0
 
LVL 7

Expert Comment

by:shaydie
ID: 34209256
That way you can set and make sure the variables you say are coming from form values actually have a value and so on.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 34209637
Building the SQL dynamically is certainly an option with some advantages.
You can, of course, still use the query though.
You just need to explicitly evaluate the parameter values as the expression service can't do that for you through a data access API such as ADO.
You could just use a UDF to open your recordset for you such as shown here.

Cheers.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 1

Author Comment

by:jezskill
ID: 34214535
this is my sql for the query recordset

strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade]FROM ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id WHERE (((tblJob.DateScheduled) Between [forms]![frmInvoiceReport].[Calendar1] And [forms]![frmInvoiceReport].[Calendar2]) AND ((tblJobDescriptionLookUpResp.PlannedResponsive)='"A"'));"

The vba code window gives me an error with regards to the last condition and highlights the "A" witha complie error expected: end of statement. should the A not be encapsulated in double quotes?
0
 
LVL 34

Expert Comment

by:Norie
ID: 34215001
You shouldn't try and build an SQL statement like that - it's just going to be a nightmare to debug.

Also, I'm a little confused by this, there is no SQL statement in the code you posted.

I'm also not sure that that query would work using ADO.

That's because of the parameters, if you were to use that SQL for a query or report in Access you would at least have to have the form 'frmInvoiceReport' open.

Anyway, try something like this for the SQL, it will at least make it easier to debug.
Dim strSQL As String

   strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade] "
   strSQL = strSQL & " FROM "
   strSQL = strSQL & " ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) "
   strSQL = strSQL & " INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id "
   strSQL = strSQL & " WHERE (tblJob.DateScheduled Between [forms]![frmInvoiceReport].[Calendar1] AND [forms]![frmInvoiceReport].[Calendar2]) AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

Open in new window

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 34215110
You had a query - but you've moved to dynamically built SQL??
Not a problem in itself - but not necessary, bearing in mind what's been said already about parameter evaluation.

Naturally the forms would need to be open.
If the parameters need to be supplied regardless then you'd need to use a user defined function to supply the criteria instead.

If you're going with SQL in VBA then just concatenate the values in.

strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & [forms]![frmInvoiceReport].[Calendar1] & " AND " & [forms]![frmInvoiceReport].[Calendar2] & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

Cheers
0
 
LVL 1

Author Comment

by:jezskill
ID: 34216454
Thanks for the responses, I shall try your methods over th weekend and respond accordingly.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 34216469
Ah, just noticed "Calendar1". So these are dates?

strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & Format([forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#") & " AND " & Format([forms]![frmInvoiceReport].[Calendar2], "\#yyyy\-mm\-dd\#")  & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 125 total points
ID: 34216478
Which is often preferably created as

strSQL = strSQL & " WHERE (tblJob.DateScheduled >= " & Format([forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#") & " AND tblJob.DateScheduled < " & Format([forms]![frmInvoiceReport].[Calendar2] + 1, "\#yyyy\-mm\-dd\#")  & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

If your DateScheduled may contain time component parts too.
0
 
LVL 1

Author Comment

by:jezskill
ID: 34222565
Ok , this is the current code I have, unfortunately I am still getting an invalid SQL statement error. The form is open which references the date values (which have no time values) .  Not sure what I should try now?
Public Sub AuditsDue1_xlFile()
     '   Late Binding (Needs no reference set)
    Dim oXL As Excel.Application
    'Dim oExcel As Object
    Dim sFullPath As String
    'Dim sPath As String
    
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset
    Set conn = CurrentProject.Connection
    
'strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade]FROM ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id WHERE (((tblJob.DateScheduled) Between [forms]![frmInvoiceReport].[Calendar1] And [forms]![frmInvoiceReport].[Calendar2]) AND ((tblJobDescriptionLookUpResp.PlannedResponsive)='A'));"

Dim strSQL As String

   strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade] "
   strSQL = strSQL & " FROM "
   strSQL = strSQL & " ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) "
   strSQL = strSQL & " INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id "
   strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & Format([Forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#") & " AND " & Format([Forms]![frmInvoiceReport].[Calendar2], "\#yyyy\-mm\-dd\#") & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"



rst.Open "qryHFAuditsDue1", conn, adOpenForwardOnly, adLockReadOnly
     
     '   Create a new Excel instance
    'Set oXL = CreateObject("Excel.Application")
    Set oXL = New Excel.Application
     
     
     '   Only XL 97 supports UserControl Property
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0
     
     
     '   Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "C:\Temp\AuditsDue.xlt"
     
     
     '   Open it
    With oXL
        .Visible = True
        .Workbooks.Add (sFullPath)
        .Cells(2, 1).CopyFromRecordset rst
    End With
    
rst.Close

Set rst = Nothing
     
     
ErrExit:
    Set oXL = Nothing
    Exit Sub
     
ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit
End Sub

Open in new window

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 34222678
The syntax looks fine.
I'd suggest the old standard, to print out the SQL (Debug.Print strSQL) and execute that definition in a new query window.
See if it parses.  Nothing stands out to me.
0
 
LVL 34

Assisted Solution

by:Norie
Norie earned 125 total points
ID: 34223424
One thing I would definitely check is the parantheses.

I don't know where the SQL came from but there seems to be quite a few of them kicking about.

I'm sure most of them are needed but there are times when you copy SQL that's been generated it includes some you don't need.

That's usually not a problem if you are just going to use the SQL as is without adding criteria or values.

The best way to test it is to do what LPurvis suggests.

You might want to add another couple of variables for the dates.
Dim strSQL As String
Dim strStartDate As String
Dim strEndDate As String
 
strStartDate = Format([Forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#")
strenddatee Format([Forms]![frmInvoiceReport].[Calendar2], "\#yyyy\-mm\-dd\#")
 
    
   strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade] "
   strSQL = strSQL & " FROM "
   strSQL = strSQL & " ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) "
   strSQL = strSQL & " INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id "
   strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & strStartDate & " AND " & strEndDate & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

Open in new window

0
 
LVL 1

Author Comment

by:jezskill
ID: 34334250
Apologies for delay, I am still trying to carve out some time to test.
0
 
LVL 6

Accepted Solution

by:
nathaniel earned 250 total points
ID: 38082291
When I tried reconstructing the SQL string (i tested the content of the SQL string from the immediate pane), I think one of the errors are missing parenthesis (after Between and last line):

It should be:

WHERE (tblJob.DateScheduled Between ([forms]![frmInvoiceReport].[Calendar1] AND [forms]![frmInvoiceReport].[Calendar2]) AND (tblJobDescriptionLookUpResp.PlannedResponsive='A'))
0
 
LVL 24

Expert Comment

by:broomee9
ID: 41544411
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

731 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question