Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Exprt Query to specific Cell In Excel (2003)

    With the help of the folks in the lounge, I have been able to do a lot with getting data into Excel and creating pivot tables. I now have a new challenge, which is to get information from an access query into a specified cell. I know how to open and instance of Excel, I now how to go to specific cell on a specific sheet, and how to insert formula or labels into those cells.

    How would I instead go about putting the results of a query into a sheet beginning at Cell A3? Then put a Second query result further down the sheet, for example A35. The query is a crosstab query and results will be 14 columns wide and 3 to 7 rows in height. I am sure the answer is here in the archive, but my search up to now has been fruitless.

    Thanks in advance for your consideration and ideas.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exprt Query to specific Cell In Excel (2003)

    The Range method in Excel has a CopyFromRecordset method that lets you insert the results of a DAO or ADODB recordset into a worksheet

    For example (I have omitted the code to start Excel, since you know how to do that):

    Dim wsh As Excel.Worksheet
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set wsh = ... ' code to specify worksheet
    ...
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("MyQuery", dbOpenDynaset)
    wsh.Range("A3").CopyFromRecordset rst
    rst.Close
    ...
    Set rst = Nothing
    Set dbs = Nothing

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Exprt Query to specific Cell In Excel (2003)

    Hans,

    I am trying to export a CrossTab query. Prior to exporting I test the recordset to ensure there are in fact records to use to create the CrossTab query. The following is the code to test and the resulting error when running the code. What am I missing?



    Below is the code including the strSQL statement.

    Dim wsh As Excel.Worksheet
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    'Set wsh = ... ' code to specify worksheet
    '...
    Set wsh = xlWrkbk.Worksheets("CrossTab")
    Set dbs = CurrentDb


    strSQL = "SELECT PCID, YearCount " & _
    "FROM sqCrssTabName4EachPreQry " & _
    "WHERE PCID= " & PC & ";"

    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)



    Error msg:
    3061 Too few parameters. Expected 1.

    NOTE: 'PC' is an integer and does have a value when the code runs.

    Any ideas?

    Thanks!

    Ken

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exprt Query to specific Cell In Excel (2003)

    Does sqCrssTabName4EachPreQry prompt for a parameter, or does it refer to a control on a form, or does it use a custom VBA function?

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Exprt Query to specific Cell In Excel (2003)

    Hans,

    Yes, the prequery does use a date range in the criteria which comes from a form:
    Between DateSerial(Year([Forms]![frm_Reports]![txtReportEndDate]),1,1) And DateSerial(Year([Forms]![frm_Reports]![txtReportEndDate]),Month([Forms]![frm_Reports]![txtReportEndDate])+1,0)

    Is that the problem? Is there a way around it?

    Thanks!

    Ken

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exprt Query to specific Cell In Excel (2003)

    Yes, that is a problem. DAO doesn't "know" about Access forms (neither does ADO).
    As a workaround, you can assemble the SQL for the crosstab query with the added restriction in code, using concatenation to insert the date values into the SQL instead of the references to the form.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •