Results 1 to 9 of 9
  1. #1
    wscwt01 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    16

    MS Access Selecting Columns to Export to Excel

    I have coded a button on my client's application that will export the contents of a table to Excel. That is all working fine. However, he has now asked if when the button is activated a PoP Up can be facilitated where they can select which of the columns are actually exported, via for example a checkbox. Any pointers on this would be very much appreciated. My VBA is at learner/beginner status. Many thanks in advance. Current Code is: -

    Private Sub Command39_Click()
    On Error GoTo ErrorHandler
    Dim dbTable As String


    Dim xlWorksheetPath As String
    xlWorksheetPath = "C:\UNHCR\Export File"
    xlWorksheetPath = xlWorksheetPath & "Complience.xlsx"
    dbTable = "tblMasterRecords"
    DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel12Xml, tablename:=dbTable, FileName:=xlWorksheetPath, hasfieldnames:=True
    ErrorHandlerExit:
    Exit Sub
    ErrorHandler:
    MsgBox "Error No: " & Err.Number & ";Description: " & Err.Description
    Resume ErrorHandlerExit
    End Sub

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You would want to create a query based on the users selections then export that instead of the table.

    There is an example of creating a query for the user here: https://www.access-programmers.co.uk...8/post-1810506
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    wscwt01 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    16

    Thanks

    Quote Originally Posted by Minty View Post
    You would want to create a query based on the users selections then export that instead of the table.

    There is an example of creating a query for the user here: https://www.access-programmers.co.uk...8/post-1810506
    Thank you for your prompt reply, much appreciated I will take a look.

  4. #4
    wscwt01 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    16
    Quote Originally Posted by wscwt01 View Post
    Thank you for your prompt reply, much appreciated I will take a look.
    <br>Hi again Minty,<br><br>That is pretty much it thanks. However my client is very very picky and won't want to see the "Datasheet View" at the bottom but i note it is an integral part of the code. Can this be hidden?. I don't need the qrySelector box either as there is only one output. Also I note the Deselect is not built but not critical for me.&nbsp; &nbsp;<br>
    <br>

  5. #5
    wscwt01 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    16
    I have imported the requisite details across from the above Database. The selection and populating of lists is fine. But on Export it Errors out on the Line in Red below.. Any pointers please?

    Private Sub cmdExport_Click()
    Dim qdf As QueryDef
    Dim strSql As String
    Dim strColumns As String
    Dim ctrl As Access.control
    Dim fileName As String
    Dim frm As Access.Form
    'Only select the visible columns
    If Me.subFrmQuery.SourceObject = "" Then Exit Sub
    Set frm = Me.subFrmQuery.Form

    For Each ctrl In frm.Controls
    If Not ctrl.ColumnHidden Then
    If strColumns = "" Then
    strColumns = ctrl.Name
    Else
    strColumns = strColumns & ", " & ctrl.Name
    End If
    End If
    Next ctrl

    strSql = "Select " & strColumns & " From " & Me.cmboQuery


    Set qdf = CurrentDb.CreateQueryDef("tmpQdf", strSql)


    fileName = CurrentProject.Path & "" & Me.cmboQuery & Format(Date, "YYYYMMDD")
    MsgBox "File located: " & fileName
    DoCmd.TransferSpreadsheet acExport, , qdf.Name, fileName, True
    CurrentDb.QueryDefs.Delete (qdf.Name)
    qdf.Close
    End Sub

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    if in doubt - use debug.print to see what your sql string looks like - also might be better to be using ctl.controlsource. Do your fields have space or no alpha numeric characters? in which case you need to surround with []. And you are looking through each control - so that will include labels, unbound controls, subforms and the like

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Any pointers please?
    - add Debug.Print strSql before you try to execute
    - add a break point in the code, run it and step through with F8
    - when strSql is printed in the immediate window, examine it and fix any problem with it by editing your code. Can also copy/paste output to a new query in sql view and attempt to switch to datasheet view. If it balks, it usually highlights the problem part. I imagine the issue is what strColumns contains.

    - and please use code tags (# on forum posting toolbar) with proper code indentation for easier reading.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    wscwt01 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    16
    Quote Originally Posted by Micron View Post
    - add Debug.Print strSql before you try to execute
    - add a break point in the code, run it and step through with F8
    - when strSql is printed in the immediate window, examine it and fix any problem with it by editing your code. Can also copy/paste output to a new query in sql view and attempt to switch to datasheet view. If it balks, it usually highlights the problem part. I imagine the issue is what strColumns contains.

    - and please use code tags (# on forum posting toolbar) with proper code indentation for easier reading.
    Thank you so much for your input. It turned out to be a couple of field names with spaces.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks, but I'd say Ajax beat me to it.
    At least you have a trouble shooting method you can use from now on. Will probably save you time posting here and waiting for answers some day.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-19-2021, 02:38 AM
  2. Replies: 3
    Last Post: 10-24-2018, 01:27 PM
  3. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  4. Replies: 7
    Last Post: 04-25-2013, 03:47 PM
  5. Export columns (table) from access to excel
    By dacodac in forum Programming
    Replies: 4
    Last Post: 01-30-2013, 04:27 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