Results 1 to 4 of 4
  1. #1
    jjmartinson is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Posts
    3

    Export Command Button in Form

    Good Afternoon. I am trying to add a command button to a form that will export a list within the form to excel. The form is set up with 4 drop down menus (Region, Job, Sub, and Month) that decide the outcome of the lists. There are 5 lists on the form tied to 5 different queries. After I filter using the drop downs and update the form, I would like to export the results of one of the lists to excel, but I am running into an issue. The code that I am currently using for command button is:



    Private Sub Retired_Click()
    Dim sXL As String, oXL As Object
    sXL = "C:\temp\Retired.xls"
    DoCmd.TransferSpreadsheet acExport, , "qryOldAssets", sXL
    Set oXL = CreateObject("Excel.Application")
    oXL.Visible = True
    oXL.WorkBooks.Open FileName:=sXL
    End Sub

    Is there a way to directly add the list name, lstRetired, to the DoCmd?
    Do I need to add Me.cboMonth.Value, etc. as query criteria in the DoCmd line?

    Let me know if I haven't provided enough info.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    You need to tell the query what filter to apply before you transfer it to Excel

  3. #3
    jjmartinson is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Posts
    3
    Are you speaking of a filter like the following? Also, where would I add that to a DoCmd.TransferSpreadsheet?

    If Me.cboRegion.value <> "" Then
    If Me.cboJob.value <> "" Then
    If Me.cboSub.value <> "" Then
    strChoice = strSQL1 & Me.cboRegion.value & _
    strSQL2 & Me.cboJob.value & _
    strSQL3 & Me.cboSub.value & _
    strSQL4 & Me.cboMonth.value & strSQL5
    Else
    strChoice = strSQL1 & Me.cboRegion.value & _
    strSQL2 & Me.cboJob.value & _
    strSQL4 & Me.cboMonth.value & strSQL5
    End If
    Else
    strChoice = strSQL1 & Me.cboRegion.value & _
    strSQL4 & Me.cboMonth.value & strSQL5
    End If
    Else
    If Me.cboSub.value <> "" Then
    strChoice = strSQL101 & Me.cboJob.value & _
    strSQL102 & Me.cboSub.value & _
    strSQL103 & Me.cboMonth.value & strSQL104
    Else
    If Me.cboJobvalue <> "" Then
    strChoice = strSQL101 & Me.cboJob.value & _
    strSQL103 & Me.cboMonth.value & strSQL104
    Else
    strChoice = strSQL47 & Me.cboMonth.value & strSQL48
    End If
    End If
    End If

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Can't understand the logic in your sql filtering. You seems to be repeating statements if you do a Debug.Print strChoice at the end what does it look like?

    David

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

Similar Threads

  1. command button to export to excel
    By jains in forum Forms
    Replies: 5
    Last Post: 06-30-2015, 06:27 PM
  2. Command Button Help!
    By arthura in forum Programming
    Replies: 3
    Last Post: 06-30-2009, 12:55 PM
  3. Adding Command Button To Form
    By uneek78 in forum Forms
    Replies: 7
    Last Post: 03-27-2009, 07:43 PM
  4. Duplicate command button
    By brettg in forum Database Design
    Replies: 1
    Last Post: 08-04-2008, 04:16 AM
  5. Command button code
    By lfolger in forum Forms
    Replies: 3
    Last Post: 03-25-2008, 04:26 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