Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Unanswered: Crosstab query to export to excel?

    I created a crosstab query with a parameter in it. I run it using a command button within a form. I want my crosstab query to export to excel. Can I do this?

  2. #2
    Join Date
    Feb 2004
    Posts
    11

    Help

    Hi, if anyone could help I could really use it!

  3. #3
    Join Date
    Feb 2004
    Posts
    42

    Re: Help

    Originally posted by vbtrader
    Hi, if anyone could help I could really use it!
    I did this, and it seemed to work:

    Private Sub RunCrossTabQuery_Click()

    DoCmd.OpenQuery "CrossTab", acViewNormal, acReadOnly
    'Run your Query

    DoCmd.TransferSpreadsheet acExport,
    _acSpreadsheetTypeExcel9, "CrossTab", "C:\Temp\test.xls"
    'Export it to excel file you specify


    End Sub



    Good luck, hope that helps
    Last edited by accessnoobie; 02-26-04 at 15:11.

  4. #4
    Join Date
    Feb 2004
    Posts
    11
    Thank You! I tried the code, however I am getting an error message that says: Compile error: Label not defined. What am I doing wrong. Here is what it looks like:

    Private Sub MonthlyHitsQuery_Click()
    On Error GoTo Err_MonthlyHitsQuery_Click

    Dim stDocName As String

    stDocName = "Crosstab"
    DoCmd.OpenQuery acViewNormal, acReadOnly
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query4", "\\Mdci5\MDCI5_HOME\hcamacho\My Docs\Data\Intranet Awards"

    Err_MonthlyHitsQuery_Click:
    MsgBox Err.Description
    Resume Exit_MonthlyHitsQuery_Click

    End Sub

  5. #5
    Join Date
    Feb 2004
    Posts
    42
    I used "CrossTab" to be the name of my query. If your query is named "Query4" you need to put that in both places I had "CrossTab". Make sense?

    If the name of your query is Query4, I think the code should look like this:

    Private Sub MonthlyHitsQuery_Click()
    On Error GoTo Err_MonthlyHitsQuery_Click

    DoCmd.OpenQuery "Query4", acViewNormal, acReadOnly
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query4", "\\Mdci5
    _\MDCI5_HOME\hcamacho\My Docs\Data\Intranet Awards"

    Exit_MonthlyHitsQuery_Click:
    Exit Sub

    Err_MonthlyHitsQuery_Click:
    MsgBox Err.Description
    Resume Exit_MonthlyHitsQuery_Click

    End Sub


    There may have also been a problem with the error/exit thing, not sure.
    Try that and see if it works, good luck...
    Last edited by accessnoobie; 03-01-04 at 17:52.

  6. #6
    Join Date
    Feb 2004
    Posts
    11
    Thank you for your help! I have one more question. I am getting an error message when I click on my query. My crosstab query will export to excel when I only select 1 day, however if I select 2 or more days I get the following error message "Too Many Fields defined". Any ideas how fix this? My code looks like this:

    Private Sub RunQuery_Click()
    On Error GoTo Err_RunQuery_Click

    Dim stDocName As String

    stDocName = "MonthlyHits_Crosstab"
    DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MonthlyHits_Crosstab", "R:\DEV\Docs\Intranet\IntranetAwards\Monthlyhits.x ls"

    Exit_RunQuery_Click:
    Exit Sub

    Err_RunQuery_Click:
    MsgBox Err.Description
    Resume Exit_RunQuery_Click

    End Sub

  7. #7
    Join Date
    Feb 2004
    Posts
    42
    Can you post the DB you are working on (if it is not too big)? I deleted the example one I was using.

    Thanks,
    Noobie

  8. #8
    Join Date
    Feb 2004
    Posts
    11
    The form name that I am having issues with is HitCountLog. The button is "Run Query". I want it to run the crosstab query and then save the output in R:\DEV\Docs\Intranet\IntranetAwards\Monthlyhits.xl s.

    Thanks for your help!
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2004
    Posts
    42
    I'm not sure why you are getting that error...it is working fine for me. The only problem I see is that it asks for the inputs twice. It is asking twice because the first time is to run the query for display within Access and the second time is for running the query to export to excel. Not sure how to make this go away, but I'll work on it. I'll get back to you tomorrow.

    Noobie

  10. #10
    Join Date
    Feb 2004
    Posts
    11
    I have a question regarding your last reply. You are not getting the "Too Many Fields defined" when you enter the second input? For example: When I enter the dates 3/1/04 (first) and 3/31/04 (second), I get the query to run with no errors when it displays to access. However, when I enter the same dates for the second input (query to excel) I get that error message. So I know that the query can run with these dates but it is not working for me when it exports to excel. Are you experiencing this same issue?

    VBTrader

  11. #11
    Join Date
    Feb 2004
    Posts
    42
    No, it seems to work just fine for me. It is exporting all the information to excel and not giving any error message. I don't know why it would be doing that for you, but working for me. Hope you figure it out. The only thing I did was change the path that the file is being written to, so it would work on my computer. I think your code would look like this:

    Good luck,
    Noobie

    Private Sub RunQuery_Click()
    On Error GoTo Err_RunQuery_Click

    Dim stDocName As String

    stDocName = "MonthlyHits_Crosstab"
    pathOutputFile =
    _"R:\DEV\Docs\Intranet\IntranetAwards\Monthlyhits. xls"

    DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
    _stDocName, pathOutputFile

    Exit_RunQuery_Click:
    Exit Sub

    Err_RunQuery_Click:
    MsgBox Err.Description
    Resume Exit_RunQuery_Click

    End Sub

  12. #12
    Join Date
    Feb 2004
    Posts
    11

    Smile

    Thank you for your help Noobie. I found a fix to my error message. I deleted the old "Monthlyhits.xls" file. Now when I run the query it works fine.

    VBTrader

Posting Permissions

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