Results 1 to 12 of 12
  1. #1
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36

    Export Single Query to Multiple Excel Files

    Windows 7
    Access 2010
    Extreme Novice

    All,

    I am trying to export a single query (qry_Change Control Forms) to multiple excel files based on the field "Business_Unit". Persons (field name "ID") can exist on multiple rows; all rows for a given ID will have the same Business_Unit value. I thought I could use an open query command but keep receiving an error. Here is what I have at the moment:



    Code:
    rs.Open "SELECT EMPLOYEE_ID_#, Business_Unit FROM qry_Generate Change Forms", cn, adOpenStatic, adLockPessimistic
    While Not rs.EOF
          DoCmd.OpenQuery "qry_Generate Change Forms", acViewNormal, acReadOnly, "EMPLOYEE_ID_#='" & rs!EMPLOYEE_ID_# & "'"
          DoCmd.OutputTo acOutputQuery, "", acFormatXLS, "z:\2012_testing\ChgForms\" & Business_Unit & ".xls", False
          DoCmd.Close acQuery, "qry_Generate Change Forms", acSaveNo
    I am receiving an error on the following line that indicates "Wrong Number of Arguments or invalid property assignment"
    Code:
    DoCmd.OpenQuery "qry_Generate Change Forms", acViewNormal, acReadOnly, "EMPLOYEE_ID_#='" & rs!EMPLOYEE_ID_# & "'"
    Any thoughts on what I am doing wrong? It is probably something very simple. I started with code I had that does this with a report to .pdf format and maybe that is where I am going wrong.

    Kindest regards.
    sren

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The # character might be an issue. This is a special character for Access. Should not use special characters and punctuation (underscore is exception) in names. If used, enclose in [].

    Also, include semi-colon in the SELECT statement.

    rs.Open "SELECT [EMPLOYEE_ID_#], Business_Unit FROM qry_Generate Change Forms;", cn, adOpenStatic, adLockPessimistic

    DoCmd.OpenQuery "qry_Generate Change Forms", acViewNormal, acReadOnly, "[EMPLOYEE_ID_#]='" & rs![EMPLOYEE_ID_#] & "'"

    Is Employee_ID_# actually a text field? If not, remove the apostrophe delimiters.

    Num or No would be better than #.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36
    June 7,

    Many thanks. I wondered about the "#" symbol however it is part of the required field name in the output file. I will look at addressing manually...although I admit that is not the preferred option.

    Employee_ID_# is a text field. In looking at this though, it dawned on me that whereas I normally group by Employee ID, that is not the case this time. I am actually needing the output to create a different Excel file for each Business_Unit that includes all of the people. (In some cases, the people will have more than one row.) Should I just change the ID fields to the Business Unit field?

    Thanks.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sounds like yes.

    And the recordset only needs the Business_Unit field.

    SELECT DISTINCT Business_Unit FROM qry_Generate Change Forms;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36

    Export Single Query to Multiple Excel Files

    Okay, I made all of those changes. Here is what my code currently looks like:
    Code:
    Private Sub ctlGenChgCtlForms_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    Dim Business_Unit As String
    
    rs.Open "SELECT DISTINCT Business_Unit FROM qry_GenerateChangeForms;", cn, adOpenStatic, adLockPessimistic
    While Not rs.EOF
    DoCmd.OpenQuery "qry_GenerateChangeForms", acViewPreview, acReadOnly, "Business_Unit='" & rs!Business_Unit & "'"
    DoCmd.OutputTo acOutputQuery, "", acFormatXLS, "z:\2012_testing\CelergoChgForms\" & rs!Business_Unit & ".xls", False
    DoCmd.Close acQuery, "qry_GenerateChangeForms", acSaveNo
    rs.MoveNext
    Wend
    End Sub
    When I attempt to run, I am still receiving an error on this line:
    Code:
    DoCmd.OpenQuery "qry_GenerateChangeForms", acViewPreview, acReadOnly, "Business_Unit='" & rs!Business_Unit & "'"
    This is the error:
    Click image for larger version. 

Name:	error.PNG 
Views:	14 
Size:	31.8 KB 
ID:	10394
    Any thoughts on what I may have done incorrectly that is generating the error?

    Regards.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't remember last time I used the OpenQuery method so just checked it out. There is no WHERE CONDITION argument. The error is 'wrong number of arguments'.

    Options:

    1. Set a textbox on form to the recordset field value. Reference that textbox as a parameter in the query object

    2. Use VBA to modify the query object filter using QueryDefs collection http://www.logicwurks.com/CodeExampl...fsFromVBA.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36
    Yikes! This is WAY over my head! I will try to re-read this and do additional research to make sense of this. thanks!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No. 1 should be fairly simple. You already have the query and the form.

    Reference the textbox as an input parameter in the query. Under the Business_Unit field, criteria would be:
    Forms!formname!textboxname

    Create textbox on form. Code in your procedure to set the value would be:
    While Not rs.EOF
    Me.textboxname = rs!Business_Unit
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36
    My apologies but this is all new for me.

    Does this mean I have to enter the Business Unit that I want to export each time? There are countless ones. I was hoping to export all at one time into the different files.

    I did try what you listed however I am still receiving an error on the "openquery" line.

    Help......I am so lost but yet can not believe this is turning out to be so difficult. Thanks so much.

  10. #10
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36
    Question....Is a better option to write individual queries that pulls each Business Unit's data, then run through code to export all of them? I can try that.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The code I showed will set the value of the textbox on the form.

    You modified the form to create textbox?

    You modified the query to set input parameter?

    You modified the code?

    Beyond this, I would have to view your exact code to help further. Post code or attach the file if you want to provide. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36
    Hi...I was able to get this working. Thank you!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 12-29-2015, 10:41 PM
  2. Export Access into multiple excel files based on field value
    By turntabl1st in forum Import/Export Data
    Replies: 7
    Last Post: 11-08-2012, 12:43 PM
  3. Replies: 4
    Last Post: 09-19-2012, 11:49 AM
  4. Export multiple query's to multiple sheet excel
    By vaikz in forum Import/Export Data
    Replies: 4
    Last Post: 08-15-2012, 08:53 AM
  5. Export Query to Various Excel Files
    By dalet in forum Import/Export Data
    Replies: 7
    Last Post: 11-09-2009, 09:22 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums