Results 1 to 6 of 6
  1. #1
    Nadal is offline Novice
    Windows 8 Access 2016
    Join Date
    May 2016
    Posts
    4

    Export the Access selected Records to Excel

    Hi,

    I have two codes that work perfectly seperate, but now I want to combine them.
    What I want to do, is that I want to select records via a checkbox and after I press a command button, the checkbox should switch back to FALSE (automatically) and the selected records should be automated been shown in a Excel table.
    I'm trying to manage this now since a long time and can't quite figure out how it works...

    Click image for larger version. 

Name:	1.jpg 
Views:	25 
Size:	199.9 KB 
ID:	24708

    Heres the code for resetting the checkbox:

    Private Sub cmd_Check_click()
    With Me.Recordset
    Do While Not .EOF
    If !Compare = True Then
    Call .Edit
    !Compare = False
    Call .Update
    End If
    Call .MoveNext
    Loop
    End With
    End Sub

    Heres the Code for jumping to the Excel file (Just could manage to export all records to a Excel sheet...):

    Option Compare Database
    Option Explicit

    Private Sub SaveRecordsetToExcelRange()
    ' Excel constants:
    Const strcXLPath As String = "C:\Users\qxn4964\Desktop\Dateien\Arbeit\Datasheet \Excel\Example\MyWorkbook.xls"
    Const strcWorksheetName As String = "Sheet1"
    Const strcCellAddress As String = "B2"

    ' Access constants:
    Const strcQueryName As String = "Vehicle queries"

    ' Excel Objects:
    Dim objXL As Excel.Application
    Dim objWBK As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objRNG As Excel.Range

    ' DAO objects:
    Dim objDB As DAO.Database
    Dim objQDF As DAO.QueryDef
    Dim objRS As DAO.Recordset


    On Error GoTo Error_Exit_SaveRecordsetToExcelRange

    ' Open a DAO recordset on the query:
    Set objDB = CurrentDb()
    Set objQDF = objDB.QueryDefs(strcQueryName)
    Set objRS = objQDF.OpenRecordset

    ' Open Excel and point to the cell where
    ' the recordset is to be inserted:
    Set objXL = New Excel.Application
    objXL.Visible = True
    Set objWBK = objXL.Workbooks.Open(strcXLPath)
    Set objWS = objWBK.Worksheets(strcWorksheetName)
    Set objRNG = objWS.Range(strcCellAddress)
    objRNG.CopyFromRecordset objRS

    ' Destroy objects:
    GoSub CleanUp

    Exit_SaveRecordsetToExcelRange:
    Exit Sub

    CleanUp:
    ' Destroy Excel objects:
    Set objRNG = Nothing
    Set objWS = Nothing
    Set objWBK = Nothing
    Set objXL = Nothing



    ' Destroy DAO objects:
    If Not objRS Is Nothing Then
    objRS.Close
    Set objRS = Nothing
    End If
    Set objQDF = Nothing
    Set objDB = Nothing

    Return

    Error_Exit_SaveRecordsetToExcelRange:
    MsgBox "Error " & Err.Number _
    & vbNewLine & vbNewLine _
    & Err.Description, _
    vbExclamation + vbOKOnly, _
    "Error Information"

    GoSub CleanUp
    Resume Exit_SaveRecordsetToExcelRange
    End Sub





    Hope you can help me out.

    Thanks.

    Nadal

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    I dont recommend using optimized code when you your not sure how it should work. It limits your abilities for debugging.

    Create a query that returns only selected records. export those results of that query in a sendobject command. Unselect afterwards.

  3. #3
    Nadal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    4
    Hi Perceptus,
    thanks for the quick reply.
    I tried to figure out, how to import the selected records in another query, but I can't find the answer.
    Would you be so kind and tell me how I can import the records that were selected as TRUE in the checkbox to another query.

    Thanks for your time.

  4. #4
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Create a new query. Use the old query as the record source for the new query. Bring in the required fields. Set your criteria/parameters for the new query. Run it.

  5. #5
    Nadal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    4
    Hi Alansidman,

    Thanks for the help, I'll try to do that

  6. #6
    Nadal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    4
    I figured it out Thanks very much for the help

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

Similar Threads

  1. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  2. Replies: 5
    Last Post: 03-15-2016, 11:56 PM
  3. Export To Excel Through Macro is Duplicating Records
    By raykdogs in forum Import/Export Data
    Replies: 2
    Last Post: 05-05-2015, 04:30 AM
  4. Exporting Selected Records from Access to Excel
    By HarryScofs in forum Access
    Replies: 1
    Last Post: 07-25-2011, 05:09 PM
  5. Replies: 1
    Last Post: 08-17-2010, 02: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