Results 1 to 9 of 9
  1. #1
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154

    Marco for action

    Dear all



    I would like to handle a query and convert it into excel format, around 600 files.

    The flow is

    a) open a form which as a combox to select the code to be queried
    b) source from [code] from the master.table
    c) made a button to "run Query
    d) then convert the query into excel and save in desktop's folder

    How can i do that ??

    Eric

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    to export a single query
    docmd.transferspreadsheet.....(fill in the details)

    If you are going to make 600 files, is this 600 queries?
    are they 600 choices in the combo box?
    if so, you must cycle thru the cbo and export....

    Code:
    dim qry, vFile
    dim i as integer
    
    for i = 0 to cboBox.listcount-1
       qry= cboBox.itemdata(i)   'get next item
       cboBox = qry                  'set the cbo to this item
    
                                           'export the query shown
      vFile = "C:\folder\" & qry & ".xls"
      docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12 ,qry,vFile,true
    next

  3. #3
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154
    Ranman

    Thanks for your help , sorry i am not familiar with the code

    Just to let you know that i built a form with a combo Box to select G_code for Query

    And in the Query, the SQL as follow:



    Hotel_T Query


    SELECT Hotel_T.G_Code, Hotel_T.PAIRNO, Hotel_T.Surname, Hotel_T.FirstName, Hotel_T.PAIRING, Hotel_T.HOTELNAME, Hotel_T.CHECKIN, Hotel_T.CHECKOUT, Hotel_T.NITE, Hotel_T.ROOM_TYPE, Hotel_T.REMARKS, Hotel_T.RM_PRICE, Hotel_T.TTL_PRICE, Hotel_T.PAY_MOD
    FROM Hotel_T
    WHERE (((Hotel_T.G_Code)=[Forms]![GroupSelector]![Grp_Selector]));




    G_code


    [Forms]![GroupSelector]![Grp_Selector]

    it does not work, wonder what when wrong.

    i need to do this for 300 times per day base, which i have 2 database

    I watched this a few time! https://youtu.be/mnWidUABYdQ


    Thanks for your help

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    im still not sure where the 300 items are.
    you want 1 query with 300 different keys where the keys are the value of the combo box?

    is the combo box called : Grp_Selector?

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    im still not sure where the 300 items are.
    if you have 1 query with 300 different keys where the keys are the value of the combo box

    Below is run with an 'export' button:
    Code:
    sub btnExport_click()
    dim vID, vFile
    dim i as integer
    
    
    for i = 0 to me.Grp_Selector.listcount-1
        vID= Grp_Selector.itemdata(i)   'get next item
       Grp_Selector = vID     'set the cbo to this item
    
    
                                           'export the query shown
      vFile = "C:\folder\" & vID & ".xls"
      docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12 ,"Hotel_T Query",vFile,true
    next

  6. #6
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154
    Ranman

    You are so kind

    Please find attached two photos.

    The Group selector is the G_code which is like a primary key to call for all records

    I create the Hotel_Query and in the G_code field, i put the parameter forms! xxxx

    But not responese

    Eric
    Attached Thumbnails Attached Thumbnails Group_selector.JPG   Query.JPG  

  7. #7
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154
    Ranman
    I have checked few time still unable to figure it out why not responding


    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	51.0 KB 
ID:	29951

  8. #8
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154
    Ranman

    Can you help me ?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is master.table - Hotel_T? Do you use any lookups set on fields in table?

    Continued in https://www.accessforums.net/showthread.php?t=67564
    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.

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

Similar Threads

  1. Marco Condition based off a Query
    By bigbox486 in forum Macros
    Replies: 1
    Last Post: 03-30-2015, 09:02 AM
  2. Using Null with Marco
    By wes9659 in forum Forms
    Replies: 8
    Last Post: 03-11-2015, 04:17 PM
  3. Marco: Export to Excel w/o Formatting
    By AKoval in forum Import/Export Data
    Replies: 1
    Last Post: 03-20-2013, 09:14 AM
  4. marco needed to add a certain number of records
    By askjacq in forum Programming
    Replies: 2
    Last Post: 02-09-2013, 12:45 PM
  5. Marco to split up and save a report
    By jjsaw5 in forum Programming
    Replies: 1
    Last Post: 02-02-2012, 01:33 PM

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