Results 1 to 11 of 11
  1. #1
    Michael2233 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    6

    Lightbulb Export Data from listbox to new excel file by button press


    Hi Experts,
    what I've got is already done database with Form, all data are in Listbox, what i need is export filtered data by search boxes to excel files. Is it possible in easy way?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the code cycles thru the list box.
    the query uses the listbox to pull only data that is selected in the list box (customer name)
    the query would look like:


    select * from table where [custName]=forms!fMyForm!lstBox

    then exports the query data to excel:

    Code:
    sub btnPrintRpts_click()
    dim vID, vDir , vFile,vCustName
    dim i as integer
    
    vDir = "c:\temp\"
    
    for i = 0 to lstBox.listcount -1
        vCustName = lstBox.itemdata(i)      'get next item in listbox
        lstBox = vCustName          'set the listbox to this item
    
          vFile = vDir & "Invoice_" & vCustName & ".xls"
         docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel9,"qsListBoxQry", vFile, true, "Invoice"
    next
    end sub

  3. #3
    Michael2233 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    6
    Hi Ranman,
    i just recieved error 3011

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you didnt show WHERE the err occurred.
    nor show any code you are using. Are all your objects named the same as mine?

  5. #5
    Michael2233 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    6
    Can I share with You access file and could You help me somehow add it to an button? Today is first time i am using Access and i completely dont know what to do

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Yes send it on

  7. #7
    Michael2233 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    6
    https://file.io/Zc3Uih7CcE38 , i've put it on file.io site cuz in every case my file was too big , isnt it problem?

  8. #8
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    site doesnt work. just zip the file and upload here.

  9. #9
    Michael2233 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    6
    Sorry for delay, i needed to reduce base, all the time file was too big.


    And what i need here in Form to add button, that will download data from Listbox( List20) in excel format
    Attached Files Attached Files

  10. #10
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1. your 'criteria boxes' to search should be combo boxes, so user can select the item that already exists.
    2. the listbox query should be built (or chosen) when user applies the filter

    then you only export the 1 listbox query:
    Code:
    vDir = "c:\temp\"
    vListName = InputBox("Enter list name", "name list")
    If vListName = "" Then Exit Sub
    
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qsListBoxQry", vFile, True, vListName

  11. #11
    Michael2233 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    6
    You're expert so im sure Youre right but i dont know how to use this knowledge

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

Similar Threads

  1. Replies: 26
    Last Post: 04-07-2020, 02:29 AM
  2. export data to existing excel file
    By joshynaresh in forum Import/Export Data
    Replies: 7
    Last Post: 01-27-2014, 10:57 PM
  3. Replies: 3
    Last Post: 02-09-2013, 09:23 PM
  4. Replies: 3
    Last Post: 12-13-2012, 04:40 AM
  5. export data on excel file
    By Mina Garas in forum Queries
    Replies: 1
    Last Post: 12-01-2012, 02:43 PM

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